INDEX- und MATCH-Funktionskombination in Excel (10 einfache Beispiele)

Excel hat viele Funktionen - über 450+ davon.

Und viele davon sind einfach genial. Die Menge an Arbeit, die Sie mit ein paar Formeln erledigen können, überrascht mich immer noch (auch nachdem ich mehr als 10 Jahre Excel verwendet habe).

Und unter all diesen erstaunlichen Funktionen sticht die INDEX MATCH-Funktionskombination heraus.

Ich bin ein großer Fan der INDEX MATCH-Combo und habe es oft ziemlich deutlich gemacht.

Ich habe sogar einen Artikel über Index Match Vs SVERWEIS geschrieben, der eine kleine Debatte auslöste (Sie können den Kommentarbereich für ein Feuerwerk überprüfen).

Und heute schreibe ich diesen Artikel, der sich ausschließlich auf Index Match konzentriert, um Ihnen einige einfache und erweiterte Szenarien zu zeigen, in denen Sie diese leistungsstarke Formelkombination verwenden und die Arbeit erledigen können.

Notiz: Es gibt andere Nachschlageformeln in Excel - wie SVERWEIS und HVERWEIS und diese sind großartig. Viele Leute finden SVERWEIS einfacher zu bedienen (und das stimmt auch). Ich glaube, dass INDEX MATCH in vielen Fällen die bessere Option ist. Aber da es den Leuten schwer fällt, wird es weniger genutzt. Also versuche ich es mit diesem Tutorial zu vereinfachen.

Bevor ich Ihnen nun zeige, wie die Kombination von INDEX MATCH die Welt der Analysten und Data Scientists verändert, möchte ich Ihnen zunächst die einzelnen Teile – INDEX- und MATCH-Funktionen – vorstellen.

INDEX-Funktion: Ermittelt den Wert basierend auf Koordinaten

Der einfachste Weg, die Funktionsweise der Indexfunktion zu verstehen, besteht darin, sie sich als GPS-Satelliten vorzustellen.

Sobald Sie dem Satelliten die Breiten- und Längenkoordinaten mitteilen, weiß er genau, wohin er gehen und diesen Standort finden muss.

Trotz einer unglaublichen Anzahl von Lat-Long-Kombinationen würde der Satellit also genau wissen, wo er suchen muss.

Ich habe schnell nach meinem Arbeitsort gesucht und das habe ich bekommen.

Jedenfalls genug Geographie.

So wie ein Satellit Breiten- und Längenkoordinaten benötigt, benötigt die INDEX-Funktion in Excel die Zeilen- und Spaltennummer, um zu wissen, auf welche Zelle Sie sich beziehen.

Und das ist die Excel INDEX-Funktion in Kürze.

Lassen Sie mich es in einfachen Worten für Sie definieren.

Die INDEX-Funktion verwendet die Zeilen- und Spaltennummer, um eine Zelle im angegebenen Bereich zu finden und den darin enthaltenen Wert zurückzugeben.

INDEX selbst ist eine sehr einfache Funktion ohne Nutzen. Schließlich kennen Sie in den meisten Fällen die Zeilen- und Spaltennummern wahrscheinlich nicht.

Aber…

Die Tatsache, dass Sie es mit anderen Funktionen (Hinweis: MATCH) verwenden können, die die Zeilennummer und die Spaltennummer finden, macht INDEX zu einer äußerst mächtigen Excel-Funktion.

Unten ist die Syntax der INDEX-Funktion:

=INDEX (Array, Zeilennummer, [Spaltennummer]) =INDEX (Array, Zeilennummer, [Spaltennummer], [Bereichsnummer])

• Array - ein Zellbereich oder eine Array-Konstante.
• row_num - die Zeilennummer, aus der der Wert geholt werden soll.
• [Spaltenzahl] - die Spaltennummer, aus der der Wert geholt werden soll. Dies ist zwar ein optionales Argument, aber wenn row_num nicht angegeben wird, muss es angegeben werden.
• [are_num] - (Optional) Wenn das Array-Argument aus mehreren Bereichen besteht, wird diese Zahl verwendet, um die Referenz aus allen Bereichen auszuwählen.

Die INDEX-Funktion hat 2 Syntaxen (nur zur Information).

Die erste wird in den meisten Fällen verwendet. Der zweite wird nur in fortgeschrittenen Fällen verwendet (z. B. bei einer Drei-Wege-Suche), die wir in einem der Beispiele später in diesem Tutorial behandeln werden.

Aber wenn Sie neu in dieser Funktion sind, denken Sie einfach an die erste Syntax.

Nachfolgend finden Sie ein Video, das die Verwendung der INDEX-Funktion erklärt

MATCH-Funktion: Findet die Position basierend auf einem Lookup-Wert

Um auf mein vorheriges Beispiel für Längen- und Breitengrad zurückzukommen, ist MATCH die Funktion, die diese Positionen (in der Excel-Tabellenwelt) finden kann.

In einfacher Sprache kann die Excel-MATCH-Funktion die Position einer Zelle in einem Bereich finden.

Und auf welcher Grundlage würde es die Position einer Zelle ermitteln?

Basierend auf dem Lookup-Wert.

Wenn Sie beispielsweise eine Liste wie unten gezeigt haben und die Position des Namens „Mark“ darin finden möchten, können Sie die MATCH-Funktion verwenden.

Die Funktion gibt 3 zurück, da dies die Position der Zelle mit dem Namen Mark darin ist.

Die MATCH-Funktion sucht von oben nach unten nach dem Lookup-Wert (der „Mark“ ist) im angegebenen Bereich (der in diesem Beispiel A1:A9 ist). Sobald es den Namen findet, gibt es die Position in diesem bestimmten Bereich zurück.

Unten ist die Syntax der MATCH-Funktion in Excel.

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup-Wert - Der Wert, für den Sie im lookup_array nach einer Übereinstimmung suchen.
• lookup_array - Der Zellbereich, in dem Sie nach dem lookup_value suchen.
• [match_type] - (Optional) Dies gibt an, wie Excel nach einem übereinstimmenden Wert suchen soll. Es kann drei Werte annehmen -1, 0 oder 1.

Grundlegendes zum Match-Typ-Argument in der MATCH-Funktion

Es gibt eine zusätzliche Sache, die Sie über die MATCH-Funktion wissen müssen, und zwar wie sie die Daten durchsucht und die Zellenposition findet.

Das dritte Argument der MATCH-Funktion kann 0, 1 oder -1 sein.

Nachfolgend finden Sie eine Erklärung, wie diese Argumente funktionieren:

• 0 - dies sucht nach einer genauen Übereinstimmung mit dem Wert. Wenn eine genaue Übereinstimmung gefunden wird, gibt die MATCH-Funktion die Zellenposition zurück. Andernfalls wird ein Fehler zurückgegeben.
• 1 - Hiermit wird der größte Wert gefunden, der kleiner oder gleich dem Nachschlagewert ist. Damit dies funktioniert, muss Ihr Datenbereich aufsteigend sortiert sein.
• -1 – findet den kleinsten Wert, der größer oder gleich dem Nachschlagewert ist. Damit dies funktioniert, muss Ihr Datenbereich in absteigender Reihenfolge sortiert werden.

Unten ist ein Video, das erklärt, wie die MATCH-Funktion verwendet wird (zusammen mit dem Match-Typ-Argument).

Um es zusammenzufassen und in einfachen Worten auszudrücken:

• INDEX benötigt die Zellenposition (Zeilen- und Spaltennummer) und gibt den Zellenwert an.
• MATCH findet die Position mithilfe eines Nachschlagewerts.

Lassen Sie uns sie kombinieren, um ein Kraftpaket zu erstellen (INDEX + MATCH)

Nachdem Sie nun ein grundlegendes Verständnis dafür haben, wie INDEX- und MATCH-Funktionen einzeln funktionieren, lassen Sie uns diese beiden kombinieren und all die wunderbaren Dinge kennenlernen, die sie tun können.

Um dies besser zu verstehen, habe ich ein paar Beispiele, die die INDEX MATCH-Kombination verwenden.

Ich beginne mit einem einfachen Beispiel und zeige Ihnen dann auch einige fortgeschrittene Anwendungsfälle.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Beispiel 1: Eine einfache Suche mit INDEX MATCH Combo

Lassen Sie uns eine einfache Suche mit INDEX/MATCH durchführen.

Unten ist eine Tabelle, in der ich die Noten für zehn Schüler habe.

Aus dieser Tabelle möchte ich die Noten für Jim finden.

Unten ist die Formel, die dies leicht tun kann:

=INDEX($A$2:$B$11,MATCH("Jim",$A$2:$A$11,0),2)

Wenn Sie jetzt denken, dass dies leicht mit einer SVERWEIS-Funktion möglich ist, haben Sie Recht! Dies ist nicht die beste Verwendung von INDEX MATCH Großartigkeit. Obwohl ich ein Fan von INDEX MATCH bin, ist es etwas schwieriger als SVERWEIS. Wenn Sie nur Daten aus einer Spalte auf der rechten Seite abrufen möchten, empfehle ich Ihnen, SVERWEIS zu verwenden.

Der Grund, warum ich dieses Beispiel gezeigt habe, das auch mit SVERWEIS leicht gemacht werden kann, ist, Ihnen zu zeigen, wie INDEX MATCH in einer einfachen Umgebung funktioniert.

Lassen Sie mich nun einen Vorteil von INDEX MATCH aufzeigen.

Angenommen, Sie haben dieselben Daten, aber nicht in Spalten, sondern in Zeilen (wie unten gezeigt).

Weißt du was, du kannst immer noch die INDEX MATCH-Kombination verwenden, um Jims Noten zu erhalten.

Unten ist die Formel, die Ihnen das Ergebnis liefert:

=INDEX($B$1:$K$2,2,MATCH(“Jim”,$B$1:$K$1,0))

