Warum INDEX und MATCH besser sind als SVERWEIS in Excel

Wir haben Einsteigern bereits erklärt, wie sie die Grundfunktionen von SVERWEIS (engl. SVERWEIS, die Abkürzung steht für „Vertical Lookup Function“) nutzen können. Und erfahrenen Benutzern wurden mehrere kompliziertere Formeln gezeigt.

Und in diesem Artikel werden wir versuchen, Informationen über eine andere Methode zum Arbeiten mit der vertikalen Suche zu geben.

Sie fragen sich vielleicht: „Warum ist das notwendig?“. Und das ist notwendig, um alle möglichen Suchmethoden anzuzeigen. Darüber hinaus verhindern zahlreiche SVERWEIS-Einschränkungen häufig, dass das gewünschte Ergebnis erzielt wird. In dieser Hinsicht ist INDEX( ) MATCH( ) viel funktionaler und vielfältiger und hat auch weniger Einschränkungen.

Grundlagen INDEX-MATCH

Da der Zweck dieses Handbuchs darin besteht, zu zeigen, wie gut diese Funktion ist, haben wir Schauen wir uns die grundlegenden Informationen zu den Funktionsprinzipien an. Und wir werden Beispiele zeigen und auch überlegen, warum es besser ist als VLOOKUP ().

Syntax und Verwendung der INDEX-Funktion

Diese Funktion hilft dabei, anhand der Spalten- oder Zeilennummer den gewünschten Wert in den vorgegebenen Suchbereichen zu finden. Syntax:

=INDEX(Array, Zeilennummer, Spaltennummer):

• array – der Bereich, in dem die Suche stattfinden wird;
• Zeilennummer – die Nummer der zu durchsuchenden Zeile im angegebenen Array. Wenn die Zeilennummer unbekannt ist, muss die Spaltennummer angegeben werden;
• Spaltennummer – die Nummer der Spalte, die im angegebenen Array gefunden werden soll. Wenn der Wert unbekannt ist, ist eine Zeilennummer erforderlich.

Ein Beispiel für eine einfache Formel:

=INDEX(A1:S10,2,3)

Die Funktion sucht im Bereich von A1 bis C10. Die Zahlen geben an, aus welcher Zeile (2) und Spalte (3) der gewünschte Wert angezeigt werden soll. Das Ergebnis ist Zelle C2.

Ziemlich einfach, oder? Aber wenn Sie mit echten Dokumenten arbeiten, haben Sie wahrscheinlich keine Informationen über Spaltennummern oder Zellen. Dafür ist die Funktion MATCH() da.

Syntax und Verwendung der MATCH-Funktion

Die Funktion MATCH() sucht nach dem gewünschten Wert und zeigt seine ungefähre Anzahl im angegebenen Suchbereich an.

Die Syntax von searchpos() sieht folgendermaßen aus:

=VERGLEICH(nachzuschlagender Wert, nachzuschlagendes Array, Übereinstimmungstyp)

• Suchwert – die zu findende Nummer oder der gesuchte Text;
• Gesuchtes Array – der Bereich, in dem die Suche stattfinden wird;
• Übereinstimmungstyp – gibt an, ob nach dem genauen Wert oder den ihm am nächsten liegenden Werten gesucht werden soll:
  • 1 (oder kein Wert angegeben) – gibt den größten Wert zurück, der kleiner oder gleich dem angegebenen Wert ist;
  • 0 – zeigt eine genaue Übereinstimmung mit dem gesuchten Wert. In der Kombination INDEX() MATCH() benötigen Sie fast immer eine exakte Übereinstimmung, also schreiben wir 0;
  • -1 – Zeigt den kleinsten Wert an, der größer oder gleich dem in der Formel angegebenen Wert ist. Die Sortierung erfolgt in absteigender Reihenfolge.

Beispielsweise sind im Bereich B1:B3 New York, Paris, London registriert. Die folgende Formel zeigt die Zahl 3, weil London auf der Liste an dritter Stelle steht:

=EXPOSE(London,B1:B3,0)

So arbeiten Sie mit der INDEX MATCH-Funktion 

Sie haben wahrscheinlich bereits begonnen, das Prinzip zu verstehen, nach dem die gemeinsame Arbeit dieser Funktionen aufgebaut ist. Kurz gesagt also INDEX() sucht unter den angegebenen Zeilen und Spalten nach dem gewünschten Wert. Und MATCH() zeigt die Nummern dieser Werte:

=INDEX(Spalte, aus der der Wert zurückgegeben wird, MATCH(zu suchender Wert, zu durchsuchende Spalte, 0))

Haben Sie immer noch Schwierigkeiten zu verstehen, wie es funktioniert? Vielleicht erklärt ein Beispiel besser. Angenommen, Sie haben eine Liste der Hauptstädte der Welt und ihrer Bevölkerung:

Um die Einwohnerzahl einer bestimmten Hauptstadt, beispielsweise der Hauptstadt Japans, herauszufinden, verwenden wir die folgende Formel:

=INDEX(C2:C10, ÜBEREINSTIMMUNG(Japan, A2:A10,0))

Erläuterung:

• Die Funktion MATCH() sucht im Array A2:A10 nach dem Wert „Japan“ und gibt die Zahl 3 zurück, da Japan der dritte Wert in der Liste ist. 
• Diese Zahl geht anZeilennummer” in der INDEX()-Formel und weist die Funktion an, einen Wert aus dieser Zeile zu drucken.

Die obige Formel wird also zur Standardformel INDEX(C2:C10,3). Die Formel sucht von den Zellen C2 bis C10 und gibt Daten aus der dritten Zelle in diesem Bereich zurück, d. h. C4, da der Countdown in der zweiten Zeile beginnt.

Sie möchten den Namen der Stadt nicht in der Formel vorschreiben? Schreiben Sie es dann in eine beliebige Zelle, z. B. F1, und verwenden Sie es als Referenz in der MATCH()-Formel. Und Sie erhalten eine dynamische Suchformel:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Wichtig! Anzahl Zeilen ein Array INDEX() muss gleich der Anzahl der Zeilen sein betrachtetes Array in MATCH(), sonst erhalten Sie das falsche Ergebnis.

Warten Sie eine Minute, warum verwenden Sie nicht einfach die SVERWEIS()-Formel?

=SVERWEIS(F1, A2:C10, 3, Falsch)

 Was bringt es, Zeit zu verschwenden, wenn man versucht, all diese Komplexitäten von INDEX MATCH herauszufinden?

In diesem Fall spielt es keine Rolle, welche Funktion verwendet werden soll. Dies ist nur ein Beispiel, um zu verstehen, wie die Funktionen INDEX() und MATCH() zusammenarbeiten. Andere Beispiele sollen zeigen, wozu diese Funktionen in Situationen fähig sind, in denen SVERWEIS machtlos ist. 

INDEXVERGLEICH oder SVERWEIS

Bei der Entscheidung, welche Suchformel verwendet werden soll, sind sich viele einig, dass INDEX() und MATCH() VLOOKUP weit überlegen sind. Viele Leute verwenden jedoch immer noch SVERWEIS(). Erstens ist VLOOKUP() einfacher und zweitens verstehen Benutzer nicht alle Vorteile der Arbeit mit INDEX() und MATCH(). Ohne dieses Wissen wird niemand bereit sein, seine Zeit mit dem Studium eines komplexen Systems zu verbringen.

Hier sind die wichtigsten Vorteile von INDEX() und MATCH() gegenüber VLOOKUP():

• Suche von rechts nach links. SVERWEIS() kann nicht von rechts nach links suchen, daher müssen die gesuchten Werte immer in den linken Spalten der Tabelle stehen. Aber INDEX() und MATCH() können damit problemlos umgehen. Wie das in der Praxis aussieht, erfahren Sie in diesem Artikel: So finden Sie den gewünschten Wert auf der linken Seite.

