Indexübereinstimmung mit mehreren Kriterien

Indexübereinstimmung mit mehreren Kriterienzeilen und -spalten

Wir alle verwenden VLOOKUP Tag für Tag, um die Daten abzurufen, und wir sind uns auch der Tatsache bewusst, dass VLOOKUP die Daten von links nach rechts abrufen kann. Daher sollte der Suchwert immer auf der linken Seite der Ergebnisspalten stehen. Wir haben jedoch mehrere Alternativen, die als Alternative zur VLOOKUP-Funktion in Excel verwendet werden können. Mit fortschrittlicher Technologie können wir diese INDEX + MATCH-Formel verwenden, um mehrere Kriterien für Zeilen und Spalten zu erfüllen. In diesem speziellen Artikel werden Sie ausführlich über diese Technik informiert.

Wie verwende ich die INDEX + MATCH-Formel, um mehrere Kriterien zu erfüllen?

Hier erklären wir, wie Sie mit der Index + Match-Formel mehrere Kriterien für Zeilen und Spalten mit Beispielen abgleichen können.

Sie können diese Excel-Vorlage für Indexübereinstimmungen mit mehreren Kriterien hier herunterladen - Excel-Vorlage für Indexübereinstimmungen mit mehreren Kriterien

Beispiel 1 - INDEX + MATCH-Formel

Nicht die Mehrheit der Excel-Benutzer sucht nach Funktionen außerhalb von VLOOKUP. Es kann so viele Gründe geben. Wie auch immer, lassen Sie uns eine einfache Einführung in diese Formel geben, bevor wir zum fortgeschrittenen Level übergehen.

Schauen Sie sich zum Beispiel die folgende Datenstruktur in Excel an.

Wir haben "Sales Rep" -Namen und ihre jeweiligen Verkaufswerte. Auf der anderen Seite haben wir eine Dropdown-Liste von "Sale Rep" in Zelle D2.

Basierend auf der Auswahl, die wir aus der Dropdown-Liste treffen, muss der Verkaufsbetrag in Zelle E2 angezeigt werden.

Das Problem ist, dass wir die VLOOKUP-Formel nicht anwenden können, da sich der Suchwert "Sales Rep" rechts von der Ergebnisspalte "Sales" befindet. In diesen Fällen können wir also die kombinierte Suchwertformel INDEX + MATCH verwenden.

INDEX sucht nach dem genannten Zeilennummernwert im Bereich A2: A11 und in diesem Bereich müssen wir angeben, aus welcher Zeile der Verkaufswert stammen soll. Dieser Zeilenwert basiert auf dem Namen "Vertriebsmitarbeiter", der in der Dropdown-Liste in Excel ausgewählt wurde. Die MATCH-Funktion sucht daher nach der Zeilennummer "Vertriebsmitarbeiter" im Bereich B2: B11 und gibt die Zeilennummer des übereinstimmenden Werts zurück .

Beispiel 2 - Mehrere Kriterien in der INDEX + MATCH-Formel

Jetzt haben wir eine Datenstruktur wie die folgende.

Wir haben monatliche Verkaufswerte von "Sales Rep". Aus dieser Tabelle benötigen wir dynamische Ergebnisse wie in Zelle A15 habe ich eine Dropdown-Liste "Vertriebsmitarbeiter" erstellt und in Zelle B14 habe ich eine Dropdown-Liste "Monat" erstellt.

Basierend auf der Auswahl in diesen beiden Zellen muss unsere Formel die Daten aus der obigen Tabelle abrufen.

Wenn ich beispielsweise "Rep 8" und "Apr" wähle, muss der Verkaufswert von "Rep 8" für den Monat "Apr" angezeigt werden.

In diesen Fällen müssen wir also sowohl Zeilen als auch Spalten abgleichen. Führen Sie die folgenden Schritte aus, um die Formel so anzuwenden, dass sie sowohl Zeilen als auch Spalten entspricht.

Schritt 1: Öffnen Sie die INDEX-Funktion in Zelle B15.

Schritt 2: Das erste Argument der INDEX-Funktion ist "Array", dh aus welchem ​​Zellbereich wir das Ergebnis benötigen. In diesem Fall benötigen wir Verkaufswerte. Wählen Sie daher den Zellbereich von B2 bis G11.

Schritt 3: Nächstes Argument der INDEX-Funktion, aus welcher Zeile des ausgewählten Bereichs wir das Ergebnis benötigen. In diesem Fall müssen wir die Zeilennummer „Vertriebsmitarbeiter“ basierend auf der Auswahl in der Dropdown-Zelle Zelle A15 ermitteln. Um also die Zeilennummer basierend auf der Auswahl dynamisch abzurufen, öffnen Sie die MATCH-Funktion.

Schritt 4: LOOKUP VALUE der MATCH-Funktion lautet "Sales Rep". Wählen Sie daher die Zelle A15 als Referenz.

Schritt 5: Das Lookup-Array enthält den Namensbereich "Vertriebsmitarbeiter" in der Haupttabelle. Wählen Sie also den Bereich A2 bis A11.

Schritt 6: Der Übereinstimmungstyp der MATCH-Funktion ist genau. Geben Sie daher Null als Argumentwert ein.

Schritt 7: Das nächste Argument der INDEX-Funktion ist "Spaltennummer", dh aus dem ausgewählten Zellbereich, aus welcher Spalte wir das Ergebnis benötigen. Dies hängt von dem Monat ab, den wir aus der Dropdown-Liste der Zelle B14 auswählen. Um die Spaltennummer automatisch zu erhalten, öffnen Sie eine andere MATCH-Funktion.

Schritt 8: Dieser Nachschlagewert ist der Monatsname. Wählen Sie daher die B14-Zelle als Referenz aus.

Schritt 9: Das Lookup-Array ist der Monatsbereich der Zellen in der Haupttabelle, dh von B1 bis G1.

Schritt 10: Das letzte Argument ist der Übereinstimmungstyp. Wählen Sie als Kriterium „Genaue Übereinstimmung“. Schließen Sie zwei Klammern und drücken Sie die Eingabetaste, um das Ergebnis zu erhalten.

Wie wir oben sehen können, haben wir "Rep 6" und "Apr" als Monat gewählt und unsere Formel hat den Verkaufswert für den Monat "Apr" für "Rep 6" zurückgegeben.

Hinweis: Die gelb gefärbte Zelle ist die Referenz für Sie.

Dinge, an die man sich erinnern sollte

  • Eine Kombination aus INDEX + MATCH kann leistungsfähiger sein als die VLOOKUP-Formel.
  • INDEX & MATCH können sowohl Zeilen- als auch Spaltenüberschriften abgleichen und das Ergebnis aus der mittleren Tabelle zurückgeben.
  • MATCH kann die Zeilennummer und Spaltennummer der Tabellenüberschriften von Zeilen und Spalten zurückgeben.