SOLVED
Home

SVERWEIS / 2 Suchkriterien in einer Tabelle (1 x in Zeile und 1 x in Spalte)

%3CLINGO-SUB%20id%3D%22lingo-sub-856530%22%20slang%3D%22de-DE%22%3ESVERWEIS%20%2F%202%20search%20criteria%20in%20a%20table%20(1%20x%20in%20row%20and%201%20x%20in%20column)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856530%22%20slang%3D%22de-DE%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EI%20have%20a%20table%20(see%20below)%20from%20which%20I%20have%20to%20find%20the%20result%20in%20a%20search%20with%202%20criteria.%20Can%20I%20do%20this%20with%20SReference%20and%20who%20knows%20the%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESearch%20criterion%201%3A%20Find%20in%20a%20table%20the%20row%20in%20which%20a%20certain%20amount%20is%20written%3C%2FP%3E%3CP%3ESearch%20criterion%202%3A%20And%20now%20find%20the%20column%20in%20which%20the%202nd%20digit%20is%20the%20amount.%20criterion.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20amount%20(9'700)%20and%20the%20tariff%20level%20(A3)%20and%20I%20want%20Excel%20to%20find%20the%20result%20for%209700%20%3D%20170%20and%201.76.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20normal%20Sverweis%20formula%2C%20I%20find%20in%20the%20table%20the%20row%20in%20which%20the%20search%20amount%20of%209700%20is%20written%20and%20can%20output%20the%20result%20from%20the%20right-hand%20cells%20depending%20on%20the%20parameters.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20need%20the%20result%20from%20a%20cell%20that%20is%20below%20this%20row%20(9700)%20with%20the%202nd%20search%20criterion%20(A3)%20in%20another%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20should%20be%3A%20At%209700%20and%20tariff%20level%20A3%20the%20amount%20is%20%3CSTRONG%3E170%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E1.76%25%3C%2FSTRONG%3E.%20These%20values%20should%20then%20be%20shown%20as%20a%20view%20in%20a%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20know%20the%20formula(s)%3F%20Perhaps%20the%20result%20can%20be%20found%20by%20another%20formula%20(function)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20support.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131901iE047DB4244D30F0D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFredi01%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-856530%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856958%22%20slang%3D%22en-US%22%3ERe%3A%20SVERWEIS%20%2F%202%20Suchkriterien%20in%20einer%20Tabelle%20(1%20x%20in%20Zeile%20und%201%20x%20in%20Spalte)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409338%22%20target%3D%22_blank%22%3E%40fredi01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20try%20this%20formula%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDIRECT(ADDRESS(MATCH(%3CFONT%20color%3D%22%230000FF%22%3E9700%3C%2FFONT%3E%2CB%3AB%2C)%2CMATCH%3CFONT%20color%3D%22%230000FF%22%3E(170%3C%2FFONT%3E%2CINDIRECT(MATCH(%3CFONT%20color%3D%22%230000FF%22%3E9700%3C%2FFONT%3E%2CB%3AB%2C)%26amp%3B%22%3A%22%26amp%3BMATCH(%3CFONT%20color%3D%22%230000FF%22%3E9700%3C%2FFONT%3E%2CB%3AB%2C))%2C)%2B1))%2C0)%3C%2FP%3E%3CP%3EThis%20formula%20looks%20for%209700%20in%20columnB%2C%20gets%20that%20row%2C%26nbsp%3B%20then%20looks%20for%20170%20in%20same%20row%2C%20gets%20column%2C%20then%20fetches%20the%20next%20cell%20value(1.76).%26nbsp%3B%20if%20you%20do%20not%20find%20a%20matching%20entry%2C%20it%20will%20return%200.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857150%22%20slang%3D%22de-DE%22%3ERe%3A%20SVERWEIS%20%2F%202%20search%20criteria%20in%20a%20table%20(1%20x%20in%20row%20and%201%20x%20in%20column)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857150%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20and%20thanks%20for%20your%20replay.%3C%2FP%3E%3CP%3EI%20did%20not%20work%2C%20because%20I%20only%20know%20the%20amount%20and%20the%20code%20B1.%3C%2FP%3E%3CP%3EI%20use%20the%20form%20in%20my%20queryfield%3A%20%3D%2BSVERWEIS(A14%3B%20QST_TarifZG!%24A-6%3A%24N-1977%3B9*WAHR.%3C%2FP%3E%3CP%3Ewhit%20this%20form%2C%20I%20find%20the%20right%20row%20in%20a%20matrix%20which%20reads%20under%20QST_TarifZG!%24A-6%3A%24N-1977.%26nbsp%3B%3C%2FP%3E%3CP%3E9%20shows%20me%20than%20the%209th%20field%20from%20left%20to%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20only%20know%20the%20amount%20(which%20will%20be%20found%20in%20column%20A)%20and%20I%20know%20the%20code%20B2%2C%20The%20search%20should%20find%20the%20figure%20under%20Column%20A%20and%20in%20the%20field%20under%20the%20row%20B2.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857280%22%20slang%3D%22en-US%22%3ERe%3A%20SVERWEIS%20%2F%202%20Suchkriterien%20in%20einer%20Tabelle%20(1%20x%20in%20Zeile%20und%201%20x%20in%20Spalte)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409338%22%20target%3D%22_blank%22%3E%40fredi01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGrunds%C3%A4tzlich%20so%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.excelformeln.de%2Fformeln.html%3Fwelcher%3D39%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.excelformeln.de%2Fformeln.html%3Fwelcher%3D39%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EF%C3%BCr%20Anpassungen%20an%20dein%20Problem%20solltest%20du%20eine%20Beispieldatei%20bereitstellen.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866896%22%20slang%3D%22de-DE%22%3ERe%3A%20SVERWEIS%20%2F%202%20search%20criteria%20in%20a%20table%20(1%20x%20in%20row%20and%201%20x%20in%20column)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866896%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20-%20I%20have%20only%20just%20come%20to%20test%20your%20formula.%20Unfortunately%2C%20it%20doesn't%20work%20either.%20I%20have%20attached%20a%20file%20to%20you%20as%20an%20example.%20The%20search%20term%20A2%20and%209800%20is%20shown%20under%20a%20separate%20tab%20-%20but%20I%20used%20this%20data%20under%20Table%201.%20I%20would%20then%20just%20table2!%20before%20the%20value%20%22R12%22%20or%20%22R11%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumns%20A%20and%20B%20would%20have%20to%20be%20included%20in%20the%20search%2C%20as%20the%20amount%20could%20also%20be%20between%20these%20numbers.%20Thank%20you%20for%20your%20help.%20Lg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-867405%22%20slang%3D%22de-DE%22%3ERe%3A%20SVERWEIS%20%2F%202%20search%20criteria%20in%20a%20table%20(1%20x%20in%20row%20and%201%20x%20in%20column)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-867405%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409338%22%20target%3D%22_blank%22%3E%40fredi01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooks%20like%20a%20homework.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3EColumns%20A%20and%20B%20would%20have%20to%20be%20included%20in%20the%20search%2C%20as%20the%20amount%20could%20also%20be%20between%20these%20numbers.%20Thank%20you%20for%20your%20help.%20Lg%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3EONLY%20one%20one-dimensional%20matrix%20(one%20column%3B%20one%20row)%20can%20search.%20So%20adjust%20the%20area%20there.%3C%2FP%3E%3CP%3EThe%20range%20A2%3AA28%20does%20not%20contain%20all%20possible%20values%2C%20but%20it%20is%20sorted%20in%20ascending%20order.%20Therefore%2C%20the%20comparison%20type%20must%20be%20set%20to%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-868942%22%20slang%3D%22de-DE%22%3ERe%3A%20SVERWEIS%20%2F%202%20search%20criteria%20in%20a%20table%20(1%20x%20in%20row%20and%201%20x%20in%20column)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868942%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20support.%3C%2FP%3E%3CP%3EExcept%20for%20one%20other%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
fredi01
New Contributor

Hi

Ich habe eine Tabelle (siehe unten) aus der ich in einer Suche mit 2 Kriterien das Resultat finden muss. Kann ich das mit SVerweis und wer kenn die Formel:

 

Suchkriterium 1: Finde in einer Tabelle die Zeile, in welcher ein bestimmter Betrag steht

Suchkriterium 2: Und finde nun die Spalte, in der zu diesem Betrag das 2. Kriterium steht.

 

Ich kenne den Betrag (9'700) und die Tarifstufe (A3) und möchte, dass Excel das Resultat für 9700 = 170 und 1,76 findet.

 

Mit der normalen Sverweis-Formel finde ich in der Tabelle die Zeile, in der der Suchbetrag von 9700 steht und kann je nach Parametern das Resultat aus den rechtsfolgenden Zellen ausgeben.

 

Ich benötige jedoch das Resultat aus einer Zelle, welche unter dieser Zeile (9700) mit dem 2. Suchkriterium (A3) in einer anderen Spalte  übereinstimmt.

 

Ergebnis sollte sein: Bei 9700 und Tarifstufe A3 beträgt der Betrag 170 und 1.76%. Diese Werte sollten dann in einer Zelle als Ansicht ausgewiesen werden.

 

Kennt jemand die Formel(n)? Vielleicht kann das Ergebnis durch eine andere Formel(Funktion) gefunden werden?

 

Danke für Eure Unterstützung.

 

clipboard_image_0.png

 

Fredi01

 

6 Replies

@fredi01 

You can try this formula, 

=IFERROR(INDIRECT(ADDRESS(MATCH(9700,B:B,),MATCH(170,INDIRECT(MATCH(9700,B:B,)&":"&MATCH(9700,B:B,)),)+1)),0)

This formula looks for 9700 in columnB, gets that row,  then looks for 170 in same row, gets column, then fetches the next cell value(1.76).  if you do not find a matching entry, it will return 0. 

 

Hope this helps!! 

@Kodipady 

Hi and thanks for your replay.

I did not work, because I only know the amount and the code B1.

I use the formular in my queryfield: =+SVERWEIS(A14;QST_TarifZG!$A$6:$N$1977;9*WAHR.

whit this formular, I find the right row in a matrix which lies under QST_TarifZG!$A$6:$N$1977. 

9 shows me than the 9th field from left to right.

 

I only know the amount (which will be found in column A) and I know the Code B2, The search should find the figure under Column A and in the field under the row B2.

@fredi01 

Grundsätzlich so:

http://www.excelformeln.de/formeln.html?welcher=39

 

Für Anpassungen an dein Problem solltest du eine Beispieldatei bereitstellen.

 

@Detlef Lewin 

Besten Dank - ich bin erst jetzt dazugekommen, Deine Formel zu testen. Leider klappt das auch nicht. Ich habe Dir eine Datei als Beispiel angehängt. Der Suchbegriff A2 und 9800 wird unter einer separaten Lasche ausgewiesen - der einfachhalber habe ich diese Daten jedoch unter Tabelle1 eingesetzt. Ich würde dann einfach Tabelle2! vor den Wert "R12" oder "R11" voransetzen.

 

Spalten A und B müssten bei der Suche einbezogen werden, da der Betrag auch zwischen diesen Zahlen liegen könnte. Besten Dank für Deine Hilfe. LG

 

Solution

@fredi01 

 

Sieht ganz nach einer Hausaufgabe aus.

 


Spalten A und B müssten bei der Suche einbezogen werden, da der Betrag auch zwischen diesen Zahlen liegen könnte. Besten Dank für Deine Hilfe. LG

VERGLEICH() kann nur eine eindimensionale Matrix durchsuchen (eine Spalte; eine Zeile). Passe dort also den Bereich an.

Der Bereich A2:A28 enthält nicht alle möglichen Werte, aber er ist aufsteigend sortiert. Deshalb muss der Vergleichstyp auf 1 gesetzt werden.

 

@Detlef Lewin 

Lieben Dank für die Untersützung.

Bis auf ein Andermal

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies