Note: The other languages of the website are Google-translated. Back to English
Einloggen  \/ 
x
or
x
Registrieren  \/ 
x

or

So verwenden Sie INDEX und MATCH zusammen in Excel

Bei der Arbeit mit Excel-Tabellen finden Sie möglicherweise ständig Situationen, in denen Sie einen Wert nachschlagen müssen. In diesem Tutorial zeigen wir Ihnen, wie Sie die Kombination der INDEX- und MATCH-Funktionen anwenden, um horizontale und vertikale Lookups, bidirektionale Lookups, Groß-/Kleinschreibung berücksichtigende Lookups und Lookups durchzuführen, die mehrere Kriterien erfüllen.

Was machen INDEX- und MATCH-Funktionen in Excel?

So verwenden Sie INDEX- und MATCH-Funktionen zusammen


Was machen INDEX- und MATCH-Funktionen in Excel?

Bevor wir die INDEX- und MATCH-Funktionen verwenden, sollten wir uns zunächst vergewissern, wie uns INDEX und MATCH beim Nachschlagen von Werten helfen können.

Verwendung der INDEX-Funktion in Excel

Der INDEX -Funktion in Excel gibt den Wert an einer bestimmten Position in einem bestimmten Bereich zurück. Die Syntax der INDEX-Funktion lautet wie folgt:

=INDEX(array, row_num, [column_num])
  • Feld (erforderlich) bezieht sich auf den Bereich, aus dem Sie den Wert zurückgeben möchten.
  • row_num (erforderlich, es sei denn, column_num ist vorhanden) bezieht sich auf die Zeilennummer des Arrays.
  • Spalte_num (optional, aber erforderlich, wenn row_num weggelassen wird) bezieht sich auf die Spaltennummer des Arrays.

Zum Beispiel zu wissen die Abschlussprüfung von Jeff, dem 6. Schüler auf der Liste, können Sie die INDEX-Funktion wie folgt verwenden:

=INDEX(E2:E11, 6) >>> retournieren 60

Excel-Indexübereinstimmung 01

√ Hinweis: Die Reichweite E2: E11 ist, wo die Abschlussprüfung aufgeführt ist, während die Zahl 6 findet das Prüfungsergebnis der 6Schülerin.

Hier machen wir einen kleinen Test. Für die Formel =INDEX(B2:E2,3), welchen Wert wird es zurückgeben? ---Ja, es wird zurückkehren China, die 3. Wert im angegebenen Bereich.

Jetzt sollten wir wissen, dass die INDEX-Funktion perfekt mit horizontalen oder vertikalen Bereichen arbeiten kann. Aber was ist, wenn wir einen Wert in einem größeren Bereich mit mehreren Zeilen und Spalten zurückgeben müssen? Nun, in diesem Fall sollten wir sowohl eine Zeilennummer als auch eine Spaltennummer anwenden. Zum Beispiel, um es herauszufinden das Land, aus dem Emily kommt Mit INDEX können wir den Wert mit einer Zeilennummer von 8 und einer Spaltennummer von 3 in den Zellen bis B2 bis E11 wie folgt finden:

=INDEX(B2:E11,8,3) >>> retournieren China

Excel-Indexübereinstimmung 02

Nach den obigen Beispielen, über die INDEX-Funktion in Excel sollten Sie Folgendes wissen:

  • Die INDEX-Funktion kann mit vertikalen und horizontalen Bereichen arbeiten.
  • Bei der INDEX-Funktion wird die Groß-/Kleinschreibung nicht beachtet.
  • Die Zeilennummer geht der Spaltennummer (wenn Sie beide Zahlen benötigen) in der INDEX-Formel voran.

Bei einer wirklich großen Datenbank mit mehreren Zeilen und Spalten ist es jedoch sicherlich nicht bequem, die Formel mit einer genauen Zeilen- und Spaltennummer anzuwenden. Und hier sollten wir die Verwendung der MATCH-Funktion kombinieren.

Lassen Sie uns nun zuerst die Grundlagen der MATCH-Funktion kennenlernen.


Verwendung der MATCH-Funktion in Excel