Beachten Sie, dass Sie den Bereich ändern und die Zeilen-/Spaltenteile wechseln müssen, damit diese Formel auch für horizontale Daten funktioniert.

Dies ist mit SVERWEIS nicht möglich, aber Sie können dies mit HVERWEIS problemlos tun.

Die INDEX MATCH-Kombination kann sowohl horizontale als auch vertikale Daten problemlos verarbeiten.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Beispiel 2: Nach links suchen

Es ist häufiger als Sie denken.

Oft müssen Sie die Daten aus einer Spalte abrufen, die sich links von der Spalte mit dem Nachschlagewert befindet.

Etwas wie unten gezeigt:

Um Michaels Verkäufe herauszufinden, müssen Sie links nachschlagen.

Wenn Sie an SVERWEIS denken, lassen Sie mich hier aufhören.

SVERWEIS ist nicht dafür gemacht, die Werte auf der linken Seite zu suchen und abzurufen.

Können Sie es immer noch mit SVERWEIS tun?

Ja, du kannst!

Aber das kann zu einer langen und hässlichen Formel werden.

Wenn Sie also eine Suche durchführen und Daten aus den Spalten auf der linken Seite abrufen möchten, verwenden Sie besser die INDEX MATCH-Kombination.

Unten ist die Formel, die Michaels Verkaufsnummer erhält:

=INDEX($A$2:$C$11,MATCH("Michael",C2:C11,0),2)

Ein weiterer Punkt hier für INDEX MATCH. SVERWEIS kann die Daten nur aus den Spalten abrufen, die sich rechts von der Spalte mit dem Nachschlagewert befinden.

Beispiel 3: Zwei-Wege-Suche

Bisher haben wir die Beispiele gesehen, in denen wir die Daten aus der Spalte abrufen wollten, die der Spalte mit dem Nachschlagewert benachbart ist.

Aber im wirklichen Leben erstrecken sich die Daten oft über mehrere Spalten.

INDEX MATCH kann problemlos eine bidirektionale Suche verarbeiten.

Unten ist ein Datensatz mit den Noten der Schüler in drei verschiedenen Fächern.

Wenn Sie in allen drei Fächern schnell die Noten eines Schülers einholen möchten, können Sie dies mit INDEX MATCH tun.

Die folgende Formel gibt Ihnen die Noten für Jim für alle drei Themen (Kopieren und Einfügen in eine Zelle und Ziehen, um andere Zellen zu füllen, oder Kopieren und Einfügen in andere Zellen).

=INDEX($B$2:$D$11,MATCH($F$3,$A$2:$A$11,0),MATCH(G$2,$B$1:$D$1,0))

Lassen Sie mich auch diese Formel kurz erklären.

Die INDEX-Formel verwendet B2:D11 als Bereich.

Das erste MATCH verwendet den Namen (Jim in Zelle F3) und holt seine Position in der Namensspalte (A2:A11). Dies wird die Zeilennummer, aus der die Daten abgerufen werden müssen.

Die zweite MATCH-Formel verwendet den Subjektnamen (in Zelle G2), um die Position dieses spezifischen Subjektnamens in B1:D1 zu erhalten. Mathe ist beispielsweise 1, Physik ist 2 und Chemie ist 3.

Da diese MATCH-Positionen in die INDEX-Funktion eingespeist werden, gibt sie die Punktzahl basierend auf dem Schüler- und Fachnamen zurück.

Diese Formel ist dynamisch, d. h. wenn Sie den Schülernamen oder die Fächernamen ändern, funktioniert sie immer noch und ruft die richtigen Daten ab.

Eine großartige Sache bei der Verwendung von INDEX/MATCH ist, dass selbst wenn Sie die Namen der Probanden vertauschen, Sie weiterhin das richtige Ergebnis erhalten.

Beispiel 4: Wert aus mehreren Spalten/Kriterien suchen

Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Markierungen für „Mark Long“ abrufen.

Da sich die Daten in zwei Spalten befinden, kann ich nicht nach Mark suchen und die Daten abrufen.

Wenn ich das so mache, erhalte ich die Markierungsdaten für Mark Frost und nicht für Mark Long (weil die MATCH-Funktion mir das Ergebnis für die MARK liefert, die sie trifft).

Eine Möglichkeit besteht darin, eine Hilfsspalte zu erstellen und die Namen zu kombinieren. Sobald Sie die Hilfsspalte haben, können Sie SVERWEIS verwenden und die Markierungsdaten abrufen.

Wenn Sie daran interessiert sind, dies zu erfahren, lesen Sie dieses Tutorial zur Verwendung von SVERWEIS mit mehreren Kriterien.

Bei der INDEX/MATCH-Kombination benötigen Sie jedoch keine Hilfsspalte. Sie können eine Formel erstellen, die mehrere Kriterien in der Formel selbst verarbeitet.

Die folgende Formel liefert das Ergebnis.

=INDEX($C$2:$C$11,MATCH($E$3&"|"&$F$3,$A$2:A11&"|"&$B$2:$B$11,0))

Lassen Sie mich kurz erklären, was diese Formel bewirkt.

