XLOOKUP mit mehreren Kriterien in Excel: Zwei praktische Methoden

Quick Links

• Die XLOOKUP-Syntax und ein Einzelkriteriumsbeispiel

• Beispiel 1: Mehrere Kriterien mit Boolescher Logik

• Beispiel 2: Mehrere Kriterien mit Verkettung

Ein weit verbreitetes Missverständnis über die XLOOKUP-Funktion in Microsoft Excel ist, dass sie nur eine einzige Bedingung akzeptiert. Tatsächlich kannst du aber Werte anhand mehrerer Kriterien suchen. Es gibt zwei verbreitete Methoden, die sich in Komplexität und Flexibilität unterscheiden.

Wenn du die Desktop-App auf PC oder Mac verwendest, brauchst du Excel 2021 oder neuer (inklusive Excel für Microsoft 365), um XLOOKUP zu nutzen. Die Funktion ist außerdem in Excel für das Web sowie in Tablet- und Mobil-Apps verfügbar.

Microsoft 365 Personal

Microsoft 365 enthält Zugriff auf Office-Apps wie Word, Excel und PowerPoint auf bis zu fünf Geräten, 1 TB OneDrive-Speicher und mehr.

$100 at Microsoft

$100 at Amazon

Expand

Collapse

Die XLOOKUP-Syntax und ein Einzelkriteriumsbeispiel

Obwohl die Syntax von XLOOKUP auf den ersten Blick kompliziert wirkt, folgt sie einer logischen Struktur:

=XLOOKUP(*a*,*b*,*c*,*d*,*e*,*f*)

wobei

• a (erforderlich) der Suchwert ist,
• b (erforderlich) das Such-Array ist,
• c (erforderlich) das Rückgabe-Array ist,
• d (optional) der Text ist, der zurückgegeben wird, wenn der Suchwert (a) im Such-Array (b) nicht gefunden wird,
• e (optional) der Vergleichsmodus ist (0 = exakte Übereinstimmung (Standard), -1 = exakte Übereinstimmung oder nächstkleinerer Wert, 1 = exakte Übereinstimmung oder nächstgrößerer Wert, 2 = Platzhalter/Wildcard), und
• f (optional) der Suchmodus ist (1 = von oben nach unten (Standard), -1 = von unten nach oben, 2 = Binärsuche bei aufsteigendem Array, -2 = Binärsuche bei absteigendem Array).

In diesem Beispiel, nachdem du in Zelle E1 über eine Datenüberprüfung (Dropdown) eine ID gewählt hast, liefert die Formel

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"Kein Treffer",0,1)

in Zelle E2 z. B. den Wert 51.

Das liegt daran, dass:

• E1 die Zelle mit dem Suchwert (z. B. 1323) ist,
• T_Scores[ID] Excel sagt, im ID-Feld der Tabelle T_Scores zu suchen,
• T_Scores[Score] Excel sagt, den entsprechenden Wert aus der Score-Spalte zurückzugeben,
• “Kein Treffer” angibt, was zurückgegeben werden soll, wenn der Suchwert nicht gefunden wird,
• 0 Excel anweist, eine exakte Übereinstimmung zu finden, und
• 1 Excel anweist, von oben nach unten zu suchen.

Die letzten beiden Argumente kannst du in diesem Fall weglassen, da exakte Übereinstimmung und die Suche von oben nach unten die Standardwerte sind.

Beispiel 1: Mehrere Kriterien mit Boolescher Logik

Wenn du mehr als ein Kriterium hast, ändert sich die Art, wie du XLOOKUP anwendest. Stellen wir uns eine Tabelle vor: sechs Getränke, verkauft in sechs Ländern, und jeweils ein Manager pro Getränk–Land-Kombination.

Ziel: Gib ein Getränk und ein Land ein; XLOOKUP soll Manager und Verkaufszahl zurückgeben.

XLOOKUP arbeitet normalerweise mit einem einzigen Suchwert. Um mehrere Kriterien zu unterstützen, erzeugen wir mit Boolescher Logik ein temporäres Sucharray.

Die Formel (für Lesbarkeit in mehreren Zeilen):

=XLOOKUP(
1,
(T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
T_Managers[[Manager]:[Sales]],
"Kein Ergebnis"
)

Erklärung des Such-Arrays (Argument b):

(T_Managers[Drink]=G1)*(T_Managers[Country]=G2)

1. T_Managers[Drink]=G1 erzeugt ein temporäres Array mit TRUE/FALSE für jede Zeile.

Beispiel:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}

2. T_Managers[Country]=G2 erzeugt ein weiteres TRUE/FALSE-Array, z. B.:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE...}

3. Durch die Multiplikation (*) werden TRUE zu 1 und FALSE zu 0. Nur dort, wo beide Kriterien TRUE sind, entsteht eine 1:

{0;0;0;0;0;0;0;0;0;0;1;0...}

Wir suchen dann nach der Zahl 1:

=XLOOKUP(1, , T_Managers[[Manager]:[Sales]], "Kein Ergebnis")

Die erste 1 markiert die erste Zeile, in der beide Kriterien erfüllt sind. XLOOKUP gibt für diese Zeile die entsprechenden Werte aus den Rückgabe-Spalten zurück (Manager und Sales).

Hinweise und Varianten:

• Verwende “*” (Multiplikation), um eine UND-Bedingung zu erreichen (alle Kriterien müssen zutreffen).
• Verwende “+” (Addition), um eine ODER-Bedingung zu erreichen (mindestens ein Kriterium muss zutreffen). Dann suchst du ebenfalls nach 1, aber die Arithmetik verändert die gewählten Treffer.
• Vergleichsoperatoren sind möglich, z. B. (T_Managers[Sales]>100) liefert TRUE für Zeilen mit Sales > 100.

Beispiel 2: Mehrere Kriterien mit Verkettung

Eine andere, oft einfachere Methode ist, sämtliche Suchwerte zu verketten (Concatenate) und ebenso die Quellspaltenkandidaten zu verketten. Das funktioniert gut, wenn jede Kombination eindeutig ist.

=XLOOKUP(
G1&G2,
T_Managers[Drink]&T_Managers[Country],
T_Managers[[Manager]:[Sales]],
"Kein Ergebnis"
)

Hier erzeugt G1&G2 z. B. den Suchstring “CoffeeSpain”. Die Spalten T_Managers[Drink]&T_Managers[Country] liefern für jede Zeile einen vergleichbaren String: “Apple juiceAustralia”, “CoffeeSpain” usw. XLOOKUP sucht dann den kombinierten String und gibt die Zeilenwerte zurück.

Vorteile der Verkettung:

• Einfach zu implementieren.
• Gut, wenn Kombinationswerte einzigartig sind.
• Keine Array-Mathematik nötig.

Nachteile:

• Empfindlich gegenüber führenden/nachgestellten Leerzeichen.
• Fehleranfällig bei unterschiedlichen Datentypen (Zahlen vs. Texte) ohne passende Formatierung.

Wann welche Methode wählen?

• Verkettung: wenn jede Kombination genau einmal vorkommt und die Felder sauber sind.
• Boolesche Logik: wenn du komplexere Regeln brauchst (z. B. Mischung aus UND/ODER, Vergleichsoperatoren, Bereichsprüfungen).

Gute Praxis: Datentypen und Bereinigung

Bevor du komplexe XLOOKUP-Formeln baust, prüfe und bereinige die Quelldaten:

• Entferne führende/nachgestellte Leerzeichen mit TRIM().
• Vereinheitliche Groß-/Kleinschreibung mit UPPER()/LOWER(), falls nötig.
• Stelle sicher, dass Zahlen als Zahlen vorliegen (VALUE() konvertiert Text zu Zahl).

Fehlerbehebung: Häufige Probleme und schnelle Tests

• Ergebnis ist #N/A: Stelle sicher, dass der Suchwert tatsächlich existiert. Teste mit MATCH oder FILTER.
• Falsche Zeile wird zurückgegeben: Prüfe die Sortierung oder ob mehrere gleiche Kombinationen existieren.
• Leere Rückgabe: Teste das kombinierte Sucharray separat in einer Hilfsspalte.

Erweiterte Anwendungsfälle und Grenzen

• Mehrere Rückgabespalten: XLOOKUP akzeptiert ein Rückgabe-Array mit mehreren Spalten (z. B. Manager und Sales gleichzeitig).
• Mehrere Übereinstimmungen: XLOOKUP liefert nur die erste Übereinstimmung. Wenn du alle Treffer brauchst, nutze FILTER (erfordert Microsoft 365 / Excel 2021+).
• Performance: Große Tabellen mit vielen berechneten Arrays (Boolesche Logik über hunderttausende Zeilen) können spürbar langsamer werden als strukturierte Hilfsspalten.

Alternative Ansätze

• FILTER: Gibt alle Zeilen zurück, die die Kriterien erfüllen. Gut für mehrere Treffer.
• INDEX/MATCH mit Hilfsspalten: Klassische Alternative, besonders nützlich in älteren Excel-Versionen.
• SUMIFS/COUNTIFS: Wenn du aggregierte Werte (Summen, Zählungen) statt einzelner Zeilen suchst.

Kurze Vergleichstabelle (konzeptionell):

• XLOOKUP Verkettung: schnell, einfach, benötigt eindeutige Kombinationen.
• XLOOKUP Boolesch: flexibel, mächtig, komplexer.
• FILTER: wenn du alle Treffer brauchst.
• INDEX/MATCH: kompatibel und robust in älteren Versionen.

Mini-Cheat Sheet: Formelsnippets

• Verkettung (ein Ergebnis):

=XLOOKUP(G1&G2, T_Managers[Drink]&T_Managers[Country], T_Managers[[Manager]:[Sales]], "Kein Ergebnis")

• Boolesches UND (erstes Ergebnis):

=XLOOKUP(1, (T_Managers[ColA]=G1)*(T_Managers[ColB]=G2), T_Managers[[ColC]:[ColD]], "Kein Ergebnis")

