INDEX and MATCH ist das beliebteste Tool in Excel zum Durchführen komplexerer Lookups. Dies liegt daran, dass INDEX und MATCH unglaublich flexibel sind – Sie können horizontale und vertikale Lookups, 2-Wege-Lookups, Left-Lookups, Lookups mit Berücksichtigung der Groß-/Kleinschreibung und sogar Lookups basierend auf mehreren Kriterien durchführen. Wenn Sie Ihre Excel-Kenntnisse verbessern möchten, sollten INDEX und MATCH auf Ihrer Liste stehen.
In diesem Artikel wird in einfachen Worten erklärt, wie Sie INDEX und MATCH zusammen verwenden, um Suchvorgänge durchzuführen. Es erfordert einen schrittweisen Ansatz, der zuerst INDEX, dann MATCH erklärt und Ihnen dann zeigt, wie Sie die beiden Funktionen miteinander kombinieren, um eine dynamische bidirektionale Suche zu erstellen. Weiter unten auf der Seite gibt es weiterführende Beispiele.
INDEX-Funktion | MATCH-Funktion | INDEX und MATCH | 2-Wege-Suche | Linke Suche | Groß-/Kleinschreibung beachten | Engste Übereinstimmung | Mehrere Kriterien | Mehr Beispiele
Die INDEX-Funktion
Die INDEX-Funktion in Excel ist unglaublich flexibel und leistungsstark und Sie finden sie in einer Vielzahl von Excel-Formeln, insbesondere in fortgeschrittenen Formeln. Aber was macht INDEX eigentlich? Kurz gesagt, INDEX ruft den Wert an einer bestimmten Position in einem Bereich ab. Nehmen wir zum Beispiel an, Sie haben eine Planetentabelle in unserem Sonnensystem (siehe unten) und möchten den Namen des vierten Planeten, Mars, mit einer Formel erhalten. Sie können INDEX wie folgt verwenden:
= INDEX (B3:B11,4)
INDEX gibt den Wert in der 4. Zeile des Bereichs zurück.
Video: So suchen Sie mit INDEX . nach
Was ist, wenn Sie mit INDEX den Durchmesser des Mars ermitteln möchten? In diesem Fall können wir sowohl eine Zeilennummer als auch eine Spaltennummer angeben und einen größeren Bereich bereitstellen. Die folgende INDEX-Formel verwendet den gesamten Datenbereich in B3:D11 mit einer Zeilennummer von 4 und einer Spaltennummer von 2:
= INDEX (B3:D11,4,2)
INDEX ruft den Wert in Zeile 4, Spalte 2 ab.
Zusammenfassend ruft INDEX einen Wert an einer bestimmten Position in einem Zellbereich basierend auf der numerischen Position ab. Wenn der Bereich eindimensional ist, müssen Sie nur eine Zeilennummer angeben. Wenn der Bereich zweidimensional ist, müssen Sie sowohl die Zeilen- als auch die Spaltennummer angeben.
An diesem Punkt denken Sie vielleicht: „Na und? Wie oft kennen Sie eigentlich die Position von etwas in einer Tabellenkalkulation?'
Genau richtig. Wir brauchen eine Möglichkeit, die Position der gesuchten Dinge zu lokalisieren.
Rufen Sie die MATCH-Funktion auf.
Die MATCH-Funktion
Die MATCH-Funktion wurde für einen Zweck entwickelt: die Position eines Elements in einem Bereich zu finden. Zum Beispiel können wir MATCH verwenden, um die Position des Wortes 'Pfirsich' in dieser Liste von Früchten wie folgt zu ermitteln:
= MATCH ('peach',B3:B9,0)
MATCH gibt 3 zurück, da 'Peach' das dritte Element ist. Bei MATCH wird die Groß-/Kleinschreibung nicht beachtet.
MATCH ist egal, ob ein Bereich horizontal oder vertikal ist, wie Sie unten sehen können:
= MATCH ('peach',C4:I4,0)
Gleiches Ergebnis mit horizontalem Bereich, MATCH gibt 3 zurück.
Video: So verwenden Sie MATCH für genaue Übereinstimmungen
Wichtig: Das letzte Argument in der MATCH-Funktion ist der Übereinstimmungstyp. Der Übereinstimmungstyp ist wichtig und steuert, ob die Übereinstimmung genau oder ungefähr ist. In vielen Fällen möchten Sie Null (0) verwenden, um ein exaktes Übereinstimmungsverhalten zu erzwingen. Der Übereinstimmungstyp ist standardmäßig auf 1 eingestellt, was eine ungefähre Übereinstimmung bedeutet, daher ist es wichtig, einen Wert anzugeben. Siehe die MATCH-Seite für mehr Details.
INDEX und MATCH zusammen
Nachdem wir nun die Grundlagen von INDEX und MATCH behandelt haben, wie kombinieren wir die beiden Funktionen in einer einzigen Formel? Betrachten Sie die folgenden Daten, eine Tabelle mit einer Liste von Vertriebsmitarbeitern und monatlichen Verkaufszahlen für drei Monate: Januar, Februar und März.
Angenommen, wir möchten eine Formel schreiben, die die Verkaufsnummer für Februar für einen bestimmten Verkäufer zurückgibt. Aus der obigen Diskussion wissen wir, dass wir INDEX eine Zeilen- und Spaltennummer geben können, um einen Wert abzurufen. Um beispielsweise die Februar-Verkaufsnummer für Frantz zurückzugeben, stellen wir den Bereich C3:E11 mit einer Zeile 5 und Spalte 2 bereit:
= INDEX (C3:E11,5,2) // returns 94
Aber wir wollen natürlich keine Zahlen fest codieren. Stattdessen wollen wir a dynamisch Schau hoch.
Wie werden wir das tun? Die MATCH-Funktion natürlich. MATCH wird perfekt funktionieren, um die Positionen zu finden, die wir brauchen. Lassen Sie uns Schritt für Schritt die Spalte fest als 2 kodiert und machen Sie die Zeilennummer dynamisch. Hier ist die überarbeitete Formel, wobei die MATCH-Funktion anstelle von 5 in INDEX verschachtelt ist:
= INDEX (C3:E11, MATCH ('Frantz',B3:B11,0),2)
Wir gehen noch einen Schritt weiter und verwenden den Wert von H2 in MATCH:
= INDEX (C3:E11, MATCH (H2,B3:B11,0),2)
MATCH findet 'Frantz' und gibt 5 an INDEX für Zeile zurück.
Zusammenfassen:
- INDEX benötigt numerische Positionen.
- MATCH findet diese Positionen.
- MATCH ist verschachtelt innen INDEX.
Kommen wir nun zur Spaltennummer.
Zwei-Wege-Suche mit INDEX und MATCH
Oben haben wir die MATCH-Funktion verwendet, um die Zeilennummer dynamisch zu finden, aber die Spaltennummer hartcodiert. Wie können wir die Formel vollständig dynamisch gestalten, damit wir die Verkäufe für jeden bestimmten Verkäufer in einem bestimmten Monat zurückgeben können? Der Trick besteht darin, MATCH zweimal zu verwenden – einmal, um eine Zeilenposition zu erhalten, und einmal, um eine Spaltenposition zu erhalten.
Aus den obigen Beispielen wissen wir, dass MATCH sowohl mit horizontalen als auch mit vertikalen Arrays gut funktioniert. Das bedeutet, dass wir mit MATCH leicht die Position eines bestimmten Monats finden können. Diese Formel gibt beispielsweise die Position von März zurück, die 3 ist:
= MATCH ('Mar',C2:E2,0) // returns 3
Aber natürlich wollen wir nicht hart kodieren irgendein Werte, also aktualisieren wir das Arbeitsblatt, um die Eingabe eines Monatsnamens zu ermöglichen, und verwenden Sie MATCH, um die benötigte Spaltennummer zu finden. Der folgende Bildschirm zeigt das Ergebnis:
Eine vollständig dynamische Zwei-Wege-Suche mit INDEX und MATCH.
= INDEX (C3:E11, MATCH (H2,B3:B11,0), MATCH (H3,C2:E2,0))
Die erste MATCH-Formel gibt 5 an INDEX als Zeilennummer zurück, die zweite MATCH-Formel gibt 3 an INDEX als Spaltennummer zurück. Sobald MATCH ausgeführt wird, vereinfacht sich die Formel zu:
= INDEX (C3:E11,5,3)
und INDEX gibt korrekt 10.525 US-Dollar zurück, die Verkaufszahl von Frantz im März.
Hinweis: Sie könnten verwenden Datenvalidierung um Dropdown-Menüs zu erstellen, um Verkäufer und Monat auszuwählen.
Video: So führen Sie eine bidirektionale Suche mit INDEX und MATCH durch
Video: So debuggen Sie eine Formel mit F9 (um die Rückgabewerte von MATCH anzuzeigen)
Linke Suche
Einer der Hauptvorteile von INDEX und MATCH gegenüber der SVERWEIS-Funktion ist die Möglichkeit, einen „Linken Lookup“ durchzuführen. Einfach ausgedrückt bedeutet dies nur eine Suche, wo sich die ID-Spalte befindet rechts der Werte, die Sie abrufen möchten, wie im folgenden Beispiel zu sehen:
Lesen Sie hier eine ausführliche Erklärung .
Groß-/Kleinschreibung beachten
Die MATCH-Funktion selbst unterscheidet nicht zwischen Groß- und Kleinschreibung. Sie verwenden jedoch die EXAKT-Funktion mit INDEX und MATCH, um eine Suche durchzuführen, die Groß- und Kleinschreibung berücksichtigt, wie unten gezeigt:
Lesen Sie hier eine ausführliche Erklärung .
Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365 .
Engste Übereinstimmung
Ein weiteres Beispiel, das die Flexibilität von INDEX und MATCH zeigt, ist das Problem, die engste Übereinstimmung . Im folgenden Beispiel verwenden wir die MIN-Funktion zusammen mit dem ABS-Funktion zu schaffen ein Lookup-Wert und ein Lookup-Array Innerhalb die MATCH-Funktion. Im Wesentlichen verwenden wir MATCH, um den kleinsten Unterschied zu finden. Dann verwenden wir INDEX, um die zugehörige Fahrt aus Spalte B abzurufen.
Lesen Sie hier eine ausführliche Erklärung .
Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365 .
Suche nach mehreren Kriterien
Eines der schwierigsten Probleme in Excel ist eine Suche basierend auf mehreren Kriterien. Mit anderen Worten, eine Suche, die gleichzeitig in mehr als einer Spalte übereinstimmt. Im folgenden Beispiel verwenden wir INDEX und MATCH und Boolesche Logik um in 3 Spalten abzugleichen: Artikel, Farbe und Größe:
Lesen Sie hier eine ausführliche Erklärung .
Hinweis: Dies ist ein Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden, außer in Excel 365 .
Excel-Anzeige Wochentag Name
Weitere Beispiele für INDEX + MATCH
Hier sind einige grundlegendere Beispiele für INDEX und MATCH in Aktion, jedes mit einer detaillierten Erklärung:
- Basic INDEX und MATCH exakt (mit Toy Story)
- Basic INDEX und MATCH ungefähr (Noten)
- Zwei-Wege-Suche mit INDEX und MATCH (ungefähre Übereinstimmung)
Exceljet-Index und match.xlsx