Der MATCH-Teil der Formel kombiniert den Lookup-Wert (Mark und Long) sowie das gesamte Lookup-Array. Wenn $A$2:A11&”|”&$B$2:$B$11 als Lookup-Array verwendet wird, prüft es tatsächlich den Lookup-Wert mit der kombinierten Zeichenfolge aus Vor- und Nachname (getrennt durch das Pipe-Symbol).

Dadurch wird sichergestellt, dass Sie das richtige Ergebnis erhalten, ohne Hilfsspalten zu verwenden.

Sie können diese Art der Suche (bei mehreren Spalten/Kriterien) auch mit SVERWEIS durchführen, müssen jedoch eine Hilfsspalte verwenden. Die INDEX MATCH-Kombination macht es leicht, dies ohne Hilfsspalten zu tun.

Beispiel 5: Werte aus der gesamten Zeile/Spalte abrufen

In den obigen Beispielen haben wir die INDEX-Funktion verwendet, um einen Wert aus einer bestimmten Zelle abzurufen. Sie geben die Zeilen- und Spaltennummer an, und es wird der Wert in dieser bestimmten Zelle zurückgegeben.

Aber Sie können mehr tun.

Sie können auch die INDEX-Funktion verwenden, um die Werte aus einer ganzen Zeile oder Spalte abzurufen.

Und wie kann das nützlich sein, fragen Sie!

Angenommen, Sie möchten die Gesamtpunktzahl von Jim in allen drei Fächern wissen.

Sie können die INDEX-Funktion verwenden, um zuerst alle Noten von Jim zu erhalten und dann die SUM-Funktion verwenden, um eine Summe zu erhalten.

Mal sehen, wie das geht.

Unten habe ich die Ergebnisse aller Schüler in drei Fächern.

Die folgende Formel gibt mir die Gesamtpunktzahl von Jim in allen drei Fächern.

=SUMME(INDEX($B$2:$D$11,VERGLEICH($F$4,$A$2:$A$11,0),0))

Lassen Sie mich erklären, wie diese Formel funktioniert.

Der Trick besteht darin, 0 als Spaltennummer zu verwenden.

Wenn Sie 0 als Spaltennummer in der INDEX-Funktion verwenden, werden alle Zeilenwerte zurückgegeben. Wenn Sie 0 als Zeilennummer verwenden, werden alle Werte in der Spalte zurückgegeben.

Der folgende Teil der Formel gibt also ein Array von Werten zurück - {97, 70, 73}

INDEX($B$2:$D$11,MATCH($F$4,$A$2:$A$11,0),0)

Wenn Sie einfach diese obige Formel in eine Zelle in Excel eingeben und die Eingabetaste drücken, sehen Sie einen #WERT! Error. Dies liegt daran, dass kein einzelner Wert zurückgegeben wird, sondern ein Array von Werten.

Aber keine Sorge, das Array der Werte ist immer noch da. Sie können dies überprüfen, indem Sie die Formel auswählen und die Taste F9 drücken. Es zeigt Ihnen das Ergebnis der Formel, die in diesem Fall ein Array mit drei Werten ist - {97, 70, 73}

Wenn Sie nun diese INDEX-Formel in die SUM-Funktion einschließen, erhalten Sie die Summe aller von Jim erzielten Noten.

Sie können dasselbe verwenden, um die höchsten, niedrigsten und durchschnittlichen Noten von Jim zu erhalten.

So wie wir dies für einen Studenten getan haben, können Sie dies auch für ein Fach tun. Wenn Sie beispielsweise die durchschnittliche Punktzahl in einem Fach möchten, können Sie die Zeilennummer in der INDEX-Formel als 0 belassen und Sie erhalten alle Spaltenwerte dieses Fachs.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Beispiel 6: Ermitteln der Note des Schülers (ungefähre Match-Technik)

Bisher haben wir die MATCH-Formel verwendet, um die genaue Übereinstimmung des Lookup-Werts zu erhalten.

Sie können es aber auch für eine ungefähre Übereinstimmung verwenden.

Nun, was zum Teufel ist Approximate Match?

Lassen Sie mich erklären.

Wenn Sie nach Dingen wie Namen oder IDs suchen, suchen Sie nach einer genauen Übereinstimmung. Manchmal müssen Sie jedoch den Bereich kennen, in dem Ihre Lookup-Werte liegen. Dies ist normalerweise bei Zahlen der Fall.

Als Klassenlehrer möchten Sie beispielsweise die Note jedes Schülers in einem Fach wissen, und die Note wird basierend auf der Punktzahl festgelegt.

Unten ist ein Beispiel, in dem ich die Note für alle Schüler möchte und die Benotung anhand der Tabelle rechts festgelegt wird.

Wenn ein Schüler also weniger als 33 bekommt, ist die Note F und wenn er / sie weniger als 50, aber mehr als 33 bekommt, ist es E und so weiter.

Unten ist die Formel, die dies tut.

=INDEX($F$3:$F$8,VERGLEICH(B2,$E$3:$E$8,1),1)

