Wie kann ich sverweis in excel mit index-match umkehren?

Um zu verstehen, wie INDEX und MATCH als Reverse-Lookup-Formel funktionieren, müssen Sie eine einfache Sache auswählen: Match teilt dem Index die Position (Zellennummer) eines Werts in einer Spalte oder Zeile mit, dann gibt der Index diesen Wert zurück. Wert unter Verwendung dieser Position (Zellennummer) .

Stellen Sie sich das so vor: Die MATCH-Funktion ist ein verdeckter Ermittler, der den Kriminellen findet, und die INDEX-Funktion ist ein Polizist, der diesen Kriminellen anschließend festnimmt.

Aber lassen Sie uns im Detail lernen, wie wir diese beiden Funktionen kombinieren können. Nachfolgend finden Sie die Syntax von INDEX, wie Sie wissen.

INDEX(array, row_num, [column_num])

In der INDEX-Funktion teilt ihr das Argument row_num mit, aus welcher Zeile sie den Wert zurückgeben soll. Nehmen wir an, wenn Sie 4 eingeben, wird der Wert der 4. Zeile zurückgegeben.

Um die Reverse-Lookup-Formel zu erstellen, müssen wir MATCH durch row_argument von INDEX ersetzen.

Wenn wir MATCH verwenden, sucht es nach dem Wert in der Suchspalte und gibt die Zellennummer dieses Werts zurück. Und dann verwendet INDEX diese Zahl, um die Zellenposition aus der Wertespalte zu bestimmen.

Am Ende wird der Wert dieser Zelle zurückgegeben und Sie erhalten den gesuchten Wert. Aber jetzt arbeiten wir mit einem realen Beispiel. Nachfolgend finden Sie eine Liste der Städte und die Namen der dort arbeitenden Mitarbeiter.

Hier müssen wir nach dem Namen des Mitarbeiters suchen, der in Mumbai arbeitet. Wenn Sie sich nun die Daten ansehen, ist die Spalte, in der Sie Städte haben, unsere Nachschlagespalte, und in der Spalte, in der Sie Mitarbeiternamen haben, ist das Ihre Wertespalte. Und die Formel wird sein:

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Teilen wir diese Formel in zwei Teile auf, um sie zu verstehen.

Teil 1 : Im ersten Teil haben wir die Match-Funktion verwendet, um nach dem Wert „Mumbai“ zu suchen, und sie hat die „5“ zurückgegeben, was die Position der Zelle ist, in der Sie den Wert „Mumbai“ in der Spalte „Städte“ haben.

Teil 2 : Im zweiten Teil haben wir INDEX verwendet und auf die Spalte „Mitarbeitername“ verwiesen, um den Wert zu ermitteln. Hier weiß die Indexfunktion, dass Sie den Wert der 5. Zelle der Spalte haben möchten. Daher wurde im Ergebnis „Siya“ zurückgegeben.

Weitere Beispiele für INDEX und Matching

Wir haben einige der häufigsten Probleme vor uns, die wir mit der INDEX MATCH-Formel gelöst haben. Greifen Sie auf die Beispieldateien zu: Laden Sie diese Beispieldateien unbedingt hier herunter, um jedes Beispiel nachzuvollziehen.

1. Einfache Suche mit INDEX – MATCH

Eine normale Suche ist eine der wichtigsten Aufgaben, die Sie mit Suchformeln erledigen müssen, und INDEX MATCH ist dafür perfekt. Hier haben wir eine Datentabelle mit Mitarbeiter-ID und -Namen. Jede ID ist einzigartig und Sie müssen anhand der ID nach dem Namen des Mitarbeiters suchen.

Angenommen, Sie möchten nach dem Namen EMP-132 suchen. Dafür lautet die Formel:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

So funktioniert diese Formel

Zuerst gleicht MATCH die Emp-ID in der Emp-ID-Spalte ab und gibt die Zellennummer der gesuchten ID zurück. Hier ist die Zeilennummer 6.

Danach gibt INDEX den Mitarbeiternamen aus der Namensspalte unter Verwendung derselben Zellennummer zurück.

2. Schauen Sie nach links

