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
- Beispiel zum Kombinieren von INDEX und MATCH
- INDEX und MATCH, um eine Linkssuche anzuwenden
- INDEX und MATCH, um eine bidirektionale Suche anzuwenden
- INDEX und MATCH, um eine Groß-/Kleinschreibung anzuwenden
- INDEX und MATCH, um eine Suche mit mehreren Kriterien anzuwenden
- INDEX und MATCH, um eine Suche über mehrere Spalten hinweg anzuwenden
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:
- 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
√ 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
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:
- 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
√ 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
☞ 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
Nun, da die Formel kompliziert aussehen kann, lassen Sie uns jeden Teil davon durchgehen.
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))
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
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:
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:
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:
√ 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:
=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:
√ 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:
=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:
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:
=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:
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?
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.
3 Wählen Nach Formeltyp suchen, dann klicken Sie auf Index und Übereinstimmung für mehrere Spalten.
4. a. Klicken Sie auf die 1. 
Knopf 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. Knopf 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. Knopf 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.
5. Klicken Sie auf Ok, Sie sehen den Klassennamen von Jimmy in der Zielzelle.
6. Jetzt können Sie den Ausfüllgriff nach unten ziehen, um die Klassen anderer Schüler auszufüllen.
Die besten Tools für die Office-Produktivität
Kutools for Excel löst die meisten Ihrer Probleme und erhöht Ihre Produktivität um 80%
- 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...
- 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!
You are guest
or post as a guest, but your post won't be published automatically.
Be the first to comment.