Lassen Sie mich erklären, wie diese Formel funktioniert.

In der MATCH-Funktion haben wir 1 als [match_type]-Argument verwendet. Dieses Argument gibt den größten Wert zurück, der kleiner oder gleich dem Nachschlagewert ist.

Dies bedeutet, dass die MATCH-Formel den Markierungsbereich durchläuft, und sobald sie einen Markierungsbereich findet, der kleiner oder gleich dem Wert der Nachschlagemarkierungen ist, wird sie dort anhalten und ihre Position zurückgeben.

Wenn also der Wert der Nachschlagemarke 20 ist, würde die MATCH-Funktion 1 zurückgeben, und wenn sie 85 ist, würde sie 5 zurückgeben.

Und die INDEX-Funktion verwendet diesen Positionswert, um die Neigung zu erhalten.

WICHTIG: Damit dies funktioniert, müssen Ihre Daten aufsteigend sortiert sein. Wenn dies nicht der Fall ist, können Sie falsche Ergebnisse erhalten.

Beachten Sie, dass das obige auch mit der folgenden SVERWEIS-Formel durchgeführt werden kann:

=SVERWEIS(B2,$E$3:$F$8,2,WAHR)

Die MATCH-Funktion kann jedoch noch einen Schritt weiter gehen, wenn es um die ungefähre Übereinstimmung geht.

Sie können auch absteigende Daten haben und die INDEX MATCH-Kombination verwenden, um das Ergebnis zu finden. Wenn ich beispielsweise die Reihenfolge der Notentabelle ändere (wie unten gezeigt), kann ich immer noch die Noten der Schüler finden.

Dazu muss ich nur das Argument [match_type] auf -1 ändern.

Unten ist die Formel, die ich verwendet habe:

=INDEX($F$3:$F$8,MATCH(B2,$E$3:$E$8,-1),1)

SVERWEIS kann auch eine ungefähre Übereinstimmung durchführen, jedoch nur, wenn die Daten in aufsteigender Reihenfolge sortiert sind (aber es funktioniert nicht, wenn die Daten in absteigender Reihenfolge sortiert sind).

Beispiel 7: Suche nach Groß-/Kleinschreibung

Bisher haben alle von uns durchgeführten Nachschlagevorgänge die Groß-/Kleinschreibung nicht beachtet.

Das bedeutet, dass es egal war, ob der Lookup-Wert Jim oder JIM oder Jim war. Sie erhalten das gleiche Ergebnis.

Aber was ist, wenn Sie möchten, dass bei der Suche die Groß-/Kleinschreibung beachtet wird.

Dies ist normalerweise der Fall, wenn Sie große Datensätze und die Möglichkeit der Wiederholung oder unterschiedliche Namen/IDs haben (mit dem einzigen Unterschied, der der Fall ist).

Angenommen, ich habe den folgenden Datensatz von Schülern, in dem es zwei Schüler mit dem Namen Jim gibt (der einzige Unterschied besteht darin, dass einer als Jim und ein anderer als jim eingegeben wird).

Beachten Sie, dass es zwei Schüler mit demselben Namen gibt - Jim (Zelle A2 und A5).

Da eine normale Suche nicht funktionieren würde, müssen Sie eine Groß-/Kleinschreibung beachten.

Unten ist die Formel, die Ihnen das richtige Ergebnis liefert. Da es sich um eine Matrixformel handelt, müssen Sie Strg + Umschalt + Eingabetaste verwenden.

=INDEX($B$2:$B$11,MATCH(WAHR,GENAU(D3,A2:A11),0),1)

Lassen Sie mich erklären, wie diese Formel funktioniert.

Die EXACT-Funktion prüft auf eine genaue Übereinstimmung des Nachschlagewerts (in diesem Fall „jim“). Es geht alle Namen durch und gibt FALSE zurück, wenn es keine Übereinstimmung ist, und TRUE, wenn es eine Übereinstimmung ist.

Die Ausgabe der EXACT-Funktion in diesem Beispiel ist also - {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Beachten Sie, dass es nur ein TRUE gibt, wenn die EXACT-Funktion eine perfekte Übereinstimmung gefunden hat.

Die MATCH-Funktion findet dann die Position von TRUE in dem von der EXACT-Funktion zurückgegebenen Array, das in diesem Beispiel 4 ist.

Sobald wir die Position haben, verwendet die INDEX-Funktion sie, um die Markierungen zu finden.

Beispiel 8: Finden Sie die engste Übereinstimmung

Kommen wir jetzt ein wenig weiter.

Angenommen, Sie haben einen Datensatz, in dem Sie die Person finden möchten, deren Berufserfahrung der erforderlichen Erfahrung am nächsten kommt (in Zelle D2 erwähnt).

Obwohl Lookup-Formeln nicht dafür gemacht sind, können Sie sie mit anderen Funktionen (wie MIN und ABS) kombinieren, um dies zu erreichen.

Unten ist die Formel, die die Person mit der Erfahrung findet, die der erforderlichen am nächsten kommt, und den Namen der Person zurückgibt. Beachten Sie, dass die Erfahrung am nächsten sein muss (was entweder weniger oder mehr sein kann).

=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))

