Wie kann man mit SVERWEIS mehrere Werte in einer einzigen Zelle in Excel zurückgeben?

SVERWEIS ist eine leistungsstarke Funktion in Excel, aber standardmäßig gibt sie nur den ersten übereinstimmenden Wert zurück. Was tun, wenn Sie alle übereinstimmenden Werte abrufen und in einer Zelle zusammenfassen möchten? Dies ist eine häufige Anforderung bei der Analyse von Datensätzen oder der Zusammenfassung von Informationen. In diesem Leitfaden führen wir Sie Schritt für Schritt durch Methoden, um mehrere Werte in eine einzelne Zelle sowohl mit Formeln als auch mit hilfreichen Funktionen zurückzugeben.

Mehrere Werte in eine Zelle mit der TEXTVERBINDEN-Funktion zurückgeben (Excel 2019 und Office 365)

• Alle übereinstimmenden Werte in eine Zelle zurückgeben
• Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben

Mehrere Werte in eine Zelle mit Kutools zurückgeben

Mehrere Werte in eine Zelle mit einer benutzerdefinierten Funktion zurückgeben

• Alle übereinstimmenden Werte in eine Zelle zurückgeben
• Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben

Mehrere Werte in eine Zelle mit der TEXTVERBINDEN-Funktion zurückgeben (Excel 2019 und Office 365)

Wenn Sie eine höhere Version von Excel wie Excel 2019 oder Office 365 haben, gibt es eine neue Funktion - TEXTVERBINDEN. Mit dieser leistungsstarken Funktion können Sie schnell einen SVERWEIS durchführen und alle übereinstimmenden Werte in einer Zelle zurückgeben.

  Alle übereinstimmenden Werte in eine Zelle zurückgeben

Bitte wenden Sie die folgende Formel in einer leeren Zelle an, wo Sie das Ergebnis platzieren möchten, dann drücken Sie gleichzeitig die Tasten Strg + Umschalt + Eingabe, um das erste Ergebnis zu erhalten, und ziehen Sie den Ausfüllkästchen nach unten in die Zelle, in der Sie diese Formel verwenden möchten, und Sie erhalten alle entsprechenden Werte, wie im folgenden Screenshot gezeigt:

  Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben

Wenn Sie alle übereinstimmenden Werte basierend auf den Suchdaten ohne Duplikate zurückgeben möchten, könnte Ihnen die folgende Formel helfen.

Bitte kopieren und fügen Sie die folgende Formel in eine leere Zelle ein, dann drücken Sie gleichzeitig die Tasten Strg + Umschalt + Eingabe, um das erste Ergebnis zu erhalten, und kopieren Sie diese Formel, um andere Zellen zu füllen, und Sie erhalten alle entsprechenden Werte ohne doppelte Einträge, wie im folgenden Screenshot gezeigt:

Mehrere Werte in eine Zelle mit Kutools zurückgeben

Mit der Funktion „Erweiterte Zeilen zusammenführen“ von Kutools für Excel können Sie ganz einfach mehrere übereinstimmende Werte in eine einzige Zelle abrufen – komplexe Formeln sind nicht erforderlich! Verabschieden Sie sich von manuellen Lösungen und entdecken Sie einen effizienteren Weg, um Ihre Suchaufgaben in Excel zu bewältigen. Lassen Sie uns sehen, wie Kutools für Excel dies möglich macht!

Nach der Installation von Kutools für Excel gehen Sie bitte wie folgt vor:

1. Wählen Sie den Datenbereich aus, den Sie basierend auf einer anderen Spalte kombinieren möchten.

2. Klicken Sie auf „Kutools“ > „Zusammenführen & Aufteilen“ > „Erweiterte Zeilen zusammenführen“, siehe Screenshot:

3. Im erscheinenden Dialogfeld „Erweiterte Zeilen zusammenführen“:

