Wie man mit INDEX MATCH mehrere Kriterien in Excel erfüllt

Vor ein paar Jahren habe ich an einer Analyse einer Marketingkampagne gearbeitet, bei der ich die Verkaufsleistung in verschiedenen Regionen vergleichen musste. Die Daten waren auf mehrere Excel-Tabellen verteilt, und ich musste die Verkaufszahlen bestimmter Produkte in einem einzigen Bericht zusammenfassen. Zuerst habe ich versucht, die Daten manuell zu suchen und zu kopieren, aber das war nicht so einfach, wie ich dachte. Wenn es eine falsche Zeile gibt, kann der ganze Bericht auseinanderfallen.

So bin ich auf INDEX MATCH(). gestoßen. Ich brauchte ein paar Versuche, um die Formel richtig hinzubekommen, aber als ich gesehen habe, wie einfach es ist, die genauen Zahlen zu finden und zu ermitteln, die ich brauche, wurde es Teil meiner Routine. Mit nur zwei Funktionen konnte ich genau die Daten abrufen, die ich brauchte, egal wie verstreut sie in den Tabellenblättern lagen.

In diesem Artikel erkläre ich dir, wie du das auch mit den Funktionen INDEX() und MATCH() machen kannst. Bei Excel gibt es immer mehr zu lernen. Wenn du ein Anfänger bist, empfehle ich dir unseren Kurs Einführung in Excel. Wenn du mehr Erfahrung hast, probiere unseren Kurs Excel-Funktionen für Fortgeschrittene aus.

Eine Auffrischung zu INDEX MATCH

INDEX MATCH ist ist eine Kurzbezeichnung für die Kombination von zwei Excel-Funktionen, die zusammenarbeiten, um erweiterte Suchvorgänge durchzuführen. Wir könnten diese Idee auch als INDEX(MATCH()) bezeichnen, aber in diesem Artikel werde ich INDEX MATCH wählen. Schauen wir uns nun beide nacheinander an:

Mit der Funktion INDEX() kannst du den Wert einer Zelle anhand ihrer Position innerhalb eines bestimmten Bereichs ermitteln. Hier ist die Syntax:

=INDEX(array, row_num, [column_num])

Hier:

• array ist der Bereich von Zellen, aus dem du einen Wert abrufen möchtest.

• row_num ist die Zeilennummer im Array, aus dem ein Wert zurückgegeben werden soll.

• column_num (optional) ist die Spaltennummer im Array, aus dem ein Wert zurückgegeben werden soll.

Die Funktion MATCH() identifiziert die relative Position eines Wertes innerhalb eines Bereichs. Die Syntax lautet:

=MATCH(lookup_value, lookup_array, [match_type])

Hier:

• lookup_value ist der Wert, den du finden willst.

• lookup_array ist der Bereich, in dem die Funktion nach dem Wert sucht.

match_type ist optional. 1 (Standard) findet den Wert, der kleiner oder gleich lookup_value ist (das Array muss in aufsteigender Reihenfolge sortiert sein). 0 findet eine exakte Übereinstimmung (das Feld muss nicht sortiert sein). -1 findet den kleinsten Wert, der größer oder gleich lookup_value ist (das Array muss absteigend sortiert sein).

Wie man INDEX() mit MATCH() kombiniert

Indem wir MATCH() innerhalb von INDEX() verschachteln, können wir eine dynamische Suche erstellen. Lass uns das anhand eines Beispiels verstehen: Angenommen, du möchtest die Position von David Wilson im Datensatz finden. Anstatt die Zeilennummer in INDEX() fest zu codieren, kannst du sie mit MATCH() ermitteln:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

In der obigen Formel gibt MATCH("David Wilson", A2:A6, 0) 4 zurück , ist die Zeilenposition. Und INDEX(C2:C6, 4) ruft den Wert aus der 4. Zeile des Bereichs C2:C6 ab, der Seattle ist .

Kombiniere INDEX() mit MATCH(). Bild vom Autor.

Um das Ganze noch dynamischer zu gestalten, kannst du den fest codierten David Wilson durch einen Zellbezug ersetzen . Auf diese Weise passt sich die Formel automatisch an den Wert in D4 an:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Ersetze den hartkodierten Wert in der INDEX MATCH. Bild vom Autor.

INDEX MATCH vs. VLOOKUP()

Jetzt, da du weißt, wie INDEX() und MATCH() einzeln funktionieren und wie die Kombination der beiden Nachschlagewerke für mehr Dynamik sorgt, wollen wir sehen, warum INDEX MATCH eine bessere Wahl ist als VLOOKUP().