Die MATCH-Funktion in Excel gibt einen numerischen Wert zurück, die Position eines bestimmten Elements im angegebenen Bereich. Die Syntax der MATCH-Funktion lautet wie folgt:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_array (erforderlich) bezieht sich auf den Zellbereich, in dem MATCH durchsucht werden soll.
  • match_type (Optional), 1, 0 or -1:
  • 1(Standard), MATCH findet den größten Wert, der kleiner oder gleich dem ist Lookup-Wert. Die Werte im lookup_array müssen in aufsteigender Reihenfolge platziert werden.
  • 0, MATCH findet den ersten Wert, der genau dem entspricht Lookup-Wert. Die Werte im lookup_array kann in beliebiger Reihenfolge sein. (In den Fällen, in denen der Übereinstimmungstyp auf 0 festgelegt ist, können Sie Platzhalterzeichen verwenden.)
  • -1, MATCH findet den kleinsten Wert, der größer oder gleich dem Lookup-Wert. Die Werte im lookup_array muss in absteigender Reihenfolge platziert werden.

Zum Beispiel zu wissen die Position von Vera in der Namensliste, können Sie die MATCH-Formel wie folgt verwenden:

=VERGLEICH("vera",C2:C11,0) >>> retournieren 4

Excel-Indexübereinstimmung 03

√ Hinweis: Bei der Funktion MATCH wird die Groß-/Kleinschreibung nicht berücksichtigt. Das Ergebnis „4“ zeigt an, dass der Name „Vera“ an 4. Position der Liste steht. Die „0“ in der Formel ist der Übereinstimmungstyp, der den ersten Wert im Lookup-Array findet, der genau dem Lookup-Wert „Vera“ entspricht.

Wissen die Position der Partitur „96“ in der Reihe von B2 bis E2, können Sie MATCH wie folgt verwenden:

=VERGLEICH(96,B2:E2,0) >>> retournieren 4

Excel-Indexübereinstimmung 04

☞ Dinge, die wir über die MATCH-Funktion in Excel wissen sollten:

  • Die MATCH-Funktion gibt die Position des Lookup-Werts im Lookup-Array zurück, nicht den Wert selbst.
  • Die MATCH-Funktion gibt bei Duplikaten die erste Übereinstimmung zurück.
  • Genau wie die INDEX-Funktion kann auch die MATCH-Funktion mit vertikalen und horizontalen Bereichen arbeiten.
  • Bei MATCH wird die Groß-/Kleinschreibung ebenfalls nicht beachtet.
  • Wenn der Nachschlagewert der MATCH-Formel in Textform vorliegt, schließen Sie ihn in Anführungszeichen ein.

Da wir nun die grundlegende Verwendung der INDEX- und MATCH-Funktionen in Excel kennen, krempeln wir die Ärmel hoch und machen uns bereit, die beiden Funktionen zu kombinieren.


So verwenden Sie INDEX- und MATCH-Funktionen zusammen

In diesem Teil werden wir über verschiedene Umstände sprechen, um die INDEX- und MATCH-Funktionen zu verwenden, um unterschiedliche Anforderungen zu erfüllen.

Beispiel zum Kombinieren von INDEX und MATCH

Bitte sehen Sie sich das folgende Beispiel an, um herauszufinden, wie wir die INDEX- und MATCH-Funktionen kombinieren können:

Zum Beispiel zu wissen Evelyns Abschlussprüfungsergebnis, sollten wir die Formel verwenden:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> retournieren 90

Excel-Indexübereinstimmung 05

Nun, da die Formel kompliziert aussehen kann, lassen Sie uns jeden Teil davon durchgehen.

Excel-Indexübereinstimmung 06

Wie Sie oben sehen können, ist die große INDEX Formel enthält drei Argumente:

  • Feld: A2: D11 weist INDEX an, den übereinstimmenden Wert aus den Zellen zurückzugeben bis A2 bis D11.
  • row_num: ÜBEREINSTIMMUNG("evelyn",B2:B11,0) teilt INDEX die genaue Zeile mit dem Wert mit.
  • Über die MATCH-Formel können wir sie folgendermaßen erklären: Die Position des ersten Werts, der genau gleich „evelyn“ in den Zellen von B2 bis B11 ist, in einem numerischen Wert zurückgeben, der ist 5.
  • Spalte_num: MATCH("Abschlussprüfung",A1:D1,0) teilt INDEX die genaue Spalte des Wertes mit.
  • Über die MATCH-Formel können wir sie folgendermaßen erklären: die Position des ersten Werts, der genau gleich der „Abschlussprüfung“ in den Zellen von A1 bis D1 ist, in einem numerischen Wert zurückzugeben, der ist 4.

Sie können die große Formel also so einfach sehen, wie wir sie unten gezeigt haben:

=INDEX(A2: D11,5,4)