SVERWEIS kann bei der Suche nach einem Wert nicht nach links gehen. Wie bereits erwähnt, können Sie in INDEX und MATCH in jede Richtung suchen. In der Datentabelle unten befindet sich nach der Betragsspalte die Spalte „Rechnungsnummer“.

Wenn Sie also den Betrag einer bestimmten Rechnung suchen möchten, ist dies mit SVERWEIS nicht möglich. Wenn Sie in VLOOKUP eine Tabelle auswählen, ist die erste Spalte in dieser Tabelle die Nachschlagespalte.

Aber hier in dieser Tabelle müssen wir die letzte Spalte der Tabelle als Nachschlagespalte verwenden. Also Daumen runter für VLOOKUP hier. Rufen wir INDEX und MATCH zur Rettung auf und die Formel lautet:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…so funktioniert diese Formel

• Zunächst haben Sie sich auf die Betragsspalte in der Indexfunktion bezogen. Dies ist die Spalte, aus der wir den Wert erhalten müssen.
• Zweitens haben Sie im Argument row_number der Indexfunktion die Match-Funktion verwendet und die Rechnungsnummer angegeben, auf die Rechnungsspalte verwiesen und Null für die genaue Übereinstimmung verwendet.
• Drittens gibt die Match-Funktion die Zellennummer der Rechnung aus dem Bereich zurück.

Und am Ende verwendet INDEX diese Zahl, um den Betrag zurückzugeben, indem die Zelle in der Betragsspalte positioniert wird.

3. FuzzySearch

Genau wie SVERWEIS können Sie auch INDEX/MATCH für eine grobe Suche verwenden.

Die Fuzzy-Suche kann nützlich sein, wenn der gesuchte Wert nicht aufgeführt ist und Sie die größtmögliche Übereinstimmung erhalten möchten. In der folgenden Tabelle finden Sie eine Auflistung der Noten basierend auf den Noten.

Und wenn Sie eine Punktzahl von 79 erreichen möchten, können Sie die folgende Formel verwenden.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…so funktioniert diese Formel

In dieser Formel haben wir 1 in der Match-Funktion für match_type verwendet, was die Durchführung einer ungefähren Suche ermöglicht. Es gibt den ersten Wert zurück, der kleiner oder gleich dem Suchwert ist.

Bei 79 ist der erste niedrigste Wert 75 und bei 75 ist die Note B. Deshalb erhält man im Ergebnis ein B.

4. Horizontale Suche

Wie Sie wissen, ist HLOOKUP für die horizontale Suche gedacht, Sie können dafür aber auch INDEX und MATCH verwenden. Hier in der Datentabelle unten haben Sie eine horizontale Tabelle für monatliche Verkäufe und möchten den Verkaufswert für „Mai“ erhalten.

Und die Formel wird sein:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…so funktioniert diese Formel

In der obigen Formel haben wir MATCH nicht im Argument „row_num“ des Index, sondern in „column_num“ verwendet. Und match gibt die Spaltennummer von Mai zurück.

Und dann gibt INDEX den Wert der Ergebnisspalte basierend auf der Positionsnummer zurück.

5. Zwei-Wege-Suche

Bei einer bidirektionalen Suche müssen wir einen Wert aus einer Tabelle abrufen. Schauen Sie sich einfach die Tabelle unten an, in der Sie die Verkaufsmengen nach Gebiet und Produkt sehen.

Wenn Sie nun die Verkaufsmenge eines Produkts für einen bestimmten Bereich ermitteln möchten, benötigen Sie eine Zwei-Wege-Suche und dafür müssen Sie eine Kombination aus INDEX MATCH MATCH verwenden. Ja, hier müssen Sie MATCH zweimal verwenden.

Bei einer normalen Kombination von INDEX und MATCH verwenden Sie MATCH für die Zeilennummer, bei einer Zwei-Wege-Suche müssen Sie es jedoch auch für die Spaltennummer verwenden. Die Formeln lauten:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…so funktioniert diese Formel

Um in der obigen Formel den Verkaufsbetrag aus der Tabelle abzurufen, haben Sie die Indexfunktion und dann die Match-Funktion für die Argumente row_num und columns_num verwendet. Die Match-Funktion, die das Argument „column_num“ ist, gibt 5 als Produkt-D-Wert in der 5. Zeile des Bereichs zurück, auf den Sie verwiesen haben.