• Im Gegensatz zu VLOOKUP(),, das voraussetzt, dass sich die Suchspalte auf der linken Seite befindet, kannst du mit INDEX MATCH Daten aus jeder Spalte abrufen, unabhängig von ihrer Position.

• INDEX MATCH verarbeitet nur den gewünschten Bereich von Zellen, während VLOOKUP(), ganze Tabellen durchsucht. 

• Formeln, die VLOOKUP() verwenden, können kaputt gehen, wenn Spalten eingefügt oder gelöscht werden, da sie auf statischen Spaltenindizes beruhen. Auf der anderen Seite verweist INDEX MATCH auf dynamische Bereiche, um sicherzustellen, dass deine Formeln auch bei strukturellen Änderungen deiner Daten intakt bleiben.

• Mit INDEX MATCH, müssen wir die Spaltennummern nicht manuell zählen. Gib die Nachschlage- und die Rückgabespalte an, und schon bist du fertig. 

INDEX MATCH mit mehreren Kriterien

Ich muss oft mit Datensätzen arbeiten, die doppelte Einträge enthalten, und es ist extrem schwierig, darin Werte zu finden. Aber jetzt verwende ich INDEX MATCH, weil es diese Szenarien im Gegensatz zu anderen Standard-Lookup-Formeln sehr einfach handhabt. Ich zeige dir Schritt für Schritt, wie ich das verwende.

Daten für mehrere Kriterien einrichten

Erstelle zunächst deinen Datensatz und achte darauf, dass er gut in einer Tabelle mit klaren Überschriften für jede Spalte organisiert ist. Jede Zeile sollte einen eindeutigen Datensatz darstellen, und jede Spalte sollte ein bestimmtes Datenattribut enthalten.

Hier ist zum Beispiel ein Beispiel-Datensatz:

Datensatz für INDEX MATCH Mehrfachkriterien. Bild vom Autor.

Schreibe die Formel für mehrere Kriterien

Wenn deine Daten richtig organisiert sind, ist es an der Zeit, die Formel zu schreiben. Die Formel INDEX MATCH ruft einen Wert aus einer anderen Spalte ab, indem sie eine Zeile identifiziert, die mehrere Bedingungen erfüllt. Dies geschieht durch die Kombination von logischen Tests innerhalb der Funktion MATCH() und deren Einbettung in die Funktion INDEX().

Hier ist die grundlegende Syntax dafür: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Hier: 

• Return_range ist der Bereich, aus dem der Wert zurückgegeben werden soll.

• Criteria1, Criteria2, … sind die Bedingungen, die erfüllt werden müssen.

• Range1, Range2, … sind Bereiche, die den Kriterien entsprechen.

Jetzt, wo wir die Daten eingerichtet haben, schauen wir uns die beiden Methoden genauer an, um unsere Frage zu beantworten: wie man INDEX MATCH mit mehreren Kriterien verwendet.

Hilfsspalten für komplexe Kriterien verwenden

Wenn dein Datensatz mehrere Bedingungen enthält, kannst du Hilfsspalten verwenden, um den Prozess zu vereinfachen. Sie fasst alle Bedingungen in einer einzigen Spalte zusammen, um das Nachschlagen zu erleichtern. Ich verwende zum Beispiel denselben Datensatz, um eine Hilfsspalte zu erstellen, indem ich die Spalten Vorname und Gehalt kombiniere:

=A2&B2

Erstelle eine Hilfsspalte. Bild vom Autor.

Diese Hilfsspalte vereinfacht meine INDEX MATCH Formel. Anstatt eine komplexe Array-Formel mit mehreren Bedingungen zu schreiben, habe ich die Hilfsspalte in meiner Formel referenziert, um einen einfacheren Ansatz zu wählen:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH mit Hilfsspalte. Bild vom Autor.

Kombiniere mehrere Kriterien mit einer Array-Formel

Wenn du keine Hilfsspalten bevorzugst, kannst du Array-Formeln verwenden, um das gleiche Ergebnis zu erzielen. Sie ermöglichen es dir, mehrere Kriterien direkt in der Funktion MATCH() zu bewerten. So finde ich zum Beispiel Alice' Gehalt in der Personalabteilung:

Schritt 1: Schreibe die Funktion MATCH() mit logischen Bedingungen:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