Im Beispiel haben wir hartcodierte Werte verwendet, "evelyn" und "final exam". In einer so großen Formel möchten wir jedoch keine fest codierten Werte, da wir sie jedes Mal ändern müssen, wenn wir nach etwas Neuem suchen. Unter solchen Umständen können wir Zellbezüge verwenden, um die Formel wie folgt dynamisch zu machen:

=INDEX(A2: D11,SPIEL(G2,B2:B11,0),SPIEL(F3,A1:D1,0))

Excel-Indexübereinstimmung 07


INDEX und MATCH, um eine Linkssuche anzuwenden

Nehmen wir an, Sie müssen Evelyns Klasse kennen. Wie können wir INDEX und MATCH verwenden, um die Antwort zu erfahren? Wenn Sie aufgepasst haben, sollten Sie feststellen, dass sich die Klassenspalte auf der linken Seite der Namensspalte befindet und die Fähigkeit der leistungsstarken Nachschlagefunktion eines anderen Excels, SVERWEIS, nicht möglich ist.

Tatsächlich ist die Left-Lookup-Fähigkeit einer der Aspekte, bei denen die Kombination von INDEX und MATCH SVERWEIS überlegen ist.

Wissen Evelyns Klasse, alles was Sie tun müssen, ist den Wert in der Zelle F3 auf „Klasse“ zu ändern und die gleiche Formel wie oben gezeigt zu verwenden, die INDEX- und MATCH-Funktionen geben Ihnen dann sofort die Antwort:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> retournieren A

Excel-Indexübereinstimmung 08

Wenn Sie installiert habenlED Kutools for Excel, ein professionelles Excel-Add-In, das von unserem Team entwickelt wurde, können Sie mit seinem LOOKUP von rechts nach links Funktion mit wenigen Klicks. Um die Funktion zu implementieren, gehen Sie bitte zum Kutools Tab in Ihrem Excel, finden Sie die Formel Gruppe, und klicken Sie auf LOOKUP von rechts nach links in der Dropdown-Liste von Super NACHSCHLAG. Sie sehen ein Popup-Dialogfeld wie folgt:

Excel-Indexübereinstimmung 09

Klicken Sie hier für konkrete Schritte, um die linke Nachschlagefunktion mit Kutools für Excel anzuwenden.


INDEX und MATCH, um eine bidirektionale Suche anzuwenden

Können Sie nun die Kombinationsformel INDEX und MATCH mit dynamischen Lookup-Werten erstellen, um bidirektionale Lookups anzuwenden? Üben wir das Erstellen von Formeln in den Zellen G3, G4 und G5 wie unten gezeigt:

Excel-Indexübereinstimmung 10

Hier die Antworten:

Zelle G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Zelle G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Zelle G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Hinweis: Nachdem Sie die Formeln angewendet haben, können Sie leicht die Informationen aller Schüler abrufen, indem Sie den Namen in Zelle G2 ändern.


INDEX und MATCH, um eine Groß-/Kleinschreibung anzuwenden

Aus den obigen Beispielen wissen wir, dass die Funktionen INDEX und MATCH nicht zwischen Groß- und Kleinschreibung unterscheiden. In den Fällen, in denen Sie Ihre Formel jedoch benötigen, um Groß- und Kleinschreibung zu unterscheiden, können Sie hinzufügen GENAU Funktion zu Ihren Formeln wie folgt:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Strg + Umschalt + Enter. Ein Paar geschweifte Klammern wird dann in der Bearbeitungsleiste angezeigt.

Zum Beispiel zu wissen JIMMYs Prüfungsergebnis, verwenden Sie die Funktionen wie folgt:

Excel-Indexübereinstimmung 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> retournieren 86

Oder Sie können Zellbezüge verwenden:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> retournieren 86
Hinweis: Vergessen Sie nicht, mit einzugeben Strg + Umschalt + Enter.


INDEX und MATCH, um eine Suche mit mehreren Kriterien anzuwenden

Bei einer großen Datenbank mit mehreren Spalten und Zeilenbeschriftungen ist es immer schwierig, etwas zu finden, das mehrere Bedingungen erfüllt. In diesem Fall verwenden Sie die folgende Formel, um mehrere Kriterien zu suchen:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Strg + Umschalt + Enter. Ein Paar geschweifte Klammern wird dann in der Bearbeitungsleiste angezeigt.

Um zum Beispiel die zu finden Abschlussprüfung von Coco der Klasse A, die aus Indien kommt, die Formel lautet wie folgt:

Excel-Indexübereinstimmung 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> retournieren 88
√ Hinweis: Vergessen Sie nicht, mit einzugeben Strg + Umschalt + Enter.