1. Sicheres Hinzufügen oder Entfernen von Säulen. Die SVERWEIS()-Formel zeigt falsche Ergebnisse beim Entfernen oder Hinzufügen von Spalten, da SVERWEIS() die genaue Spaltennummer benötigt, um erfolgreich zu sein. Wenn Spalten hinzugefügt oder entfernt werden, ändern sich natürlich auch deren Nummern. 

Und in den Formeln INDEX() und MATCH() wird ein Spaltenbereich angegeben, nicht einzelne Spalten. Daher können Sie Spalten sicher hinzufügen und entfernen, ohne die Formel jedes Mal aktualisieren zu müssen.

1. Keine Begrenzung des Suchvolumens. Bei Verwendung von SVERWEIS() darf die Gesamtzahl der Suchkriterien 255 Zeichen nicht überschreiten, sonst erhalten Sie einen #WERT! Wenn Ihre Daten also eine große Anzahl von Zeichen enthalten, sind INDEX() und MATCH() die beste Option.
2. Hohe Verarbeitungsgeschwindigkeit. Wenn Ihre Tabellen relativ klein sind, werden Sie wahrscheinlich keinen Unterschied bemerken. Wenn die Tabelle jedoch Hunderte oder Tausende von Zeilen enthält und dementsprechend Hunderte und Tausende von Formeln vorhanden sind, werden INDEX () und MATCH () viel schneller zurechtkommen als VLOOKUP (). Tatsache ist, dass Excel nur die in der Formel angegebenen Spalten verarbeitet, anstatt die gesamte Tabelle zu verarbeiten. 

Die Leistungsauswirkung von SVERWEIS() wird besonders deutlich, wenn Ihr Arbeitsblatt eine große Anzahl von Formeln wie SVERWEIS() und SUMME() enthält. Separate Überprüfungen der SVERWEIS()-Funktionen sind erforderlich, um jeden Wert in einem Array zu analysieren. Excel muss also eine Unmenge an Informationen verarbeiten, was die Arbeit erheblich verlangsamt.

Formelbeispiele 

Wir haben bereits die Nützlichkeit dieser Funktionen herausgefunden, sodass wir zum interessantesten Teil übergehen können: der Anwendung des Wissens in der Praxis.

Formel für die Suche von rechts nach links

Wie bereits erwähnt, kann SVERWEIS diese Form der Suche nicht durchführen. Wenn sich also die gewünschten Werte nicht in der Spalte ganz links befinden, liefert SVERWEIS() kein Ergebnis. Die Funktionen INDEX() und MATCH() sind vielseitiger und die Position der Werte spielt keine große Rolle, damit sie funktionieren.

Zum Beispiel werden wir eine Rangspalte auf der linken Seite unserer Tabelle hinzufügen und versuchen herauszufinden, welchen Rang in Bezug auf die Bevölkerung die Hauptstadt unseres Landes einnimmt.

In Zelle G1 schreiben wir den zu findenden Wert und verwenden dann die folgende Formel, um im Bereich C1:C10 zu suchen und den entsprechenden Wert aus A2:A10 zurückzugeben:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Wenn Sie diese Formel für mehrere Zellen verwenden möchten, stellen Sie sicher, dass Sie die Bereiche mit absoluter Adressierung fixieren (z. B. $A$2: $A$10 und $C$2: 4C$10).

INDEX AUSGESETZTER AUSGESETZTER  um in Spalten und Zeilen zu suchen

In den obigen Beispielen haben wir diese Funktionen als Ersatz für SVERWEIS() verwendet, um Werte aus einem vordefinierten Bereich von Zeilen zurückzugeben. Aber was ist, wenn Sie eine Matrix- oder zweiseitige Suche durchführen müssen?

