Wie führt man einen SVERWEIS durch und gibt mehrere Werte ohne Duplikate in Excel zurück?
Beim Arbeiten mit Daten in Excel müssen Sie gelegentlich mehrere übereinstimmende Werte zu einem bestimmten Suchkriterium zurückgeben. Die Standardfunktion SVERWEIS liefert jedoch lediglich einen einzelnen Wert. Gibt es mehrere Übereinstimmungen und möchten Sie diese ohne Duplikate in einer einzigen Zelle anzeigen, stehen Ihnen alternative Methoden zur Verfügung, um dieses Ziel zu erreichen.
SVERWEIS und Rückgabe mehrerer übereinstimmender Werte ohne Duplikate in Excel
Mehrere übereinstimmende Werte ohne Duplikate mithilfe der Funktionen TEXTVERKETTEN und FILTER zurückgeben
Wenn Sie Excel 365 oder Excel 2021 verwenden, können Sie mit den Funktionen TEXTVERKETTEN und FILTER dies ganz einfach erreichen – sie ermöglichen eine dynamische Filterung Ihrer Daten und fassen die Ergebnisse nahtlos in einer einzigen Zelle zusammen.
Geben Sie die folgende Formel in eine leere Zelle ein, um das Ergebnis anzuzeigen, und drücken Sie anschließend die „Enter“-Taste, um alle übereinstimmenden Werte ohne Duplikate zu erhalten. Siehe Screenshot:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(C2:C17, A2:A17=E2)))
- FILTER(C2:C17; A2:A17=E2) extrahiert alle Namen aus Spalte C, deren Produkt in Spalte A dem Suchwert in Zelle E2 entspricht.
- EINDEUTIG entfernt sämtliche doppelten Werte.
- TEXTVERKETTEN(„„, “"; WAHR; …) verknüpft die resultierenden eindeutigen Werte zu einer einzigen Zelle und trennt sie durch Kommas.
Mehrere übereinstimmende Werte ohne Duplikate mithilfe einer leistungsstarken Funktion zurückgeben
Wenn Sie einen SVERWEIS durchführen und mehrere übereinstimmende Werte ohne Duplikate in Excel zurückgeben möchten – aber manuelle Formeln oder VBA als zu komplex empfinden – liefert „Kutools für Excel“ mit seiner „Ein-zu-viele-Suche“-Funktion eine einfache und effiziente Lösung. Damit extrahieren Sie im Handumdrehen alle eindeutigen Treffer und fassen sie mit nur wenigen Klicks in einer einzigen Zelle zusammen.
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 dort die gewünschten Operationen an:
- Wählen Sie im ersten Textfeld „Platzierungsbereich der Liste“ und im zweiten „Suchwertbereich“ aus;
- Wählen Sie den Tabellenbereich aus, den Sie verwenden möchten;
- Wählen Sie Schlüsselspalte und Rückgabespalte separat aus den Dropdown-Listen „Schlüsselspalte“ bzw. „Rückgabespalte“ aus;
- Klicken Sie zum Abschluss auf die Schaltfläche „OK“.
Ergebnis:
Nun sehen Sie, dass alle übereinstimmenden Werte ohne Duplikate extrahiert wurden. Siehe Screenshot:
Wenn Sie ein anderes Trennzeichen zur Abtrennung der Daten verwenden möchten, klicken Sie auf „Optionen“ und wählen das gewünschte Trennzeichen aus. Zudem können Sie mit den Ergebnissen weitere Operationen durchführen – beispielsweise Summen bilden, Mittelwerte berechnen und vieles mehr.
Mehrere übereinstimmende Werte ohne Duplikate mithilfe einer benutzerdefinierten Funktion zurückgeben
Falls Sie nicht über Excel 365 oder Excel 2021 verfügen, können Sie stattdessen die unten bereitgestellte benutzerdefinierte Funktion nutzen – und so ähnliche Ergebnisse erzielen, wie zum Beispielmehrere übereinstimmende Werte ohne Duplikate zurückgeben – sogar in älteren Excel-Versionen.
- Halten Sie die Tasten „Alt“ + „F11“ gedrückt, um das Fenster „Microsoft Visual Basic für Applikationen“ zu öffnen.
- Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code in das Modulfenster ein.
VBA-Code: SVERWEIS und Rückgabe mehrerer eindeutiger übereinstimmender Werte:
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 - Schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein. Drücken Sie anschließend die „Enter“-Taste, um das gewünschte Ergebnis zu erhalten. Siehe Screenshot:
=VlookupUnique(E2, A2:A17, C2:C17, ", ")
Zusammenfassend gibt es mehrere effektive Möglichkeiten, einen SVERWEIS durchzuführen und mehrere übereinstimmende Werte ohne Duplikate in Excel zurückzugeben. Wählen Sie die Methode, die am besten zu Ihren Anforderungen und Ihrer Excel-Version passt. Mit diesen Techniken gelingt es Ihnen mühelos, mehrere übereinstimmende Werte ohne Duplikate in Excel abzurufen. Wenn Sie weitere Excel-Tipps und -Tricks entdecken möchten,bietet unsere Website Tausende von Anleitungen.
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins