Suchen mit mehreren Kriterien mit INDEX-MATCH

Manchmal identifiziert eine einzelne Spalte eine Zeile nicht eindeutig. Vielleicht benötigen Sie den Preis eines Produkts in einer bestimmten Größe oder das Gehalt einer Mitarbeiterin bzw. eines Mitarbeiters in einer bestimmten Abteilung.

Dann ist eine Suche nach mehreren Kriterien erforderlich: Es wird gleichzeitig anhand von zwei oder mehr Spalten gesucht, um genau eine Zeile zu bestimmen.

INDEX-MATCH erledigt dies elegant, indem die Bedingungen zu einer einzigen Suchbedingung kombiniert werden. Zusätzliche Hilfsspalten sind nicht erforderlich.

Das einfachste mentale Modell besteht darin, Ihre Schlüsselspalten zu einer einzigen zusammenzufassen. Fügen Sie eine Hilfsspalte hinzu, die Produkt und Größe miteinander verbindet, und führen Sie anschließend eine gewöhnliche Suche darüber aus.

Eine Hilfszelle könnte beispielsweise =A2&"|"&B2 enthalten und "Shirt|Large" erzeugen. Anschließend führen Sie MATCH für "Shirt|Large" in dieser kombinierten Spalte aus.

Das funktioniert, macht Ihr Tabellenblatt jedoch unübersichtlich. In den nächsten Abschnitten erfahren Sie, wie Sie ganz ohne Hilfsspalte auskommen.

Der grundlegende Trick besteht darin, die beiden Bedingungsprüfungen innerhalb von MATCH miteinander zu multiplizieren.

(A2:A10=G1) erzeugt für das erste Kriterium ein Array aus TRUE/FALSE. (B2:B10=G2) macht dasselbe für das zweite. Durch die Multiplikation (A2:A10=G1)*(B2:B10=G2) entsteht nur dort eine 1, wo beide Bedingungen TRUE sind; an allen anderen Stellen steht 0.

MATCH sucht anschließend nach dem Wert 1, um die Zeile zu finden, die beide Bedingungen erfüllt.

In Tabellenkalkulationen verhält sich TRUE wie 1 und FALSE wie 0. Die Multiplikation zweier solcher Werte bildet ein logisches UND nach:

• 1 mal 1 = 1 (beide Bedingungen erfüllt)
• 1 mal 0 = 0
• 0 mal 1 = 0
• 0 mal 0 = 0

Nur Zeilen, in denen beide Kriterien erfüllt sind, erzeugen also eine 1. Jede andere Zeile wird zu 0. Diese eine 1 markiert die gewünschte Zeile.

Schließen Sie das multiplizierte Array nun in MATCH ein und suchen Sie nach dem exakten Wert 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) gibt die Position der ersten Zeile zurück, in der beide Bedingungen TRUE sind.

Wenn die passende Kombination in der vierten Datenzeile steht, gibt MATCH 4 zurück. Diese Position benötigt INDEX, um das Ergebnis abzurufen.

Übergeben Sie das MATCH-Ergebnis an INDEX für die Spalte, aus der Sie den Wert tatsächlich abrufen möchten, beispielsweise den Preis in C2:C10.

Die vollständige Formel bedeutet: Geben Sie aus C2:C10 den Wert aus der Zeile zurück, in der das Produkt G1 und die Größe G2 entspricht.

Dies ist eine echte Suche nach mehreren Kriterien – ohne Hilfsspalte und ohne Ihre Daten neu anordnen zu müssen.

Diese Formel wertet Arrays mit Bedingungen aus. In modernem Excel und Google Sheets drücken Sie einfach die Eingabetaste, und die Formel funktioniert.

In älteren Excel-Versionen (vor der Einführung dynamischer Arrays) müssen Sie sie mit Strg+Umschalt+Eingabetaste als Matrixformel bestätigen. Dadurch werden geschweifte Klammern hinzugefügt. Wenn das Ergebnis in älterem Excel falsch ist oder einen Fehler anzeigt, fehlt normalerweise genau diese Bestätigung.

Benötigen Sie drei Kriterien? Multiplizieren Sie einfach eine weitere Prüfung hinzu. Angenommen, Sie möchten zusätzlich eine Farbe in Spalte D mit der Eingabe in G3 abgleichen.

Jeder zusätzliche Faktor (range=criterion) schränkt das Ergebnis weiter ein. Nur Zeilen, in denen alle Bedingungen TRUE sind, behalten ein Produkt von 1. Ein einziges FALSE setzt das gesamte Produkt auf 0.

Dieses Muster lässt sich auf beliebig viele benötigte Spalten erweitern.

Daten: A = Produkt, B = Größe, C = Preis. Sie möchten den Preis eines "Shirt" in "Large" ermitteln.

• G1 = "Shirt", G2 = "Large".
• Die Bedingungs-Arrays erzeugen nur in der Zeile mit Shirt+Large eine 1, beispielsweise in Zeile 4.
• MATCH(1, ..., 0) gibt 4 zurück.
• INDEX(C2:C10, 4) gibt den Preis dieser Zeile zurück.

Wenn Sie eine der beiden Eingaben ändern, findet die Formel sofort die passende Zeile erneut.

Behalten Sie Folgendes im Blick:

• Gleich große Bereiche: Jeder Bedingungsbereich und die INDEX-Spalte müssen dieselbe Höhe haben.
• Kein Treffer: Wenn keine Zeile alle Kriterien erfüllt, gibt MATCH #N/A zurück. Schließen Sie die gesamte Formel in IFERROR ein.
• Duplikate: Wenn mehr als eine Zeile übereinstimmt, gibt MATCH nur die erste zurück. Formulieren Sie Ihre Kriterien so spezifisch, dass sie eindeutig sind.

Wenn mehrere Zeilen übereinstimmen können und Sie deren Werte lieber summieren möchten, statt einen einzelnen abzurufen, ist SUMPRODUCT eine übersichtliche Alternative zu INDEX-MATCH als Matrixformel.

Die Funktion multipliziert die Bedingungs-Arrays mit der Wertespalte und addiert die Ergebnisse. Dadurch tragen nur Zeilen, die beide Kriterien erfüllen, zum Ergebnis bei. Eine Bestätigung mit Strg+Umschalt+Eingabetaste ist nicht erforderlich, da SUMPRODUCT Arrays nativ verarbeitet.

Verwenden Sie INDEX-MATCH, um einen einzelnen passenden Wert abzurufen, und SUMPRODUCT, um Werte aus allen Treffern zusammenzufassen.

Für Suchvorgänge mit mehreren Kriterien und INDEX-MATCH:

• Multiplizieren Sie die Bedingungsarrays miteinander: (A=G1)*(B=G2) ergibt nur dort 1, wo alle Bedingungen erfüllt sind (ein logisches UND).
• MATCH(1, ..., 0) ermittelt die Position dieser Zeile.
• INDEX(returnCol, position) gibt den Wert zurück.

Fügen Sie für zusätzliche Bedingungen weitere Faktoren der Form *(range=criterion) hinzu, achten Sie auf gleich hohe Bereiche, bestätigen Sie die Formel in älteren Excel-Versionen mit Strg+Umschalt+Eingabe und sichern Sie sie mit IFERROR ab.