Es klingt kompliziert, aber die Formel für solche Berechnungen ähnelt der Standardformel INDEX() MATCH() mit nur einem Unterschied: die MATCH()-Formel muss zweimal verwendet werden. Das erste Mal, um die Zeilennummer zu erhalten, und das zweite Mal, um die Spaltennummer zu erhalten:

=INDEX(Array, MATCH(vertikaler Suchwert, Suchspalte, 0), MATCH(horizontaler Suchwert, Suchzeile, 0))

Sehen wir uns die folgende Tabelle an und versuchen wir, eine Formel zu erstellen INDEX() EXPRESS() EXPRESS() um demografische Daten in einem bestimmten Land für ein ausgewähltes Jahr anzuzeigen.

Das Zielland befindet sich in Zelle G1 (vertikale Suche) und das Zieljahr in Zelle G2 (horizontale Suche). Die Formel wird wie folgt aussehen:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Wie diese Formel funktioniert

Wie bei allen anderen komplexen Formeln sind sie leichter zu verstehen, wenn man sie in einzelne Gleichungen zerlegt. Und dann können Sie verstehen, was jede einzelne Funktion macht:

• ÜBEREINSTIMMUNG(G1,A2:A11,0) – sucht nach einem Wert (G1) im Bereich A2:A11 und zeigt die Nummer dieses Werts an, in unserem Fall ist es 2;
• SUCHE(G2,B1:D1,0) – sucht nach einem Wert (G2) im Bereich B1:D1. In diesem Fall war das Ergebnis 3.

Die gefundenen Zeilen- und Spaltennummern werden an den entsprechenden Wert in der Formel INDEX() gesendet:

=INDEX(B2:D11,2,3)

Als Ergebnis haben wir einen Wert, der sich in einer Zelle am Schnittpunkt von 2 Zeilen und 3 Spalten im Bereich B2:D11 befindet. Und die Formel zeigt den gewünschten Wert, der in Zelle D3 steht.

Suche nach mehreren Bedingungen mit INDEX und MATCH

Wenn Sie unseren Leitfaden zu SVERWEIS() gelesen haben, haben Sie wahrscheinlich mehrere Suchformeln ausprobiert. Diese Suchmethode hat jedoch eine wesentliche Einschränkung – die Notwendigkeit, eine Hilfsspalte hinzuzufügen.

Aber die gute Nachricht ist das Mit INDEX() und MATCH() können Sie nach mehreren Bedingungen suchen, ohne Ihr Arbeitsblatt bearbeiten oder ändern zu müssen.

Hier ist die allgemeine Suchformel mit mehreren Bedingungen für INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Die Notiz: Diese Formel muss zusammen mit der Tastenkombination verwendet werden STRG+UMSCHALT+EINGABETASTE.

Angenommen, Sie müssen den gesuchten Wert anhand von 2 Bedingungen finden: Käufer и Produkt.

Dazu ist folgende Formel erforderlich:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

In dieser Formel ist C2:C10 der Bereich, in dem die Suche stattfindet, F1 – dieser Zustand, A2:A10 – ist der Bereich zum Vergleichen der Bedingung, F2 – Bedingung 2, V2: V10 – Vergleichsbereich für Bedingung 2.

Vergessen Sie nicht, die Kombination am Ende der Arbeit mit der Formel zu drücken STRG + UMSCHALT + EINGABETASTE – Excel schließt die Formel automatisch mit geschweiften Klammern, wie im Beispiel gezeigt:

Wenn Sie keine Matrixformel für Ihre Arbeit verwenden möchten, dann fügen Sie der Formel einen weiteren INDEX() hinzu und drücken Sie die EINGABETASTE, es sieht so aus wie im Beispiel:

Wie diese Formeln funktionieren