• BOOLsches ODER (erstes Ergebnis):

=XLOOKUP(1, (T_Managers[ColA]=G1)+(T_Managers[ColB]=G2), T_Managers[[ColC]], "Kein Ergebnis")

• FILTER (alle Treffer):

=FILTER(T_Managers, (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), "Keine Treffer")

Mentales Modell: Wie XLOOKUP denkt

Denke an XLOOKUP als an drei Schritte:

1. Suche definieren (Was suche ich? Ein Wert oder eine 1 im kombinierten Array?).
2. Wo suchen? (Such-Array — oft ein temporäres TRUE/FALSE-Array oder ein verketteter String).
3. Was zurückgeben? (Rückgabe-Array, eine oder mehrere Spalten).

Dieses Modell hilft, Fehler systematisch zu finden: Überprüfe jeden Schritt unabhängig.

Rollenbasierte Checkliste

• Analyst: Prüfe Datentypen, leere Zellen und Duplikate; verwende TRIM/UPPER.
• Entwickler: Verwende strukturierte Tabellen (Excel-Tabellen) und vermeide volatile Funktionen.
• Manager: Definiere, ob nur der erste Treffer genügt oder alle Treffer nötig sind.
• QA: Testfälle mit Randwerten, fehlenden Werten und ähnlichen Texten anlegen.

Factbox: Wichtige Hinweise auf einen Blick

• Erforderliche Excel-Version: Excel 2021 oder neuer (inkl. Microsoft 365) für XLOOKUP.
• Alternativen in älteren Versionen: INDEX/MATCH.
• Verhalten: XLOOKUP gibt nur die erste passende Zeile zurück; FILTER liefert alle.
• Performance-Tipp: Verwende Hilfsspalten, wenn große Datenmengen wiederholt geprüft werden.

Edge-Case-Galerie (typische Fallstricke)

• Unterschiedliche Datentypen: “100” (Text) vs. 100 (Zahl) führen zu fehlenden Treffern.
• Leerzeichen: “Coffee “ ≠ “Coffee”. Lösung: TRIM().
• Mehrdeutige Kombinationen: Verkettung funktioniert nur bei eindeutigen kombinierten Schlüsseln.
• Leistungsprobleme: Komplexe Array-Berechnungen in großen Tabellen können Excel verlangsamen.

Mini-Methodologie: Vorgehen zum sicheren Einsatz

1. Definiere das Ziel: Einzelner Treffer oder alle Treffer?
2. Bereinige Daten: TRIM, UPPER/LOWER, VALUE.
3. Entscheidungsbaum: Sind Kombinationen eindeutig? Wenn ja, Verkettung; wenn nein, Boolesch oder FILTER.
4. Implementiere Formel in einer kleinen Testtabelle.
5. Skaliere schrittweise und mache Performance-Checks.

Mermaid-Diagramm (Entscheidungsbaum):

flowchart TD
  A[Start: Was brauchen Sie?] --> B{Erste Übereinstimmung genügt?}
  B -- Ja --> C{Kombination eindeutig?}
  B -- Nein --> D[FILTER verwenden]
  C -- Ja --> E[Verkettung mit XLOOKUP]
  C -- Nein --> F[Boolesche Logik mit XLOOKUP]
  E --> G[Implementieren + testen]
  F --> G
  D --> G

Tests und Akzeptanzkriterien

• Kriterium 1: Eingabe einer gültigen Kombination liefert korrekte Manager- und Saleswerte.
• Kriterium 2: Fehlende Kombination liefert definierte Fehlermeldung (“Kein Ergebnis”).
• Kriterium 3: Performance akzeptabel bei Produktionsdatenvolumen (kein spürbares Verzögern beim Öffnen oder Neuberechnen).

Kurze Fehler-Runbook / Rollback

• Fehler: Formel liefert #VALUE oder #N/A.
  • Schritt 1: Prüfe Datentypen mit ISTZAHL()/ISTTEXT().
  • Schritt 2: Teste Zusammensetzung der Such-Arrays in Hilfsspalten und visualisiere TRUE/FALSE.
  • Schritt 3: Ersetze komplexe Arrays temporär durch Hilfsspalten mit gespeicherten Werten, um Performance zu prüfen.

Zusammenfassung

XLOOKUP kann sehr wohl mehrere Kriterien verarbeiten. Verkettung ist simpel und schnell für eindeutige Schlüssel. Boolesche Logik bietet maximale Flexibilität und erweitert XLOOKUP um Vergleiche und gemischte Regeln. Für mehrere Treffer nutze FILTER. Achte immer auf saubere Datentypen, entferne Leerzeichen und teste die Performance vor dem Rollout.

Wichtige Handlungsempfehlung: Beginne mit einem kleinen Beispiel-Datensatz, bevor du komplexe Array-Formeln auf große Tabellen anwendest.

Wichtige Hinweise: Die gezeigten Beispiele funktionieren in Excel 2021 und Microsoft 365; FILTER und dynamische Arrays benötigen ebenfalls diese oder neuere Versionen.