Und die Übereinstimmungsfunktion im Argument row_num gibt 2 zurück, da sich der Wert der Nordzone in der zweiten Spalte des von Ihnen angegebenen Bereichs befindet.

Mit diesen Werten hat die Indexfunktion nun den Wert zurückgegeben, der in der 2. Spalte und 5. Zeile steht: 1456.

6. Groß- und Kleinschreibung beachten

Wenn Sie auf ein Problem stoßen, wenn Sie in einer Liste oder Spalte zwei gleiche Werte, jedoch in unterschiedlicher Groß-/Kleinschreibung haben, können Sie eine Suche unter Berücksichtigung der Groß- und Kleinschreibung durchführen, um den richtigen Wert zu finden. Werfen wir einen Blick auf die unten stehende Liste der Studierenden, in der Sie den Vornamen tragen und in der zweiten Spalte die Liste, die Sie markiert haben.

Und am Anfang gibt es Substantive, die gleich sind, aber in unterschiedlichen Textfällen vorkommen. Zum Beispiel John Parker und JOHN Mathew. Angenommen, Sie möchten nach Marken von „JOHN“ und nicht nach „John“ suchen. Sie können mit INDEX und MATCH eine Suche nach exakten Übereinstimmungen erstellen. Und die Formel wird sein:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…so funktioniert diese Formel

Hier in dieser Formel haben Sie die EXACT-Funktion in der Match-Funktion verwendet. Da die Match-Funktion nicht nach einem Wert suchen kann, bei dem die Groß-/Kleinschreibung beachtet wird, ist EXACT hierfür die perfekte Funktion.

Es kann zwei Werte vergleichen und TRUE zurückgeben, wenn sie genau gleich sind (einschließlich Groß- und Kleinschreibung). Sie müssen diese Formel jedoch in Tabellenform eingeben, da Sie die gesamte Spalte mit einem einzelnen Wert in EXACT vergleichen müssen. Wenn Sie es eingeben, wird ein Array wie dieses zurückgegeben.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

Von dort aus müssen Sie die Match-Funktion verwenden, um die Position TRUE aus dem Array zu erhalten.

An diesem Punkt haben Sie die Zellennummer (Zeilennummer) des Werts, den Sie suchen müssen. Und Sie können INDEX für diesen Spaltenwert verwenden.

Wichtiger Punkt: Wenn Sie mit EXACT mehr als ein WAHR erhalten, gibt die Übereinstimmung einfach die Zahl für das erste WAHR zurück

7. Mit Wildcards mit INDEX MATCH

Platzhalter sind sehr nützlich. Sie können eine Teilsuche mithilfe von Platzhaltern durchführen. Und das Beste daran ist, dass Sie wie bei allen anderen Formelsuchen auch Platzhalter mit Index und Übereinstimmung verwenden können.

Werfen Sie einfach einen Blick auf die Namensliste unten, in der Sie die Vor- und Nachnamen der Mitarbeiter sowie deren Alter finden.

Aus dieser Liste müssen Sie das Alter eines bestimmten Mitarbeiters (Sondra) ermitteln. Fakt ist aber, dass man nur den Vornamen kennt.

Und wenn Sie ein Sternchen verwenden, können Sie anhand des Vornamens nach Sondras Alter suchen. Dafür lautet die Formel:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…so funktioniert diese Formel

Ein Sternchen ist ein Platzhalterzeichen, das n Zeichen ersetzen kann. Wenn Sie es also nach dem Vornamen verwendeten, ersetzte es den Nachnamen.

8. Niedrigster Wert

Nehmen wir an, Sie haben eine Liste von Schülern mit ihren Ergebnissen wie unten. Und nun möchten Sie in dieser Liste nach dem Namen des Schülers suchen, der die niedrigste Punktzahl hat.

Hierzu können Sie die MIN-Funktion mit Index und Match verwenden und die Formel lautet:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Die Namen der Schüler finden Sie in Spalte A und ihre Ergebnisse in Spalte B.

Wenn Sie also diese Formel in eine Zelle einfügen und die Eingabetaste drücken, wird der Name des Schülers mit der niedrigsten Punktzahl zurückgegeben, z. B. Librada Bastian.

Erläuterung

In dieser Formel haben wir drei verschiedene Teile.

