XLOOKUP: So gibst du alle Treffer aus (Vertikal oder in einer Zelle)

Hier ist ein ausführliches Tutorial, das dir zeigt, wie du die Einschränkung des XLOOKUPs (XVERWEIS) umgehst, um alle passenden Ergebnisse anzuzeigen.

Der XLOOKUP (XVERWEIS) ist ein mächtiges Werkzeug, hat aber eine entscheidende Eigenschaft: Er gibt standardmäßig immer nur den ersten Treffer zurück, den er findet.

Wenn du jedoch eine Liste hast, in der ein Suchbegriff mehrfach vorkommt (z. B. alle Bestellungen eines bestimmten Kunden), brauchst du eine andere Lösung. In diesem Tutorial lernst du zwei Wege:

1. Vertikal: Alle Treffer untereinander auflisten.
2. In einer Zelle: Alle Treffer kombiniert in einer einzigen Zelle anzeigen.

Das Problem: Warum XLOOKUP allein nicht reicht

Angenommen, du hast eine Tabelle mit Verkäufern und ihren Verkäufen:

• Müller: 500 €
• Schmidt: 300 €
• Müller: 150 €

Ein =XVERWEIS("Müller"; ...) würde dir nur die 500 € liefern. Die 150 € werden ignoriert.

Lösung 1: Alle Treffer vertikal ausgeben (Die FILTER-Funktion)

Um alle Ergebnisse untereinander aufzulisten, ist die Funktion FILTER die moderne und richtige Wahl in Excel 365 oder Excel 2021+.

Die Formel:

=FILTER(Rückgabebereich; Suchspalte = Suchkriterium; "Kein Treffer gefunden")

Schritt-für-Schritt-Beispiel:

• Spalte A: Namen (Suchspalte)
• Spalte B: Umsätze (Rückgabebereich)
• Zelle D1: Der Name, den du suchst (z. B. "Müller")

Gib in Zelle E1 ein: =FILTER(B2:B100; A2:A100 = D1)

Ergebnis: Excel "spillt" (läuft über) und zeigt automatisch alle Beträge von Müller untereinander an.

Lösung 2: Alle Treffer in einer Zelle (TEXTVERKETTEN + FILTER)

Manchmal möchtest du nicht, dass die Liste mehrere Zeilen einnimmt, sondern alle Ergebnisse (z. B. durch Komma getrennt) in einer einzigen Zelle stehen. Hier kombinieren wir den FILTER mit TEXTVERKETTEN.

Die Formel:

=TEXTVERKETTEN(", "; WAHR; FILTER(Rückgabebereich; Suchspalte = Suchkriterium))

Schritt-für-Schritt-Beispiel:

• Wir suchen wieder alle Umsätze von "Müller".
• Ziel: In einer Zelle soll stehen: "500, 150".

Gib folgende Formel ein: =TEXTVERKETTEN("; "; WAHR; FILTER(B2:B100; A2:A100 = D1))

Erklärung der Bausteine:

1. "; ": Das Trennzeichen zwischen den Werten.
2. WAHR: Ignoriert leere Zellen (falls vorhanden).
3. FILTER(...): Liefert die Liste der Werte, die TEXTVERKETTEN dann zusammenschnürt.

Bonus: Nur eindeutige Treffer anzeigen

Falls ein Wert doppelt vorkommt (z. B. Müller hat zweimal genau 500 € verkauft) und du jeden Wert nur einmal in deiner Liste sehen möchtest, setze ein EINDEUTIG (UNIQUE) davor:

=EINDEUTIG(FILTER(B2:B100; A2:A100 = D1))

Zusammenfassung: Wann verwende ich was?

Tipp: Wenn du mit älteren Excel-Versionen arbeitest (vor 2021), funktionieren FILTER und XVERWEIS nicht. In diesem Fall müsstest du auf komplexe Matrixformeln mit INDEX und KLEINSTE ausweichen – ein Upgrade auf eine aktuelle Version ist für diese Anwendungsfälle jedoch dringend empfohlen!