INDEX-MATCH ist zu einem immer beliebteren Tool für Excel geworden, da es die Einschränkung der VLOOKUP-Funktion beseitigt und einfacher zu verwenden ist. Die INDEX-MATCH-Funktion in Excel bietet gegenüber der VLOOKUP-Funktion eine Reihe von Vorteilen:

1. INDEX und MATCH sind flexibler und schneller als Vlookup
2. Es ist möglich, eine horizontale Suche, eine vertikale Suche, eine bidirektionale Suche, eine Suche nach links, eine Suche unter Berücksichtigung der Groß-/Kleinschreibung und sogar eine Suche basierend auf mehreren Kriterien durchzuführen.
3. Bei sortierten Daten ist INDEX-MATCH 30 % schneller als VLOOKUP. Das bedeutet, dass in einem größeren Datensatz eine Geschwindigkeit von 30 % sinnvoller ist.

Beginnen wir mit den detaillierten Konzepten jedes INDEX und MATCH.

INDEX-Funktion

Die INDEX-Funktion in Excel ist sehr leistungsfähig und gleichzeitig ein flexibles Tool, das den Wert an einer bestimmten Stelle in einem Bereich abruft. Mit anderen Worten: Es gibt den Inhalt einer Zelle zurück, angegeben durch Zeilen- und Spaltenoffset.

Syntax:

=INDEX(reference, [row], [column])>

Parameter:

Referenz: Das Array von Zellen, in die versetzt werden soll. Es kann sich um einen einzelnen Bereich oder einen gesamten Datensatz in einer Datentabelle handeln. row [optional]: Die Anzahl der versetzten Zeilen. Das heißt, wenn wir einen Tabellenreferenzbereich als A1:A5 wählen, dann hat die Zelle/der Inhalt, die/den wir extrahieren möchten, den vertikalen Abstand. Hier ist für A1 Zeile 1, für A2 Zeile = 2 und so weiter. Wenn wir row = 4 angeben, wird A4 extrahiert. Da die Zeile optional ist, werden ganze Zeilen im Referenzbereich extrahiert, wenn wir keine Zeilennummer angeben. Das sind in diesem Fall A1 bis A5. Spalte [optional]: Die Anzahl der Offset-Spalten. Das heißt, wenn wir einen Tabellenreferenzbereich als A1:B5 wählen, dann hat die Zelle/der Inhalt, die/den wir extrahieren möchten, den horizontalen Abstand. Hier ist für A1 die Zeile 1 und die Spalte 1, für B1 ist die Zeile 1, aber die Spalte ist 2, ähnlich ist für A2 Zeile = 2, Spalte = 1, für B2 Zeile = 2, Spalte = 2 und so weiter. Wenn wir Zeile = 5 und Spalte 2 angeben, wird B5 extrahiert. Da die Spalte optional ist und wir keine Zeilennummer angeben. Dann wird die gesamte Spalte im Referenzbereich extrahiert. Wenn wir beispielsweise Zeile = 2 und Spalte als leer angeben, wird (A2:B2) extrahiert. Wenn wir nicht sowohl Zeile als auch Spalte angeben, wird die gesamte Referenztabelle (A1:B5) extrahiert.

Referenztabelle: Die folgende Tabelle dient als Referenztabelle für alle Beispiele der INDEX-Funktion. Die erste Zelle ist bei B3 (FOOD) und die letzte Diagonalzelle ist bei F10 (180).

Beispiele: Nachfolgend finden Sie einige Beispiele für Indexfunktionen.

Fall 1: Es werden keine Zeilen und Spalten erwähnt.

Eingabebefehl: =INDEX(B3:C10)

Fall 2: Es werden nur Zeilen erwähnt.

Eingabebefehl: =INDEX(B3:C10,2)

Fall 3: Es werden sowohl Zeilen als auch Spalten erwähnt.

Eingabebefehl: =INDEX(B3:D10,4,2)

Fall 4: Es werden nur Spalten erwähnt.

Eingabebefehl: =INDEX(B3 : D10 , , 2)

Problem mit der INDEX-Funktion: Das Problem mit der INDEX-Funktion besteht darin, dass Zeilen und Spalten für die gesuchten Daten angegeben werden müssen. Nehmen wir an, wir haben es mit einem Datensatz für maschinelles Lernen mit 10.000 Zeilen und Spalten zu tun, dann wird es sehr schwierig sein, die gesuchten Daten zu suchen und zu extrahieren. Hier kommt das Konzept der Match-Funktion zum Einsatz, die Zeilen und Spalten basierend auf einer bestimmten Bedingung identifiziert.

MATCH-Funktion

Es ruft die Position eines Elements/Werts in einem Bereich ab. Es handelt sich um eine weniger raffinierte Version eines VLOOKUP oder HLOOKUP, die nur die Standortinformationen und nicht die tatsächlichen Daten zurückgibt. Bei MATCH wird die Groß-/Kleinschreibung nicht beachtet und es ist egal, ob der Bereich horizontal oder vertikal ist.

Syntax:

=MATCH(search_key, range, [search_type])>

Parameter:

search_key: Der Wert, nach dem gesucht werden soll. Zum Beispiel 42, Cats oder I24. Bereich: Das zu durchsuchende eindimensionale Array. Es kann entweder eine einzelne Zeile oder eine einzelne Spalte sein. zB->A1:A10 , A2:D2 usw. search_type [optional]: Die Suchmethode. = 1 (Standard) findet den größten Wert, der kleiner oder gleich search_key ist, wenn der Bereich in aufsteigender Reihenfolge sortiert wird.
• = 0 findet den genauen Wert, wenn der Bereich unsortiert ist.
• = -1 findet den kleinsten Wert, der größer oder gleich search_key ist, wenn der Bereich in absteigender Reihenfolge sortiert wird.

Zeilennummern oder Spaltennummern können mit der Match-Funktion gefunden und in der Indexfunktion verwendet werden. Wenn also Details zu einem Element vorhanden sind, können alle Informationen über das Element extrahiert werden, indem die Zeile/Spalte des Elements mithilfe von Match gesucht wird Dann verschachteln Sie es in der Indexfunktion.

Referenztabelle: Die folgende Tabelle dient als Referenztabelle für alle Beispiele der MATCH-Funktion. Die erste Zelle befindet sich bei B3 (FOOD) und die letzte diagonale Zelle befindet sich bei F10 (180).

Beispiele: Nachfolgend finden Sie einige Beispiele für die MATCH-Funktion:

Fall 1: Suchtyp 0, das bedeutet „Genaue Übereinstimmung“.

Eingabebefehl: =MATCH(South Indian,C3:C10,0)

Fall 2: Suchtyp 1 (Standard).

Eingabebefehl: =MATCH(Südindisch,C3:C10)

Objektklasse in Java

Fall 3: Suchtyp -1.

Eingabebefehl: =MATCH(South Indian,C3:C10,-1)

INDEX-MATCH Gemeinsam

In den vorherigen Beispielen wurden die statischen Werte von Zeilen und Spalten in der INDEX-Funktion bereitgestellt. Nehmen wir an, dass keine Vorkenntnisse über die Zeilen- und Spaltenposition vorliegen, dann können die Zeilen- und Spaltenpositionen mithilfe der MATCH-Funktion bereitgestellt werden. Dies ist eine dynamische Methode zum Suchen und Extrahieren von Werten.

Syntax:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Referenztabelle: Es wird die folgende Referenztabelle verwendet. Die erste Zelle befindet sich bei B3 (FOOD) und die letzte diagonale Zelle befindet sich bei F10 (180).

Beispiel: Nehmen wir an, die Aufgabe besteht darin, die Kosten für Masala Dosa zu ermitteln. Es ist bekannt, dass Spalte 3 die Kosten der Artikel darstellt, die Zeilenposition von Masala Dosa ist jedoch nicht bekannt. Das Problem kann in zwei Schritte unterteilt werden:

Schritt 1: Finden Sie die Position von Masala Dosa mithilfe der Formel:

 =MATCH('Masala Dosa',B3:B10,0)>

Hier steht B3:B10 für Column Food und 0 bedeutet Exact Match. Es wird die Zeilennummer von Masala Dosa zurückgegeben.

Schritt 2: Finden Sie die Kosten für Masala Dosa. Verwenden Sie die INDEX-Funktion, um die Kosten für Masala Dosa zu ermitteln. Durch Ersetzen der obigen MATCH-Funktionsabfrage innerhalb der INDEX-Funktion an der Stelle, an der die genaue Position von Masala Dosa erforderlich ist und die Spaltennummer der Kosten 3 beträgt, was bereits bekannt ist.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Zwei-Wege-Suche mit INDEX-MATCH Together

Im vorherigen Beispiel wurde die Spaltenposition des Kostenattributs fest codiert. Es war also nicht vollständig dynamisch.

Fall 1: Nehmen wir an, dass auch die Spaltennummer der Kosten nicht bekannt ist, dann kann sie mit der Formel ermittelt werden:

 =MATCH('Cost',B3:F3,0)>

Hier stellt B3:F3 die Kopfspalte dar.

Fall 2: Wenn sowohl Zeilen- als auch Spaltenwerte über die MATCH-Funktion bereitgestellt werden (ohne Angabe eines statischen Werts), spricht man von einer bidirektionalen Suche. Dies kann mit der Formel erreicht werden:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Linke Suche

Einer der Hauptvorteile von INDEX und MATCH gegenüber der VLOOKUP-Funktion ist die Möglichkeit, eine Linkssuche durchzuführen. Dies bedeutet, dass es möglich ist, die Zeilenposition eines Elements mithilfe eines beliebigen Attributs auf der rechten Seite zu extrahieren, und der Wert eines anderen Attributs auf der linken Seite kann extrahiert werden.

Nehmen wir zum Beispiel an, Sie kaufen Lebensmittel, deren Kosten 140 Rs betragen sollten. Indirekt sagen wir: Kaufen Sie Biryani. In diesem Beispiel sind die Kosten Rs 140/- bekannt, es besteht die Notwendigkeit, das Essen zu extrahieren. Da die Spalte „Kosten“ rechts von der Spalte „Lebensmittel“ platziert ist. Wenn SVERWEIS angewendet wird, kann die linke Seite der Kostenspalte nicht durchsucht werden. Aus diesem Grund ist es mit VLOOKUP nicht möglich, den Lebensmittelnamen abzurufen.

Um diesen Nachteil zu überwinden, kann die INDEX-MATCH-Funktion Left Lookup verwendet werden.
Schritt 1: Extrahieren Sie zunächst die Zeilenposition von Cost 140 Rs mithilfe der Formel:

 =MATCH(140, D3:D10,0)>

Hier stellt D3: D10 die Spalte „Kosten“ dar, in der nach der Zeilennummer „Kosten 140 Rs“ gesucht wird.

Schritt 2: Nachdem Sie die Zeilennummer erhalten haben, besteht der nächste Schritt darin, die INDEX-Funktion zu verwenden, um den Lebensmittelnamen mithilfe der Formel zu extrahieren:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Hier stellt B3:B10 die Lebensmittelspalte dar und 140 sind die Kosten des Lebensmittelartikels.

Groß- und Kleinschreibung beachten

Die MATCH-Funktion selbst unterscheidet nicht zwischen Groß- und Kleinschreibung. Das heißt, wenn es einen Lebensmittelnamen DHOKLA gibt und die MATCH-Funktion mit dem folgenden Suchwort verwendet wird:

1. Dhokla
2. dhokla
3. DhOkLA

Alle geben die Zeilenposition von DHOKLA zurück. Die EXACT-Funktion kann jedoch mit INDEX und MATCH verwendet werden, um eine Suche durchzuführen, bei der Groß- und Kleinschreibung berücksichtigt wird.

