Es ist einfach, mit einem einzigen Schlüssel in einer Tabelle nach einem Wert zu suchen. Wir können einfach die SVERWEIS-Funktion verwenden. Aber wenn Sie diese eindeutige Spalte in Ihren Daten nicht haben und in mehreren Spalten nach einem Wert suchen müssen, hilft SVERWEIS nicht.
Um einen Wert in einer Tabelle mit mehreren Kriterien zu suchen, verwenden wir die INDEX-MATCH-INDEX-Formel.
Generische Formel für die Suche nach mehreren Kriterien
= INDEX(lookup_range,MATCH(1, INDEX((criteria1 = range1)*(criteria2=range2)*(criteriaN=rangeN),0,1),0))
lookup_range: Dies ist der Bereich, aus dem Sie den Wert abrufen möchten.
Kriterien1, Kriterien2, Kriterium N: Dies sind die Kriterien, die Sie in Bereich1, Bereich2 und Bereich N abgleichen möchten. Sie können bis zu 270 Kriterien - Bereichspaare haben.
Bereich1, Bereich2, BereichN : Dies sind die Bereiche, in denen Sie Ihre jeweiligen Kriterien erfüllen.
Wie wird es funktionieren? Mal sehen…
Beispiel für INDEX und MATCH mit mehreren Kriterien
Hier habe ich eine Datentabelle. Ich möchte den Namen des Kunden mit Datum der Buchung, Erbauer und Bereich abrufen. Hier habe ich also drei Kriterien und einen Nachschlagebereich.
Schreiben Sie diese Formel in Zelle I4 und drücken Sie die Eingabetaste.
=INDEX(E2:E16,MATCH(1, INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0))
Wie es funktioniert:
Wir wissen bereits, wie die INDEX- und MATCH-Funktion in EXCEL funktioniert, daher werde ich das hier nicht erklären. Wir werden über den Trick sprechen, den wir hier verwendet haben.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Der Hauptteil ist dieser. Jeder Teil dieser Anweisung gibt ein Array von true false zurück.
Wenn boolesche Werte multipliziert werden, geben sie ein Array von 0 und 1 zurück. Die Multiplikation funktioniert als UND-Operator. Hense, wenn alle Werte wahr sind, gibt es nur 1 zurück, sonst 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Das wird insgesamt zurückkehren
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}* {FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR}* {FALSCH;FALSCH;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH}
Was übersetzt in
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Funktion gibt das gleiche Array zurück ({0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}) zur MATCH-Funktion als Lookup-Array.
SPIEL(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): Die MATCH-Funktion sucht nach 1 im Array {0;0;0;0;0;0 ;0;1;0;0;0;0;0;0;0}. Und gibt die Indexnummer der ersten 1 im Array zurück. Was hier 8 ist.
INDEX(E2:E16,SPIEL(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Schließlich gibt INDEX einen Wert aus dem angegebenen Bereich (E2:E16) bei gefunden . zurück Index (8).
Einfach????. Entschuldigung, einfacher geht es nicht.
Array-Lösung
Wenn Sie konsequent STRG + SHIFT + ENTER drücken können, können Sie die innere INDEX-Funktion eliminieren. Schreiben Sie einfach diese Formel und drücken Sie STRG + UMSCHALT + EINGABETASTE.
=INDEX(E2:E16,MATCH(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0))
Generische Array-Formel für die Suche nach mehreren Kriterien
=INDEX(lookup_range,MATCH(1,(criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0))
Formel funktioniert genauso wie obige Erklärung.
Ich habe mein Bestes versucht, es so einfach wie möglich zu erklären. Aber wenn ich nicht klar genug war, lass es mich im Kommentarbereich unten wissen. Übrigens, Sie müssen nicht wissen, wie der Motor funktioniert, um ein Auto zu fahren. Sie müssen nur wissen, wie man es fährt. Und Sie wissen es sehr gut.
So suchen Sie die Top-5-Werte mit doppelten Werten mithilfe von INDEX-MATCH in Excel
Wie man mehrere Werte in Excel SVERWEIST
Wie man mit dynamischem Col-Index in Excel SVERWEIS macht
So verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen in Excel
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität
So verwenden Sie die SVERWEIS-Funktion in Excel
So verwenden Sie die ZÄHLENWENN-Funktion in Excel
So verwenden Sie die SUMIF-Funktion in Excel