Da es sich um eine Matrixformel handelt, müssen Sie Strg + Umschalt + Eingabetaste verwenden.

Der Trick in dieser Formel besteht darin, den Lookup-Wert und das Lookup-Array zu ändern, um den minimalen Erfahrungsunterschied zwischen erforderlichen und tatsächlichen Werten zu ermitteln.

Bevor ich die Formel erkläre, lassen Sie uns verstehen, wie Sie es manuell tun würden.

Sie gehen jede Zelle in Spalte B durch und finden den Unterschied in der Erfahrung zwischen dem, was erforderlich ist, und dem, was eine Person hat. Sobald Sie alle Unterschiede haben, finden Sie den Mindestwert und rufen den Namen dieser Person ab.

Genau das machen wir mit dieser Formel.

Lassen Sie mich erklären.

Der Nachschlagewert in der MATCH-Formel ist MIN(ABS(D2-B2:B15)).

Dieser Teil gibt Ihnen den minimalen Unterschied zwischen der angegebenen Erfahrung (die 2,5 Jahre beträgt) und allen anderen Erfahrungen. In diesem Beispiel gibt es 0.3 . zurück

Beachten Sie, dass ich ABS verwendet habe, um sicherzustellen, dass ich nach der nächsten suche (die mehr oder weniger als die angegebene Erfahrung sein kann).

Dieser Mindestwert wird nun zu unserem Nachschlagewert.

Das Lookup-Array in der MATCH-Funktion ist ABS(D2-$B$2:$B$15).

Dies gibt uns eine Reihe von Zahlen, von denen 2,5 (die erforderliche Erfahrung) abgezogen wurde.

Jetzt haben wir also einen Lookup-Wert (0,3) und ein Lookup-Array ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})

Die MATCH-Funktion findet die Position 0,3 in diesem Array, die auch die Position des Namens der Person ist, die die engste Erfahrung hat.

Diese Positionsnummer wird dann von der INDEX-Funktion verwendet, um den Namen der Person zurückzugeben.

Verwandte lesen: Finden Sie die engste Übereinstimmung in Excel (Beispiele mit Nachschlageformeln)

Klicken Sie hier, um die Beispieldatei herunterzuladen

Beispiel 9: Verwenden Sie INDEX MATCH mit Platzhalterzeichen

Wenn Sie bei einer teilweisen Übereinstimmung einen Wert nachschlagen möchten, müssen Sie Platzhalterzeichen verwenden.

Unten sehen Sie beispielsweise einen Datensatz mit Firmennamen und deren Marktkapitalisierungen, und Sie möchten die Marktkapitalisierung ermitteln. Daten für die drei Unternehmen auf der rechten Seite.

Da dies keine genauen Übereinstimmungen sind, können Sie in diesem Fall keine regelmäßige Suche durchführen.

Sie können jedoch immer noch die richtigen Daten erhalten, indem Sie ein Sternchen (*) verwenden, das ein Platzhalterzeichen ist.

Unten ist die Formel, die Ihnen die Daten liefert, indem Sie die Firmennamen aus der Hauptspalte abgleichen und die Marktkapitalisierung dafür abrufen.

=INDEX($B$2:$B$10,MATCH(D2&”*”,$A$2:$A$10,0),1)

Lassen Sie mich erklären, wie diese Formel funktioniert.

Da es keine genaue Übereinstimmung der Nachschlagewerte gibt, habe ich verwendet D2&”*” als Nachschlagewert in der MATCH-Funktion.

Ein Sternchen ist ein Platzhalterzeichen, das eine beliebige Anzahl von Zeichen darstellt. Das bedeutet, dass Apple* in der Formel jede Textzeichenfolge bedeuten würde, die mit dem Wort Apple beginnt und eine beliebige Anzahl von Zeichen danach haben kann.

Also wann Apfel* als Nachschlagewert verwendet wird und die MATCH-Formel in Spalte A danach sucht, gibt sie die Position von „Apple Inc.“ zurück, da sie mit dem Wort „Apple“ beginnt.

Sie können auch Platzhalterzeichen verwenden, um Textzeichenfolgen zu finden, in denen der Nachschlagewert dazwischen liegt. Wenn Sie beispielsweise *Apple* als Nachschlagewert verwenden, wird jede Zeichenfolge gefunden, die irgendwo das Wort Apfel enthält.

Hinweis: Diese Technik funktioniert gut, wenn Sie nur eine Übereinstimmungsinstanz haben. Wenn Sie jedoch mehrere Übereinstimmungsinstanzen haben (z. B. Apple Inc und Apple Corporation), dann würde die MATCH-Funktion nur die Position der ersten übereinstimmenden Instanz zurückgeben.

Beispiel 10: Drei-Wege-Suche

Dies ist eine erweiterte Anwendung von INDEX MATCH, aber ich werde sie dennoch behandeln, um Ihnen die Leistungsfähigkeit dieser Kombination zu zeigen.