In dieser Formel sorgt die 1 dafür, dass die Funktion MATCH() nach Zeilen sucht, in denen alle Bedingungen wahr sind. (F4=A2:A11) prüft, ob der Wert in F4 mit einem Wert im Bereich A2:A11übereinstimmt . (F5=B2:B11) prüft, ob der Wert in F5 mit einem Wert im Bereich B2:B11übereinstimmt . Der * Operator fungiert als AND Logik, die sicherstellt, dass alle Bedingungen erfüllt sind.

Schritt 2: Schließe diese MATCH() Funktion in die INDEX() Funktion ein:

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Schritt 3: Stelle die Formel fertig. Wenn du eine ältere Version von Excel verwendest, drücke Strg+Umschalt+Eingabe, um die Formel zu einem Array zu machen. In neueren Versionen drückst du Enter.

Array INDEX MATCH mit mehreren Kriterien. Bild vom Autor.

Erweiterte Verwendungen für INDEX MATCH mit mehreren Kriterien

Mit der Funktion INDEX MATCH kannst du so viel mehr machen. Lass uns sehen, wie: 

INDEX MATCH mit benannten Bereichen und dynamischen Bereichen verwenden

Ich verwende benannte Bereiche in Excel, um aussagekräftige Namen wie results oder totalSales anstelle von Standardreferenzen wie A1:A10festzulegen . Auf diese Weise wird es einfacher, Formeln über verschiedene Blätter hinweg zu verwalten. 

Um einen Zellbereich zu benennen, markiere die Zellen und drücke Strg + F3 (Windows) bzw. Cmd + F3 (Mac), um den Namensmanager zu öffnen . Klicke dann auf Neu, gib einen Namen ein und klicke auf OK.

Benenne den Bereich. Bild vom Autor.

Der einzige Unterschied zwischen einem benannten Bereich und einem dynamischen Bereich besteht darin, dass sich ein benannter Bereich auf eine feste Gruppe von Zellen bezieht, während sich ein dynamischer Bereich automatisch anpasst, wenn Daten hinzugefügt oder entfernt werden.

Um einen dynamischen Bereich festzulegen, wähle die Zellen aus. Klicken Sie auf derRegisterkarte Formeln auf Namensmanager oder drücken Sie Strg + F3 , um den Excel-Namensmanagerzu öffnen , und klicken Sie auf Neu. Das Dialogfeld Neuer Name wird angezeigt. Gib nun in das Feld Name deinen gewünschten Namen ein. Gib dann in dasFeld Bezieht sich auf die Formel für den Dynamikbereich ein .

Lege einen Dynamikbereich fest. Bild vom Autor. 

Schauen wir uns nun ein Beispiel an: Ich habe zwei dynamische und einen statischen Bereich definiert:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Jetzt verwende ich diese Bereiche in der Formel INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Und du kannst sehen, dass die Formel mit klaren Namen viel einfacher zu verstehen ist.

Verwende dynamische und benannte Bereiche mit INDEX MATCH. Bild vom Autor.

Verschachteltes INDEX MATCH für komplexe Suchanfragen

Abgesehen von den grundlegenden Aufgaben kannst du mit den verschachtelten Funktionen von INDEX MATCH auch komplexe Suchvorgänge durchführen. Ich habe zum Beispiel einen Datensatz, der die Verkäufe nach Produktkategorie in verschiedenen Regionen zeigt. 

Rohdatensatz. Bild vom Autor.

Ich möchte Möbelverkäufe im Ostenfinden . Dazu muss ich aber sowohl die Produktkategorie (Zeile) als auch die Region (Spalte) abgleichen, was mit INDEX MATCH nicht möglich ist. Deshalb verwende ich hier die folgende verschachtelte INDEX MATCH Formel:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

So funktioniert es: Die INDEX() zieht einen Wert aus dem Bereich B2:D4, aber sie braucht eine Zeilennummer und eine Spaltennummer, um genau zu wissen, wo sie suchen muss. Die erste MATCH(D6, A2:A4, 0) findet also die Zeilennummer heraus. Wenn D6Möbelenthält , sucht es in der Spalte A2:A4 und findet sie in der zweiten Zeile .

Als nächstes bestimmt MATCH(D7, B1:D1, 0) die Spaltennummer. Wenn D7Osten sagt , schaut es in B1:D1 nach und findet es in der zweiten Spalte .

Sobald INDEX() die Zeile und Spalte kennt, zeigt es die Ausgabewerte an. In unserem Fallsind die Verkäufe für Möbel im Osten 450.

Verwende verschachtelte INDEX MATCH. Bild vom Autor.

Ich verwende diese Formel, anstatt manuell die Zeilen und Spalten zu durchsuchen, weil sie alles präzise behandelt. 

Häufige Herausforderungen und Tipps zur Fehlerbehebung

Als ich anfing, INDEX MATCH, zu nutzen, stieß ich auf einige Herausforderungen, und ich möchte nicht, dass du dieselben Frustrationen erlebst. Deshalb gehe ich mit dir die häufigsten Herausforderungen durch und zeige dir, wie du sie meistern kannst.

Behandlung von Fehlern in INDEX MATCH-Formeln

Fehler wie #N/A und #VALUE! können auf den ersten Blick frustrierend sein, aber sie sind ziemlich einfach zu beheben. Wir zeigen dir, wie du die Ursache des Problems erkennst und wie du es in einfachen Schritten lösen kannst.

Der Fehler #N/A tritt auf, wenn die Funktion MATCH() keinen Wert findet. Das liegt daran, dass der Nachschlagewert nicht im Suchfeld vorhanden ist oder die Daten versteckte Leerzeichen enthalten. Ich habe zum Beispiel einmal die falsche Spalte referenziert, während ich die Namen der Mitarbeiter:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

#N/A Fehler in INDEX MATCH. Bild vom Autor.

Um solche Probleme zu beheben, bestätige, dass der Nachschlagewert im Array vorhanden ist und verwende die Funktion TRIM(), um Leerzeichen zu entfernen:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

#N/A Fehler in INDEX MATCH behoben. Bild vom Autor.

#VALUE! Fehler erscheint, wenn die Formel nicht als Array-Formel eingestellt ist. Wenn ich zum Beispiel die Funktion MATCH() verwende und mehr als einen Bereich einbeziehe, sieht Excel dies als eine Array-Formel an. Wenn sie jedoch nicht richtig eingerichtet ist, gibt Excel einen #VALUE! Fehler aus.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

#Wertfehler in INDEX MATCH. Bild vom Autor.

Um das Problem zu lösen, drückst du Strg + Umschalt + Enter nachdem du die Formel eingegeben hast. Auf diese Weise wickelt Excel die Formel in geschweifte Klammern {} ein und zeigt damit an, dass es sich jetzt um eine Array-Formel handelt. Tippe die geschweiften Klammern aber nicht von Hand ein, denn das würde die Formel zerstören.

#Wertfehler in INDEX MATCH behoben. Bild vom Autor.

Optimiere die Leistung bei großen Datensätzen

Bei größeren Datensätzen wurden meine Formeln von Zeit zu Zeit langsamer, so dass ich auf die Aktualisierung der Berechnungen warten musste. Wenn du auch mit ähnlichen Problemen zu kämpfen hast, probiere diese Tipps aus: 

• Schränke den Nachschlagebereich ein: Beschränke die Bereiche auf das Nötigste. Verwende zum Beispiel statt A:AA1:A100 , um die Berechnungszeit zu verkürzen.

• Verwende Hilfsspalten: Berechne komplexe Kriterien mit Hilfsspalten vor. Dadurch wird der Rechenaufwand für Array-Formeln verringert.

• Aktiviere den manuellen Berechnungsmodus: Schalte Excel in den manuellen Berechnungsmodus, um ständige Neuberechnungen zu vermeiden. Nachdem du Änderungen vorgenommen hast, drücke F9, um die Formeln manuell zu aktualisieren.

• Vermeide flüchtige Funktionen: Minimiere mit flüchtigen Funktionen wie NOW(), RAND(), und TODAY() in Kombination mit INDEX MATCH. Diese Funktionen lösen jedes Mal, wenn die Arbeitsmappe aktualisiert wird, Neuberechnungen aus.

Schlussgedanken 

INDEX MATCH Techniken sparen Zeit und vereinfachen komplexe Datenanalysen. Wenn du mit großen Datenmengen arbeitest, können sie einen Versuch wert sein. Aber der beste Weg, dein Verständnis zu festigen, ist die Praxis. Ich würde also sagen, nimm dir ein paar Datensätze vor und experimentiere mit dem, was du gelernt hast. Auf diese Weise habe ich meine Fähigkeiten geschärft. 

Um dein Wissen zu vertiefen, besuche unseren Kurs Excel-Funktionen für Fortgeschrittene, um eine breitere Palette leistungsstarker Tools zu beherrschen. Wenn du dir aber ein umfassendes Fachwissen über Datenanalyse in Excel aneignen willst, empfehle ich dir unseren Kurs Datenanalyse in Excel. Es umfasst alles von der Datenaufbereitung bis zur Visualisierung.