Diese Formel funktioniert genauso wie die Standardformel INDEX() MATCH(). Um nach mehreren Bedingungen zu suchen, erstellen Sie einfach mehrere falsche und wahre Bedingungen, die die richtigen und falschen individuellen Bedingungen darstellen. Und dann gelten diese Bedingungen für alle entsprechenden Elemente des Arrays. Die Formel konvertiert die Argumente False und True in 0 bzw. 1 und gibt ein Array aus, wobei 1 die übereinstimmenden Werte sind, die in der Zeichenfolge gefunden wurden. MATCH() findet den ersten Wert, der mit 1 übereinstimmt, und übergibt ihn an die INDEX()-Formel. Und es wird wiederum den bereits gewünschten Wert in der angegebenen Zeile aus der gewünschten Spalte zurückgeben.

Eine Formel ohne Array hängt von der Fähigkeit von INDEX() ab, sie selbst zu verarbeiten. Der zweite INDEX() in der Formel stimmt mit Falsy (0) überein, sodass das gesamte Array mit diesen Werten an die MATCH()-Formel übergeben wird. 

Dies ist eine ziemlich lange Erklärung der Logik hinter dieser Formel. Für weitere Informationen lesen Sie den Artikel „INDEX MATCH mit mehreren Bedingungen".

AVERAGE, MAX und MIN in INDEX und MATCH

Excel hat seine eigenen speziellen Funktionen zum Finden von Durchschnitten, Maxima und Minima. Aber was ist, wenn Sie Daten aus der Zelle abrufen möchten, die diesen Werten zugeordnet ist? In diesem Fall AVERAGE, MAX und MIN müssen in Verbindung mit INDEX und MATCH verwendet werden.

INDEX-MATCH und MAX

Um den größten Wert in Spalte D zu finden und ihn in Spalte C anzuzeigen, verwenden Sie die Formel: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH und MIN

Verwenden Sie die folgende Formel, um den kleinsten Wert in Spalte D zu finden und ihn in Spalte C anzuzeigen:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SUCHINDEX und SCHLANGE

Um den Durchschnittswert in Spalte D zu finden und diesen Wert in C anzuzeigen:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Abhängig davon, wie Ihre Daten geschrieben werden, ist das dritte Argument von MATCH() entweder 1, 0 oder -1:

• wenn die Spalten in aufsteigender Reihenfolge sortiert sind, setzen Sie 1 (dann berechnet die Formel den Maximalwert, der kleiner oder gleich dem Durchschnittswert ist);
• wenn die Sortierung absteigend ist, dann -1 (die Formel gibt den Mindestwert aus, der größer oder gleich dem Durchschnitt ist);
• Wenn das Lookup-Array einen Wert enthält, der genau dem Durchschnitt entspricht, setzen Sie ihn auf 0. 

 In unserem Beispiel ist die Population in absteigender Reihenfolge sortiert, also setzen wir -1. Und das Ergebnis ist Tokio, da der Bevölkerungswert (13,189) dem Durchschnittswert (000) am nächsten kommt.

SVERWEIS() kann auch solche Berechnungen durchführen, aber nur als Matrixformel: SVERWEIS mit AVERAGE, MIN und MAX.

INDEX MATCH und ESND/IFERROR

Sie haben wahrscheinlich schon bemerkt, dass die Formel einen Fehler ausgibt, wenn sie den gewünschten Wert nicht finden kann #N / A. Sie können die Standardfehlermeldung durch etwas Informativeres ersetzen. Legen Sie beispielsweise das Argument in der Formel fest Im XNUMXth:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Wenn Sie mit dieser Formel Daten eingeben, die nicht in der Tabelle enthalten sind, gibt Ihnen das Formular die angegebene Meldung aus.

Wenn Sie alle Fehler abfangen wollen, dann außer Im XNUMXth kann verwendet werden IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), „Etwas ist schief gelaufen!“)

Denken Sie jedoch daran, dass das Maskieren von Fehlern auf diese Weise keine gute Idee ist, da Standardfehler Verletzungen in der Formel melden.

Wir hoffen, Sie fanden unsere Anleitung zur Verwendung der Funktion INDEX MATCH() hilfreich.