Denken Sie daran, dass ich sagte, dass die INDEX-Funktion zwei Syntaxen hat:

=INDEX (Array, Zeilennummer, [Spaltennummer]) =INDEX (Array, Zeilennummer, [Spaltennummer], [Bereichsnummer])

Bisher haben wir in all unseren Beispielen nur das erste verwendet.

Für eine Drei-Wege-Suche müssen Sie jedoch die zweite Syntax verwenden.

Lassen Sie mich zunächst erklären, was ein Drei-Wege-Look bedeutet.

Bei einer bidirektionalen Suche verwenden wir die INDEX MATCH-Formel, um die Noten zu erhalten, wenn wir den Namen des Schülers und den Namen des Fachs haben. Das Abrufen der Noten von Jim in Math ist beispielsweise eine bidirektionale Suche.

Ein Drei-Wege-Look würde ihm eine weitere Dimension verleihen. Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten die Punktzahl von Jim in Math in der Zwischenprüfung wissen, dann wäre dies eine Drei-Wege-Suche.

Unten ist die Formel, die das Ergebnis liefert.

=INDEX(($B$3:$D$7,$B$11:$D$15,$B$19:$D$23),MATCH($F$5,$A$3:$A$7,0),MATCH(G$4 ,$B$2:$D$2,0),(IF(G$3="Unit Test",1,IF(G$3="Halbzeit",2,3))))

Die obige Formel prüfte auf drei Dinge - den Namen des Studenten, das Fach und die Prüfung. Nachdem es den richtigen Wert gefunden hat, wird es in die Zelle zurückgegeben.

Lassen Sie mich erklären, wie diese Formel funktioniert, indem Sie die Formel in Teile zerlegen.

• Array - ($B$3:$D$7,$B$11:$D$15,$B$19:$D$23): Anstatt ein einzelnes Array zu verwenden, habe ich in diesem Fall drei Arrays in Klammern verwendet.
• row_num - MATCH($F$5,$A$3:$A$7,0): Die MATCH-Funktion wird verwendet, um die Position des Schülernamens in Zelle $F$5 in der Liste der Schülernamen zu finden.
• spalte_num - MATCH(G$4,$B$2:$D$2,0): Die MATCH-Funktion wird verwendet, um die Position des Subjektnamens in Zelle $B$2 in der Liste der Subjektnamen zu finden.
• [area_num] - IF(G$3=”Einheitentest”,1,IF(G$3=”Halbzeit”,2,3)): Der Bereichsnummernwert teilt der INDEX-Funktion mit, welches der drei Arrays verwendet werden soll, um den Wert abzurufen. Wenn die Untersuchung Unit Term ist, würde die IF-Funktion 1 zurückgeben und die INDEX-Funktion würde das erste Array verwenden, um den Wert abzurufen. Wenn es sich um eine Zwischenprüfung handelt, würde die IF-Formel 2 zurückgeben, andernfalls wird 3 zurückgegeben.

Dies ist ein fortgeschrittenes Beispiel für die Verwendung von INDEX MATCH, und Sie werden wahrscheinlich keine Situation finden, in der Sie dies verwenden müssen. Aber es ist immer noch gut zu wissen, was Excel-Formeln können.

Klicken Sie hier, um die Beispieldatei herunterzuladen

Warum ist INDEX/MATCH besser als SVERWEIS?

Oder ist es?

Ja, ist es - in den meisten Fällen.

Ich werde meinen Fall in einer Weile vorstellen.

Aber bevor ich das tue, lassen Sie mich Folgendes sagen - SVERWEIS ist eine äußerst nützliche Funktion und ich liebe sie. Es kann eine Menge Dinge in Excel und ich benutze es hin und wieder selbst. Das heißt aber nicht, dass es nichts Besseres geben kann und INDEX/MATCH (mit mehr Flexibilität und Funktionalität) besser ist.

Wenn Sie also eine grundlegende Suche durchführen möchten, verwenden Sie besser SVERWEIS.

INDEX/MATCH ist SVERWEIS auf Steroiden. Und wenn Sie einmal INDEX/MATCH gelernt haben, ziehen Sie es vielleicht immer vor, es zu verwenden (insbesondere wegen seiner Flexibilität).

Lassen Sie mich, ohne es zu weit zu dehnen, kurz die Gründe nennen, warum INDEX/MATCH besser ist als SVERWEIS.

INDEX/MATCH kann nach links (sowie nach rechts) des Nachschlagewerts suchen

Ich habe es in einem der obigen Beispiele behandelt.

Wenn Sie einen Wert links vom Nachschlagewert haben, können Sie dies mit SVERWEIS nicht tun

Zumindest nicht mit nur SVERWEIS.

Ja, Sie können SVERWEIS mit anderen Formeln kombinieren und es erledigen, aber es wird kompliziert und chaotisch.

INDEX/MATCH hingegen wird dafür gemacht, überall nachzuschlagen (sei es links, rechts, oben oder unten).

INDEX/MATCH kann mit vertikalen und horizontalen Bereichen arbeiten

Auch hier, mit vollem Respekt vor SVERWEIS, ist es nicht dafür gemacht.

Schließlich steht das V in SVERWEIS für vertikal.

SVERWEIS kann nur vertikale Daten durchlaufen, während INDEX/MATCH Daten sowohl vertikal als auch horizontal durchgehen kann.

Natürlich gibt es die HLOOKUP-Funktion, die sich um die horizontale Suche kümmert, aber es ist dann nicht SVERWEIS… oder?

Ich mag die Tatsache, dass die INDEX MATCH-Kombination flexibel genug ist, um sowohl mit vertikalen als auch mit horizontalen Daten zu arbeiten.

SVERWEIS kann nicht mit absteigenden Daten arbeiten

Bei der ungefähren Übereinstimmung liegen SVERWEIS und INDEX/MATCH auf dem gleichen Niveau.

Aber INDEX MATCH nimmt den Punkt, da es auch Daten verarbeiten kann, die in absteigender Reihenfolge vorliegen.

Ich zeige dies in einem der Beispiele in diesem Tutorial, in dem wir die Note der Schüler anhand der Notentabelle ermitteln müssen. Wenn die Tabelle in absteigender Reihenfolge sortiert ist, würde SVERWEIS nicht funktionieren (aber INDEX MATCH würde).

INDEX/MATCH kann etwas schneller sein

Ich werde ehrlich sein. Ich habe diesen Test nicht selbst durchgeführt.

Ich verlasse mich auf die Weisheit eines Excel-Meisters - Charley Kyd.

Der Geschwindigkeitsunterschied zwischen SVERWEIS und INDEX/MATCH ist bei kleinen Datensätzen kaum wahrnehmbar. Aber wenn Sie Tausende von Zeilen und viele Spalten haben, kann dies ein entscheidender Faktor sein.

In seinem Artikel sagt Charley Kyd:

„Im schlimmsten Fall ist die INDEX-MATCH-Methode ungefähr so ​​schnell wie SVERWEIS; im besten Fall ist es viel schneller.“

INDEX/MATCH ist unabhängig von der tatsächlichen Spaltenposition

Wenn Sie einen Datensatz wie unten gezeigt haben, während Sie die Punktzahl von Jim in Physik abrufen, können Sie dies mit SVERWEIS tun.

Und dazu können Sie in SVERWEIS die Spaltennummer als 3 angeben.

Alles ist gut.

Aber was ist, wenn ich die Math-Spalte lösche.

In diesem Fall wird die SVERWEIS-Formel unterbrochen.

Wieso den? - Da die dritte Spalte fest programmiert war und ich eine Spalte dazwischen lösche, wird die dritte Spalte zur zweiten Spalte.

Die Verwendung von INDEX/MATCH ist in diesem Fall besser, da Sie die Spaltennummer mit MATCH dynamisch machen können. Anstelle einer Spaltennummer wird also nach dem Subjektnamen gesucht und dieser verwendet, um die Spaltennummer zurückzugeben.

Sicherlich können Sie dies tun, indem Sie SVERWEIS mit MATCH kombinieren, aber wenn Sie trotzdem kombinieren, warum nicht mit INDEX, das viel flexibler ist.

Wenn Sie INDEX/MATCH verwenden, können Sie sicher Spalten in Ihrem Dataset einfügen/löschen.

Trotz all dieser Faktoren gibt es einen Grund, warum SVERWEIS so beliebt ist.

Und das ist ein wichtiger Grund.

SVERWEIS ist einfacher zu verwenden

SVERWEIS nimmt nur maximal vier Argumente an. Wenn Sie Ihren Kopf um diese vier wickeln können, können Sie loslegen.

Und da die meisten grundlegenden Nachschlagefälle auch von SVERWEIS verarbeitet werden, hat es sich schnell zur beliebtesten Excel-Funktion entwickelt.

Ich nenne es die King of Excel-Funktionen.

INDEX/MATCH hingegen ist etwas schwieriger zu bedienen. Es kann sein, dass Sie sich bei der Verwendung hängen bleiben, aber für einen Anfänger ist SVERWEIS viel einfacher zu erklären und zu erlernen.

Und das ist kein Nullsummenspiel.

Wenn Sie also neu in der Nachschlagewelt sind und nicht wissen, wie man SVERWEIS verwendet, lernen Sie das besser.

Ich habe eine detaillierte Anleitung zur Verwendung von SVERWEIS in Excel (mit vielen Beispielen)

Meine Absicht in diesem Artikel ist es nicht, zwei großartige Funktionen gegeneinander auszuspielen. Ich wollte Ihnen die Leistungsfähigkeit der INDEX MATCH-Kombination und all die großartigen Dinge zeigen, die sie tun kann.

Ich hoffe, Sie fanden diesen Artikel nützlich.

Teilen Sie mir Ihre Meinung im Kommentarbereich mit, und falls Sie in diesem Tutorial einen Fehler finden, lassen Sie es mich bitte wissen.