Im ersten Teil gibt die MIN-Funktion die niedrigste Punktzahl zurück.

Danach gibt die Match-Funktion im zweiten Teil die Zelle mit der niedrigsten Punktzahl zurück.

Am Ende gibt die Indexfunktion den Wert der Spalte „Schülername“ unter Verwendung derselben Zellenposition zurück, die von „match“ zurückgegeben wurde.

Tipp: Auf die gleiche Weise können Sie sich auch den Namen des Schülers mit der höchsten Punktzahl anzeigen lassen.

9. Beste n-te Punktzahl

Stellen Sie sich nun Folgendes vor: Sie haben eine Liste von Studenten mit ihren Prüfungsergebnissen und möchten aus dieser Liste den Namen des Studenten erhalten, der die zweithöchste Punktzahl erreicht hat.

Die Sache ist, dass Sie nicht wissen, wie hoch die zweithöchste Punktzahl ist.

Wenn Sie mit der Formelsuche nach einem Wert suchen, sind Sie sich normalerweise sicher, nach welchem Wert Sie suchen. Aber hier wissen Sie nicht, wie hoch die zweithöchste Punktzahl ist.

Dazu können Sie also eine große Funktion mit einem Index kombinieren und diesen abgleichen. Mit der großen Funktion können Sie den zweithöchsten Wert im Bereich ermitteln.

Und die Formel wird sein:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…so funktioniert diese Formel

In dieser Formel haben Sie die große Funktion innerhalb der Match-Funktion für das Argument „lookup_value“ verwendet. Und in der großen Funktion haben Sie den Punktebereich und 2 erwähnt, um den zweithöchsten Wert zu erhalten.

Nachdem die große Funktion den zweitgrößten Wert zurückgibt, verwendet die Match-Funktion diesen Wert und gibt die entsprechende Zellennummer zurück.

Und am Ende verwendet die Indexfunktion diese Zellennummer und gibt den Namen des Schülers zurück.

10. Mehrere Kriterien

Normalerweise dient die Kombination aus Index und Match dazu, nach einem einzelnen Wert zu suchen. Und deshalb verwenden Sie in der Match-Funktion nur einen Bereich.

Aber manchmal, wenn Sie mit Daten aus der realen Welt konfrontiert werden, müssen Sie mehrere Kriterien verwenden, um einen Wert zu finden.

Betrachten Sie das folgende Beispiel. Hier finden Sie eine Liste der Produkte mit mehreren Details wie Produktname, Kategorie und Größe.

Und aus diesen Daten möchten Sie unter Berücksichtigung aller Kriterien den Preis eines bestimmten Produkts ermitteln.

Die Formel lautet daher:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Hinweis: Da es sich um eine Matrixformel handelt, müssen Sie sie mit Strg + Umschalt + Eingabetaste eingeben.

…so funktioniert diese Formel

In dieser Formel haben Sie drei verschiedene Arrays, um drei verschiedene Werte abzugleichen, und diese Arrays geben TRUE und FALSE zurück, wenn die Werte übereinstimmen.

Wenn Sie sie anschließend miteinander multiplizieren, erhalten Sie ein Array oder ähnliches.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

Und die Match-Funktion gibt die Position 1 für das Array zurück.

Am Ende gibt INDEX den Preis der Preisspalte unter Verwendung der durch die Übereinstimmung zurückgegebenen Zahl zurück.

Tipp: Wenn Sie keine Array-Formel verwenden möchten, können Sie die SUMPRODUCT-Bedingung verwenden.

11. Erster numerischer Wert eines Bereichs

Nehmen wir an, Sie haben eine Liste, die sowohl Text- als auch numerische Werte enthält, und möchten nun aus dieser Liste den ersten numerischen Wert abrufen.

Dazu können Sie die Funktion ISNUMBER mit index/match kombinieren. Mithilfe von ISNUMBER können Sie erkennen, welcher Wert eine Zahl und welcher ein Text ist.

Die Formel lautet:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Sie müssen diese Formel in Tabellenform eingeben (mit Strg + Umschalt + Eingabetaste).

…so funktioniert diese Formel

In dieser Formel gibt ISNUMBER ein Array zurück, das der Länge der Liste entspricht, und in diesem Array haben Sie TRUE für die Werte, die Zahlen sind, und FALSE für die restlichen Werte.