Genaue Funktion: Die Excel-Funktion EXACT vergleicht zwei Textzeichenfolgen unter Berücksichtigung von Groß- und Kleinbuchstaben und gibt TRUE zurück, wenn sie identisch sind, und FALSE, wenn nicht. Bei EXACT wird die Groß-/Kleinschreibung beachtet.

Beispiele:

EXACT(DHOKLA,DHOKLA): Dies wird True zurückgeben. EXACT(DHOKLA,Dhokla): Dies wird False zurückgeben. EXACT(DHOKLA,dhokla): Dies wird False zurückgeben. EXACT(DHOKLA,DhOkLA): Dies wird False zurückgeben.

Beispiel: Nehmen wir an, die Aufgabe besteht darin, nach der Art des Lebensmittels Dhokla zu suchen, jedoch unter Berücksichtigung der Groß- und Kleinschreibung. Dies kann mit der Formel erfolgen:

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Hier gibt die EXACT-Funktion „True“ zurück, wenn der Wert in Spalte B3:B10 mit Dhokla im gleichen Fall übereinstimmt, andernfalls gibt sie „False“ zurück. Jetzt wird die MATCH-Funktion in Spalte B3:B10 angewendet und nach einer Zeile mit dem genauen Wert TRUE gesucht. Danach ruft die INDEX-Funktion den Wert der Spalte C3:C10 (Lebensmitteltyp-Spalte) in der von der MATCH-Funktion zurückgegebenen Zeile ab.

Suche nach mehreren Kriterien

Eines der schwierigsten Probleme in Excel ist die Suche nach mehreren Kriterien. Mit anderen Worten: eine Suche, die in mehr als einer Spalte gleichzeitig übereinstimmt. Im folgenden Beispiel werden die INDEX- und MATCH-Funktionen sowie die boolesche Logik verwendet, um drei Spalten abzugleichen.

1. Essen.
2. Kosten.
3. Menge.

Um die Gesamtkosten zu extrahieren.

Beispiel: Nehmen wir an, die Aufgabe besteht darin, die Gesamtkosten für Pasta zu berechnen

Essen: Pasta. Kosten: 60. Menge: 1.

In diesem Beispiel gibt es also drei Kriterien für die Durchführung eines Matches. Nachfolgend finden Sie die Schritte für die Suche basierend auf mehreren Kriterien:

Schritt 1: Ordnen Sie zunächst die Lebensmittelspalte (B3:B10) mithilfe der Formel „Pasta“ zu:

 'PASTA' = B3:B10>

Dadurch werden die B3:B10-Werte (Nahrungsmittelspalte) als boolesche Werte konvertiert. Das ist wahr, wo Essen Pasta ist, sonst falsch.

Schritt 2: Passen Sie anschließend die Kostenkriterien wie folgt an:

 60 = D3:D10>

Dadurch werden die D3:D10-Werte (Kostenspalte) als boolesche Werte ersetzt. Das ist wahr, wenn Kosten = 60, sonst falsch.

Schritt 3: Der nächste Schritt besteht darin, das dritte Kriterium, das Menge = 1 ist, auf folgende Weise zuzuordnen:

 1 = E3:E10>

Dadurch wird die Spalte E3:E10 (Mengenspalte) als „Wahr“ ersetzt, wobei „Menge = 1“ ist, andernfalls ist sie „Falsch“.

Schritt 4: Multiplizieren Sie das Ergebnis des ersten, zweiten und dritten Kriteriums. Dies ist der Schnittpunkt aller Bedingungen und wandelt den booleschen Wert Wahr/Falsch in 1/0 um.

Schritt 5: Das Ergebnis ist nun eine Spalte mit 0 und 1. Verwenden Sie hier die MATCH-Funktion, um die Zeilennummer der Spalten zu ermitteln, die 1 enthalten. Denn wenn eine Spalte den Wert 1 hat, bedeutet dies, dass sie alle drei Kriterien erfüllt.

Schritt 6: Nachdem Sie die Zeilennummer erhalten haben, verwenden Sie die INDEX-Funktion, um die Gesamtkosten dieser Zeile zu ermitteln.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Hier stellt F3:F10 die Gesamtkostenspalte dar.