Wie sucht man mit SVERWEIS und gibt mehrere Werte ohne Duplikate in Excel zurück?

Beim Arbeiten mit Daten in Excel kann es vorkommen, dass Sie mehrere übereinstimmende Werte für ein bestimmtes Suchkriterium zurückgeben müssen. Die Standardfunktion SVERWEIS liefert jedoch nur einen einzelnen Wert. In Situationen, in denen mehrere Übereinstimmungen existieren und Sie diese in einer einzigen Zelle ohne Duplikate anzeigen möchten, können Sie alternative Methoden verwenden, um dies zu erreichen.

SVERWEIS und mehrere übereinstimmende Werte ohne Duplikate in Excel zurückgeben

• Durch die Verwendung der Funktionen TEXTJOIN und FILTERN
• Durch die Nutzung einer leistungsstarken Funktion
• Durch die Verwendung einer benutzerdefinierten Funktion

Mehrere übereinstimmende Werte ohne Duplikate mit den Funktionen TEXTJOIN und FILTERN zurückgeben

Wenn Sie Excel365 oder Excel2021 verwenden, können Sie die Funktionen TEXTJOIN und FILTERN nutzen, um dies einfach zu erreichen. Diese Funktionen ermöglichen eine dynamische Filterung der Daten und das Zusammenfügen der Ergebnisse in einer einzigen Zelle.

Bitte geben Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis auszugeben, und drücken Sie dann die "Enter"-Taste, um alle übereinstimmenden Werte ohne Duplikate zu erhalten. Siehe Screenshot:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(C2:C17, A2:A17=E2)))

Mehrere übereinstimmende Werte ohne Duplikate mit einer leistungsstarken Funktion zurückgeben

Wenn Sie mit SVERWEIS mehrere übereinstimmende Werte ohne Duplikate in Excel zurückgeben möchten, aber manuelle Formeln oder VBA zu komplex finden, bietet "Kutools für Excel" eine einfache und effiziente Lösung. Mit der Funktion "Ein-zu-viele-Suche" können Sie schnell alle einzigartigen übereinstimmenden Werte in einer einzigen Zelle extrahieren und kombinieren, mit nur wenigen Klicks.

Klicken Sie auf "Kutools" > "Erweiterte SVERWEIS" > "Ein-zu-viele-Suche (gibt mehrere Ergebnisse zurück)" um das Dialogfeld "Ein-zu-viele-Suche" zu öffnen, und geben Sie dann die Operationen im Dialogfeld an:

1. Wählen Sie den "Platzierungsbereich der Liste" und "Bereich der zu durchsuchenden Werte" in den Textfeldern separat aus;
2. Wählen Sie den Tabellenbereich aus, den Sie verwenden möchten;
3. Geben Sie die Schlüsselspalte und die Rückgabespalte aus den Dropdown-Menüs "Schlüsselspalte" und "Rückgabespalte" separat an;
4. Klicken Sie abschließend auf die "OK"-Schaltfläche.
   

Ergebnis:

Jetzt können Sie sehen, dass alle übereinstimmenden Werte ohne doppelte Elemente extrahiert wurden, siehe Screenshot:

Mehrere übereinstimmende Werte ohne Duplikate mit einer benutzerdefinierten Funktion zurückgeben

mehrere übereinstimmende Werte ohne Duplikate zurückzugeben, auch in älteren Versionen von Excel.

1. Halten Sie die "Alt" + "F11" Tasten gedrückt, um das "Microsoft Visual Basic für Applikationen" Fenster zu öffnen.
2. Klicken Sie auf "Einfügen" > "Modul", und fügen Sie den folgenden Code in das "Modul" Fenster ein.

   VBA-Code: SVERWEIS und mehrere einzigartige übereinstimmende Werte zurückgeben:

     Function VlookupUnique(lookupValue As String, lookupRange As Range, resultRange As Range, delim As String) As String
       Dim cell As Range
       Dim result As String
       Dim dict As Object
       Set dict = CreateObject("Scripting.Dictionary")
       For Each cell In lookupRange
           If cell.Value = lookupValue Then
               If Not dict.exists(resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value) Then
                   dict.Add resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value, True
                   result = result & delim & resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value
               End If
           End If
       Next cell 
       If Len(result) > 0 Then
           VlookupUnique = Mid(result, Len(delim) + 1)
       Else
           VlookupUnique = ""
       End If
   End Function

3. Speichern und schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein, drücken Sie die "Enter"-Taste, um das korrekte Ergebnis zu erhalten, das Sie benötigen. Siehe Screenshot:

   =VlookupUnique(E2, A2:A17, C2:C17, ", ")

   
   

Zusammenfassend gibt es mehrere effektive Möglichkeiten, mit SVERWEIS mehrere übereinstimmende Werte ohne Duplikate in Excel zurückzugeben. Wählen Sie die Methode, die am besten zu Ihren Bedürfnissen und Ihrer Excel-Version passt. Mit diesen Techniken können Sie einfach mehrere übereinstimmende Werte ohne Duplikate in Excel zurückgeben. Wenn Sie daran interessiert sind, mehr Excel-Tipps und Tricks zu entdecken, bietet unsere Website Tausende von Tutorials an.