Danach haben Sie in der Match-Funktion TRUE als Suchwert verwendet. Es gibt also die Positionsnummer des ersten TRUE im Array zurück.

Letztendlich wird bei Verwendung dieses Positionsnummernindex der erste numerische Wert zurückgegeben.

12. Holen Sie sich den ersten nicht leeren Wert

Stellen wir uns Folgendes vor: Sie haben eine Werteliste, in der einige der ersten Zellen leer sind, und Sie möchten den ersten nicht leeren Wert erhalten.

Und Sie können diese Formel verwenden, um diesen ersten nicht leeren Wert zu erhalten.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…so funktioniert diese Formel

Wir müssen diese Formel in drei verschiedene Teile unterteilen, um sie richtig zu verstehen.

Zuerst haben Sie die ISBLANK-Funktion in der Match-Funktion verwendet, um ein Array zu erhalten, in dem Sie TRUE für leere Zellen und FALSE für nicht leere Zellen haben.

Zweitens gibt MATCH die Positionsnummer des ersten TRUE im von ISBLANK zurückgegebenen Array zurück.

An diesem Punkt haben Sie also die Zellennummer des ersten nicht leeren Werts.

Drittens gibt die Indexfunktion einfach den ersten nicht leeren Wert in der Liste zurück.

13. Häufigster Text

Nehmen wir nun an, dass Sie bei einer gegebenen Liste von Textwerten den häufigsten Text zählen müssen.

In der Liste unten finden Sie Namen.

Es gibt jedoch einige Namen, die mehr als einmal vorkommen.

Jetzt müssen Sie also den Namen ermitteln, der in der Liste am häufigsten vorkommt. Sie können die folgende Formel verwenden, die eine Kombination aus MODE, INDEX und MATCH ist.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Wenn Sie diese Formel einfügen, wird „Tamesha“ zurückgegeben, der gebräuchlichste Name.

…so funktioniert diese Formel

Zunächst vergleicht MATCH den gesamten Namensbereich mit sich selbst. Dabei wird ein Array zurückgegeben, in dem jeder Text seine erste Position darstellt.

Nehmen wir als Beispiel den Namen „Tamesha“, der unser häufigster Name auf der Liste ist. Wenn Sie sich nun die Liste ansehen, geschah dies zuerst in der 8. Zelle und dann in der 12. Zelle.

Aber wenn Sie sich die Tabelle ansehen, wurde für alle Positionen, an denen wir „Tamesha“ haben, 8 zurückgegeben, was die erste Position ist.

Danach gibt die Modusfunktion aus dem von MATCH zurückgegebenen Array die häufigste Zahl zurück, nämlich die Zellennummer des ersten Vorkommens von „ Tamesha “.

Und am Ende gibt INDEX den Text mit dieser Zellennummer zurück.

14. Erstellen Sie einen Hyperlink

Angenommen, Sie möchten nicht nur nach einem Wert suchen, sondern auch einen Hyperlink für diesen Wert erstellen. Auf diese Weise können Sie schnell zu der Zelle navigieren, in der sich Ihre Suchspalte befindet.

In der folgenden Tabelle müssen Sie beispielsweise das Alter einer Person ermitteln. Und wenn Sie einen Hyperlink für diesen Wert erstellen, können Sie problemlos zu der Zelle navigieren, in der sich dieser Wert befindet.

Und dazu müssen wir HYPERLINK + Zelle mit INDEX und MATCH verwenden und die Formel lautet:

So funktioniert diese Formel

Teilen wir diese Formel in mehrere Teile auf, um sie besser zu verstehen.

• Zunächst haben Sie Index und Match in der Zellfunktion verwendet. Und wenn Sie beide Funktionen in der Zellfunktion verwenden, erhalten Sie eine Zellreferenz des entsprechenden Werts anstelle des entsprechenden Werts.
• Zweitens haben Sie „#“ mit der Zellreferenz verkettet.
• Drittens haben Sie Index und Match erneut verwendet, um den passenden Wert zu erhalten, der als Linktext verwendet werden soll. Auf diese Weise erhalten Sie den entsprechenden Wert sowie den Link zu der Zelle, in der sich dieser Wert befindet.