Nun, was ist, wenn Sie ständig vergessen zu verwenden Strg + Umschalt + Enter die Formel so vervollständigen, dass die Formel falsche Ergebnisse liefert? Hier haben wir eine komplexere Formel, mit der Sie einfach mit einem einfachen vervollständigen können Weiter Schlüssel:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Für das gleiche Beispiel oben finden Sie die Abschlussprüfung von Coco der Klasse A, die aus Indien kommt, die Formel, die nur ein übliches braucht Weiter Treffer ist wie folgt:

Excel-Indexübereinstimmung 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> retournieren 88

Hier werden wir keine hartcodierten Werte verwenden, da wir im Fall mit mehreren Kriterien eine universelle Formel benötigen. Nur so können wir leicht das gewünschte Ergebnis erzielen, indem wir die Werte in den Zellen G2, G3, G4 im obigen Beispiel ändern.

mit Kutools for Excel's Multi-Condition-Lookup-Funktion: Sie können mit wenigen Klicks nach bestimmten Werten mit mehreren Kriterien suchen. Um die Funktion zu implementieren, gehen Sie bitte zum Kutools Tab in Ihrem Excel, finden Sie die Formel Gruppe, und klicken Sie auf Suche nach mehreren Bedingungen in der Dropdown-Liste von Super NACHSCHLAG. Sie sehen dann ein Popup-Dialogfeld wie unten gezeigt:

Excel-Indexübereinstimmung 14

Klicken Sie hier für konkrete Schritte zum Anwenden der Multi-Condition-Lookup-Funktion mit Kutools for Excel.


INDEX und MATCH, um eine Suche über mehrere Spalten hinweg anzuwenden

Wenn wir eine Excel-Tabelle mit verschiedenen Spalten haben, die wie unten gezeigt eine Überschrift teilen, wie können wir dann den Namen jedes Schülers mit seiner Klasse mit INDEX und MATCH abgleichen?

Excel-Indexübereinstimmung 15

Hier zeige ich Ihnen, wie Sie die Aufgabe mit unserem Profi-Tool lösen können Kutools for Excel. Mit Formel-Helfer, Sie können Schüler wie folgt schnell ihren Klassen zuordnen:

1. Wählen Sie die Zielzelle aus, auf die Sie die Funktion anwenden möchten.

2. Unter dem Kutools Tab, gehe zu Formel-Helfer, klicken Formel-Helfer in der Dropdown-Liste.

Excel-Indexübereinstimmung 16

3 Wählen Nach Formeltyp suchen, dann klicken Sie auf Index und Übereinstimmung für mehrere Spalten.

Excel-Indexübereinstimmung 17

4. a. Klicken Sie auf die 1. Excel-Index-Match-SymbolKnopf auf der rechten Seite von Lookup_col , um die Zellen auszuwählen, von denen Sie einen Wert zurückgeben möchten, dh die Klassennamen. (Sie können hier nur eine einzelne Spalte oder Zeile auswählen.)
    B. Klicken Sie auf die 2. Excel-Index-Match-SymbolKnopf auf der rechten Seite von Tabelle_rng um die Zellen auszuwählen, die den Werten im ausgewählten . entsprechen Lookup_col, dh die Namen der Schüler.
    C. Klicken Sie auf das 3. Excel-Index-Match-SymbolKnopf auf der rechten Seite von Lookup-Wert , um die Zelle auszuwählen, die nachgeschlagen werden soll, dh den Namen des Schülers, den Sie seiner Klasse zuordnen möchten.

Excel-Indexübereinstimmung 18

5. Klicken Sie auf Ok, Sie sehen den Klassennamen von Jimmy in der Zielzelle.

Excel-Indexübereinstimmung 19

6. Jetzt können Sie den Ausfüllgriff nach unten ziehen, um die Klassen anderer Schüler auszufüllen.

Excel-Indexübereinstimmung 20

Klicken Sie hier, um Kutools for Excel für eine 30-tägige kostenlose Testversion herunterzuladen.



  • Super Formula Bar (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen und Speichern von Daten; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen und Summe / Durchschnitt... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Lieblingsformeln und schnell einfügen, Bereiche, Diagramme und Bilder; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Pivot-Tabellengruppierung nach Wochennummer, Wochentag und mehr ... Entsperrte, gesperrte Zellen anzeigen durch verschiedene Farben; Markieren Sie Zellen mit Formel / Name...
kte tab 201905
  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Erhöht Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.