• Klicken Sie auf den Namen der Schlüsselspalte, die kombiniert werden soll, und klicken Sie dann auf „Primärschlüssel“.
• Klicken Sie dann auf eine andere Spalte, deren Daten Sie basierend auf der Schlüsselspalte kombinieren möchten, und klicken Sie auf die Dropdown-Liste im Feld „Berechnung“, wählen Sie ein Trennzeichen zum Trennen der kombinierten Daten im Abschnitt „Konsolidieren“ aus.
• Klicken Sie dann auf die Schaltfläche OK.

Alle entsprechenden Werte aus einer anderen Spalte, basierend auf demselben Wert, werden in einer einzigen Zelle kombiniert. Siehe Screenshots:

Tipps: Wenn Sie beim Zusammenführen von Zellen doppelte Inhalte entfernen möchten, aktivieren Sie einfach die Option „Doppelte Werte löschen“ im Dialogfeld. Dadurch wird sichergestellt, dass nur eindeutige Einträge in einer einzigen Zelle kombiniert werden, was Ihre Daten sauberer und besser organisiert macht, ohne zusätzlichen Aufwand. Siehe Screenshots:

Jetzt Kutools für Excel herunterladen und kostenlos testen!

Mehrere Werte in eine Zelle mit einer benutzerdefinierten Funktion zurückgeben

Die oben genannte TEXTVERBINDEN-Funktion ist nur für Excel 2019 und Office 365 verfügbar. Wenn Sie andere niedrigere Excel-Versionen haben, sollten Sie einige Codes verwenden, um diese Aufgabe abzuschließen.

  Alle übereinstimmenden Werte in eine Zelle zurückgeben

1. Halten Sie die Tasten „ALT + F11“ gedrückt, und es öffnet sich das Fenster „Microsoft Visual Basic for Applications“.

2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code im Modulfenster ein.

VBA-Code: SVERWEIS zur Rückgabe mehrerer Werte in eine Zelle

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. Speichern und schließen Sie diesen Code, kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =VERKETTENWENN($A$2:$A$11; E2; $C$2:$C$11; ", ") in eine bestimmte leere Zelle, wo Sie das Ergebnis platzieren möchten, dann ziehen Sie den Ausfüllkästchen nach unten, um alle entsprechenden Werte in einer Zelle zu erhalten, die Sie möchten, siehe Screenshot:

  Alle übereinstimmenden Werte ohne Duplikate in eine Zelle zurückgeben

Um Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren, gehen Sie bitte wie folgt vor.

1. Halten Sie die Tasten „Alt + F11“ gedrückt, um das Fenster „Microsoft Visual Basic for Applications“ zu öffnen.

2. Klicken Sie auf „Einfügen“ > „Modul“ und fügen Sie den folgenden Code im Modulfenster ein.

VBA-Code: SVERWEIS und Rückgabe mehrerer eindeutiger übereinstimmender Werte in eine Zelle

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Nachdem Sie den Code eingefügt haben, klicken Sie auf „Extras“ > „Verweise“ im geöffneten Fenster „Microsoft Visual Basic for Applications“, und dann, im erscheinenden Dialogfeld „Verweise – VBAProject“, aktivieren Sie die Option „Microsoft Scripting Runtime“ in der Liste der verfügbaren Verweise, siehe Screenshots:

4. Klicken Sie dann auf OK, um das Dialogfeld zu schließen, speichern und schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =MEHRFACHSUCHEOHNEWIEDERHOLUNG(E2;$A$2:$C$11;3) in eine leere Zelle, wo Sie das Ergebnis ausgeben möchten, und ziehen Sie den Ausfüllkästchen nach unten, um alle übereinstimmenden Werte zu erhalten, siehe Screenshot:

Ob Sie sich für Formeln wie TEXTVERBINDEN in Kombination mit Array-Funktionen entscheiden, Tools wie Kutools für Excel oder benutzerdefinierte Funktionen nutzen – alle Ansätze helfen dabei, komplexe Suchaufgaben zu vereinfachen. Wählen Sie die Methode, die am besten zu Ihren Bedürfnissen passt. Wenn Sie daran interessiert sind, weitere Excel-Tipps und -Tricks zu erkunden, bietet unsere Website Tausende von Tutorials.

Weitere relevante Artikel: