Excel

So verwenden Sie INDEX und MATCH

How Use Index Match

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)

Verwenden von INDEX, um den Namen des 4. Planeten zu erhalten
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)

Mit INDEX den Durchmesser des 4. Planeten ermitteln
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)

Verwenden von MATCH, um die Position in einem vertikalen Bereich zu finden
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)

Verwenden von MATCH, um die Position in einem horizontalen Bereich zu finden
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.

Umsatz nach Verkäufer pro Monat

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)

INDEX und MATCH, um Februar-Verkäufe für jeden Namen zu finden
MATCH findet 'Frantz' und gibt 5 an INDEX für Zeile zurück.

Zusammenfassen:

  1. INDEX benötigt numerische Positionen.
  2. MATCH findet diese Positionen.
  3. 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:

Dynamische Suche mit INDEX und MATCH
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:

Linkssuche mit INDEX und MATCH

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:

Groß-/Kleinschreibung beachten mit INDEX und MATCH

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.

Finden Sie die nächste Übereinstimmung mit INDEX und MATCH

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:

INDEX und MATCH mit mehreren Kriterien

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:

Autor Dave Bruns Anhänge Datei Exceljet-Index und match.xlsx


^