Excel-Tutorial: Alle Treffer bei Duplikaten finden (Lookup on Each Duplicate)

Hier ist ein ausführliches Tutorial, wie du in Excel alle Werte zu einem Suchkriterium ausgibst, auch wenn dieser Wert mehrfach (als Duplikat) vorkommt.

Der normale SVERWEIS hat ein großes Problem: Er gibt immer nur den ersten Treffer zurück, den er in einer Liste findet. Wenn du aber eine Liste von Bestellungen für denselben Kunden oder alle Projektmitarbeiter einer Abteilung auflisten möchtest, benötigst du andere Methoden.

In diesem Tutorial zeige ich dir die drei besten Wege:

1. Die moderne Lösung: Die FILTER-Funktion (Excel 365 & 2021)
2. Die Profi-Lösung: Power Query (für große Datenmengen)
3. Die klassische Lösung: Hilfsspalte & SVERWEIS (für ältere Excel-Versionen)

Methode 1: Die FILTER-Funktion (Empfohlen für Microsoft 365 & Excel 2021)

Dies ist die einfachste und dynamischste Methode. Die Formel gibt automatisch alle passenden Zeilen untereinander aus.

Beispiel-Szenario:

• Spalte A: Projektname (z. B. "Projekt A", "Projekt B", "Projekt A")
• Spalte B: Mitarbeitername
• Ziel: Alle Mitarbeiter für "Projekt A" finden.

Die Formel:

=FILTER(B2:B10; A2:A10 = "Projekt A"; "Kein Treffer gefunden")

So gehst du vor:

1. Klicke in die Zelle, in der die Liste starten soll.
2. Gib =FILTER( ein.
3. Array: Markiere den Bereich, der die Ergebnisse enthält (z. B. die Namen in B2:B10).
4. Include: Markiere den Bereich, in dem gesucht werden soll, und setze die Bedingung (z. B. A2:A10 = "Projekt A" oder verweise auf eine Zelle mit dem Suchbegriff).
5. If_empty (Optional): Gib einen Text ein, falls nichts gefunden wird.
6. Drücke Enter. Excel "verschüttet" die Ergebnisse automatisch nach unten.

Methode 2: Power Query (Die robuste Lösung)

Power Query ist ideal, wenn du sehr große Datensätze hast oder die Daten aus einer externen Quelle stammen.

1. Markiere deine Datentabelle.
2. Gehe zum Reiter Daten -> Aus Tabelle/Bereich. (Excel wandelt den Bereich ggf. in eine "Intelligente Tabelle" um).
3. Im Power Query-Editor klickst du auf den kleinen Pfeil im Spaltenkopf der Spalte, nach der du filtern willst (z. B. "Projekt").
4. Wähle den gewünschten Wert aus (oder nutze Textfilter).
5. Klicke oben links auf Schließen & Laden.
6. Excel erstellt ein neues Tabellenblatt mit genau den gefilterten Duplikaten.

Vorteil: Wenn sich deine Quelldaten ändern, klickst du einfach auf "Daten" -> "Alle aktualisieren".

Methode 3: Der "Trick" für ältere Versionen (Hilfsspalte + SVERWEIS)

Wenn du kein Excel 365 hast, musst du die Duplikate für Excel "einzigartig" machen.

Schritt 1: Die Hilfsspalte erstellen Füge links neben deinen Daten eine Spalte ein. Wir nummerieren das Vorkommen des Wertes. Formel in Zelle A2 (wenn deine Suchbegriffe in B stehen):

=B2 & ZÄHLENWENN($B$2:B2; B2)

Dies macht aus "Projekt A" beim ersten Mal "Projekt A1", beim zweiten Mal "Projekt A2" usw.

Schritt 2: Die Suche abrufen Um nun alle Treffer aufzulisten, suchst du einfach nacheinander nach "Projekt A1", "Projekt A2" etc.

Nutze dazu diese Formel in deiner Ergebnistabelle (angenommen, das Suchwort steht in E1 und du ziehst die Formel nach unten):

=SVERWEIS(E$1 & ZEILE(A1); $A$2:$C$10; 3; FALSCH)

Erklärung: ZEILE(A1) wird beim Runterziehen zu 1, 2, 3... So sucht der SVERWEIS automatisch nach dem 1., 2. und 3. Duplikat.

Zusammenfassung: Welche Methode soll ich wählen?

Pro-Tipp: Wenn du die Ergebnisse der FILTER-Funktion nicht untereinander, sondern nebeneinander in einer einzigen Zelle (mit Komma getrennt) haben möchtest, umschließe sie mit TEXTKETTE oder TEXTVERKETTEN: =TEXTVERKETTEN("; "; WAHR; FILTER(B2:B10; A2:A10="Projekt A"))