So nutzt du XLOOKUP() mit mehreren Kriterien in Excel

XLOOKUP() ist in Excel die neueste Funktion aus der Gruppe der Suchfunktionen (LOOKUP(), VLOOKUP(), HLOOKUP() und XLOOKUP()) und bietet viele Vorteile, erweiterte Funktionen und mehr Flexibilität.

In diesem Tutorial schauen wir uns zuerst an, wofür die Excel-Funktion XLOOKUP() gedacht ist und warum sie älteren Suchfunktionen überlegen ist. Danach gehen wir die Grundsyntax durch und kommen dann zum Kern: dem Einsatz von XLOOKUP() mit mehreren Kriterien.

Wenn du die Excel-Grundlagen lernen möchtest, ist der einsteigerfreundliche Kurs Introduction to Excel der richtige Startpunkt.

Warum XLOOKUP() in Excel verwenden

Die Funktion XLOOKUP() durchsucht einen Bereich oder ein Array und gibt das Element zum ersten Treffer zurück. Wenn kein exakter Treffer gefunden wird, kann XLOOKUP() bei entsprechendem Abgleichstyp auch einen ungefähren Treffer liefern. In vielerlei Hinsicht übertrifft XLOOKUP() seine Vorgänger in Excel (VLOOKUP(), HLOOKUP() und LOOKUP()).

Konkret ermöglicht sie:

• Suche nach Daten sowohl horizontal als auch vertikal und in jede Richtung
• Suche mit mehreren Kriterien
• Ungefähren Abgleich, standardmäßig jedoch exakter Abgleich
• Teilweise Übereinstimmung
• Rückgabe mehrerer Spalten und Zeilen
• Rückgabe eines individuellen Textes, wenn kein Treffer gefunden wird.

Außerdem ist XLOOKUP() schneller als die älteren Suchfunktionen in Excel – ein Vorteil besonders bei großen Datenmengen.

XLOOKUP() mit einer Bedingung verwenden

Hier ist kurz die Grundsyntax der Funktion XLOOKUP():

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Die Pflichtparameter sind:

• lookup_value—der zu suchende Wert

• lookup_array—das Array bzw. der Bereich, in dem gesucht wird

• return_array—das Array bzw. der Bereich, aus dem der Wert zurückgegeben wird

Und die optionalen Parameter sind:

• [if_not_found]—der Text, der zurückgegeben wird, wenn kein Treffer gefunden wird

• [match_mode]—der Abgleichstyp (exakt oder ungefähr) und was zurückgegeben wird, wenn kein Treffer gefunden wird.

• [search_mode]—der Suchmodus (vorwärts oder rückwärts, binäre Suche auf auf- oder absteigend sortiertem Sucharray).

Beispiele für die optionalen Parameter sehen wir später in diesem Tutorial.

Wenn du weitere nützliche Excel-Funktionen und -Formeln auffrischen willst, schau dir das Tutorial The 15 Basic Excel Formulas Everyone Needs to Know und das Excel Formulas Cheat Sheet an.

XLOOKUP() mit mehreren Kriterien verwenden

Aus der Grundsyntax wird klar: Die Excel-Funktion XLOOKUP() ist standardmäßig für einen einzelnen Suchwert einer angegebenen Variable ausgelegt.

Wir können die Funktion jedoch so anpassen, dass sie mehrere Variablen gleichzeitig berücksichtigt und für jede Variable einen anderen Suchwert verwendet. Kurz: Wir können mit XLOOKUP() über mehrere Kriterien suchen. Dafür gibt es zwei Hauptansätze: Verkettung und boolesche Ausdrücke. Beide schauen wir uns gleich an.

Bevor wir in die Details gehen, werfen wir einen Blick auf die Excel-Tabelle, mit der wir arbeiten. Sie enthält Informationen zu 10 Katzen: Name, Farbe und Alter:

In unseren Beispielen nutzen wir XLOOKUP(), um eines der drei Merkmale einer Katze anhand der jeweils anderen beiden zu finden.

Dass unsere Tabelle sehr einfach ist und wir alles auch ohne Funktion finden könnten, ist hier in Ordnung. Es geht darum, die Grundprinzipien von XLOOKUP() mit mehreren Kriterien zu verstehen. Mit diesem Verständnis kannst du das Vorgehen leicht auf komplexere Praxisfälle übertragen, z. B. mit mehr als zwei Kriterien oder speziellen Anforderungen.

XLOOKUP() mit mehreren Kriterien per Verkettung

Der Verkettungsansatz ist recht unkompliziert: Wir verketten die zugehörigen Suchwerte und Sucharrays miteinander. So funktioniert es.

Angenommen, wir wollen das Alter einer weiß-grauen Katze namens Nala finden. Zur Übersicht haben wir die beiden Kriterien auf demselben Blatt ergänzt, auf dem auch unsere Tabelle steht:

Die XLOOKUP()-Formel lautet dann:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Sie gibt 2,5 zurück – das Alter der ersten weiß-grauen Nala (meine Katze 😺). Denk daran: XLOOKUP() liefert das Element zum ersten Treffer.

Um zu verstehen, was die ersten beiden Komponenten der Formel – die mit dem kaufmännischen Und – bedeuten, können wir sie separat in Zellen ausführen:

=B2:B11&C2:C11

Das Ergebnis ist NalaWhite und Grey.

=B2:B11&C2:C11

Das Ergebnis ist:

So einfach ist es: Wir haben die jeweiligen Suchwerte und Sucharrays miteinander verknüpft – gemäß der grundlegenden XLOOKUP()-Syntax von oben.

XLOOKUP() mit mehreren Kriterien per booleschen Ausdrücken

Jetzt lösen wir dieselbe Aufgabe – das Alter einer weiß-grauen Katze namens Nala finden – mit dem zweiten Ansatz: boolesche Ausdrücke. Die Formel lautet dann:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Wie erwartet liefert sie ebenfalls 2,5 – das Alter der ersten weiß-grauen Nala.

Schauen wir uns die Bestandteile an. Die 1 steht für TRUE, d. h., wir suchen im Sucharray – dem zweiten Teil der Formel (B2:B11=G2)*(C2:C11=G3) – nach dem Wert TRUE.

Dieser Teil enthält zwei Faktoren, die jeweils eine Bedingung prüfen: Der erste prüft, ob der Name dem gesuchten Namen entspricht, der zweite, ob die Farbe der gesuchten Farbe entspricht. Führen wir jeden Faktor getrennt aus:

=B2:B11=G2

Das Ergebnis ist:

=C2:C11=G3

Das Ergebnis ist:

Jetzt den gesamten zweiten Bestandteil ausführen:

=(B2:B11=G2)*(C2:C11=G3)

Das Ergebnis ist:

Obwohl wir dort zwei Einsen sehen (also zwei TRUE-Werte), gibt XLOOKUP() den Wert zum ersten Treffer zurück.

Der Ansatz mit booleschen Ausdrücken wirkt bei mehreren Kriterien deutlich komplexer und weniger intuitiv als die Verkettung – besonders bei mehr als zwei Kriterien. Warum also nicht immer verketten?

Der Ansatz mit booleschen Ausdrücken ist deutlich flexibler, wenn wir XLOOKUP() mit mehreren Kriterien einsetzen. Werfen wir einen Blick auf die Vorteile gegenüber der Verkettung.

Auf den Wert FALSE prüfen

In der obigen Formel prüfen wir, ob der boolesche Ausdruck TRUE ist, indem wir 1 übergeben:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

In manchen Fällen möchten wir aber auf FALSE prüfen. Zum Beispiel wollen wir das Alter der ersten Katze finden, die nicht eine weiß-graue Nala ist. Dann ändern wir die Formel so:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Das Ergebnis ist 1 – das Alter der ersten Katze, die keine weiß-graue Nala ist (genauer: eine dreifarbige Nala, aber nicht weiß-grau).

Logische Operatoren verwenden

Mit booleschen Ausdrücken sind wir nicht auf Gleichheit beschränkt. Sagen wir, wir wollen die Farbe einer Katze namens Nala finden, die jünger als 2 Jahre ist:

Die XLOOKUP()-Formel lautet dann:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Das Ergebnis ist Tricolor.

Mindestens ein Kriterium erfüllen

Bisher haben wir geprüft, ob alle Kriterien erfüllt sind. In manchen Fällen reicht es, mindestens eines zu erfüllen.

Zurück zu unserer Ausgangsaufgabe – das Alter einer weiß-grauen Katze namens Nala zu finden:

Diesmal wollen wir jedoch das Alter einer Katze finden, die entweder Nala heißt oder weiß-grau ist. Die Formel lautet:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Sie gibt 1 zurück – das Alter einer dreifarbigen Nala.

Zur Erinnerung: Als wir das Alter einer Katze gesucht haben, die sowohl Nala heißt als auch weiß-grau ist, lautete die Formel:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Der Unterschied ist das + statt *.

Wenn du das volle Potenzial von Excel ausschöpfen willst, sieh dir den umfassenden, praxisnahen Lernpfad Excel Fundamentals an.

Optionale Parameter für XLOOKUP() mit mehreren Kriterien

Wie bei der Verwendung von XLOOKUP() mit einem einzelnen Kriterium können wir auch bei mehreren Kriterien die Zusatzoptionen nutzen. Hier kommen die optionalen Parameter [if_not_found], [match_mode] und [search_mode] ins Spiel.

Werfen wir einen kurzen Blick auf Beispiele für jeden dieser Parameter in Kombination mit XLOOKUP() und mehreren Kriterien. Der Einfachheit halber verwenden wir jeweils die Verkettungsmethode.

Einen Text zurückgeben, wenn kein Treffer gefunden wird

Hier suchen wir das Alter einer schwarzen Nala – eine Katze, die in unserer Tabelle nicht existiert:

In diesem Fall ergänzen wir in der XLOOKUP()-Formel den optionalen Parameter [if_not_found] und geben den Text an, der zurückgegeben werden soll, wenn kein Treffer gefunden wird:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Tatsächlich gibt die Funktion den angegebenen Text zurück: No cat is found. Ohne Text würde #N/A erscheinen.

Einen ungefähren Treffer zurückgeben

Angenommen, wir suchen das Alter einer dreifarbigen Nala, sind uns aber nicht sicher, ob die Farbe in der Tabelle als "Tricolor" oder "Tricolour" geschrieben ist. Dann brauchen wir einen ungefähren, wildcardbasierten Abgleich und übergeben der Funktion XLOOKUP() das Argument [match_mode] mit dem Wert 2.

In der Tabelle verwenden wir ein Sternchen (*), das für eine beliebige Anzahl von Zeichen steht, auch 0. Weitere Platzhalter findest du in der Microsoft Office-Dokumentation.

Die XLOOKUP()-Formel lautet hier:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Sie liefert 1 – das Alter der ersten (und einzigen) dreifarbigen Nala in unserer Tabelle.

Wichtig: Das Argument 2 bedeutet, dass wir einen ungefähren Abgleich möchten – wir suchen also nicht wörtlich nach einer Katze namens Nala mit der Farbe "Tricolo*r". In unserem Fall hat das Sternchen "Tricolor" abgedeckt, würde aber ebenso "Tricolour" oder etwa "Tricolooor" erfassen.

Rückwärtssuche durchführen

Wir wiederholen die Übung vom Anfang – das Alter einer weiß-grauen Nala finden – starten die Suche diesmal aber vom letzten Eintrag.

Hierfür setzen wir in XLOOKUP() den optionalen Parameter [search_mode] auf -1:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

Die Funktion gibt 7 zurück – das Alter der ersten weiß-grauen Nala vom Ende des Sucharrays aus gesehen.

XLOOKUP() vs. INDEX() und MATCH()

In älteren Excel-Versionen mussten wir zur Nachbildung einer ähnlichen Funktionalität wie bei XLOOKUP() mit mehreren Kriterien die Funktionen INDEX() und MATCH() kombinieren. Ohne tief in die Syntax einzutauchen, vergleichen wir, wie wir das Alter der ersten weiß-grauen Nala mit der alten Methode INDEX() + MATCH() und der neuen Funktion XLOOKUP() finden (Zellbezüge siehe vorige Tabelle):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Auch wenn die Argumente ähnlich wirken, hier die wichtigsten Vorteile von XLOOKUP() gegenüber der alten Methode:

• Eine einzige Funktion genügt – kein Kombinieren mehrerer Funktionen nötig.

• Möglichkeit, wenn passend, den Verkettungsansatz zu verwenden.

• Nutzung der optionalen Parameter (die Funktion MATCH() bietet diese nicht).