Wie kann ich vlookup verwenden, um mehrere Werte in einer Zelle in Excel zurückzugeben?

Wenn Sie in Excel die VLOOKUP-Funktion verwenden und mehrere Werte für die Kriterien vorhanden sind, können Sie normalerweise nur den ersten abrufen. Aber manchmal möchten Sie alle entsprechenden Werte, die die Kriterien erfüllen, in einer Zelle zurückgeben, wie im folgenden Screenshot gezeigt. Wie können Sie das lösen?

Vlookup zur Rückgabe mehrerer Werte in eine Zelle mit der Funktion TEXTJOIN (Excel 2019 und Office 365)

• Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
• Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben

Vlookup, um mehrere Werte mit einer benutzerdefinierten Funktion in eine Zelle zurückzugeben

• Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben
• Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben

Vlookup, um mehrere Werte mit einer nützlichen Funktion in eine Zelle zurückzugeben

Vlookup zur Rückgabe mehrerer Werte in eine Zelle mit der Funktion TEXTJOIN (Excel 2019 und Office 365)

Wenn Sie die höhere Version von Excel wie Excel 2019 und Office 365 haben, gibt es eine neue Funktion: TEXTVERBINDENMit dieser leistungsstarken Funktion können Sie schnell alle übereinstimmenden Werte in einer Zelle anzeigen und zurückgeben.

  Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben

Bitte wenden Sie die folgende Formel in eine leere Zelle an, in die Sie das Ergebnis einfügen möchten, und drücken Sie dann Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und ziehen Sie dann den Füllpunkt nach unten in die Zelle, in der Sie diese Formel verwenden möchten. Sie erhalten alle entsprechenden Werte wie im folgenden Screenshot gezeigt:

  Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben

Wenn Sie alle übereinstimmenden Werte basierend auf den Suchdaten ohne Duplikate zurückgeben möchten, kann die folgende Formel hilfreich sein.

Bitte kopieren Sie die folgende Formel und fügen Sie sie in eine leere Zelle ein. Drücken Sie dann Strg + Umschalt + Enter Geben Sie die Tasten zusammen, um das erste Ergebnis zu erhalten, und kopieren Sie diese Formel, um andere Zellen zu füllen. Sie erhalten dann alle entsprechenden Werte ohne die doppelten Werte, wie im folgenden Screenshot gezeigt:

Vlookup, um mehrere Werte mit einer benutzerdefinierten Funktion in eine Zelle zurückzugeben

Die obige TEXTJOIN-Funktion ist nur für Excel 2019 und Office 365 verfügbar. Wenn Sie andere niedrigere Excel-Versionen haben, sollten Sie zum Beenden dieser Aufgabe einige Codes verwenden.

  Vlookup, um alle übereinstimmenden Werte in einer Zelle zurückzugeben

1. Halten Sie die Taste gedrückt ALT + F11 Tasten, und es öffnet die Microsoft Visual Basic für Applikationen Fenster.

2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modulfenster.

VBA-Code: Vlookup, um mehrere Werte in eine Zelle zurückzugeben

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 die folgende Formel ein: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in eine bestimmte leere Zelle, in der Sie das Ergebnis platzieren möchten, und ziehen Sie dann den Füllpunkt nach unten, um alle entsprechenden Werte in einer gewünschten Zelle zu erhalten (siehe Abbildung):

  Vlookup, um alle übereinstimmenden Werte ohne Duplikate in einer Zelle zurückzugeben

Um die Duplikate in den zurückgegebenen übereinstimmenden Werten zu ignorieren, verwenden Sie bitte den folgenden Code.

1. Halten Sie die Taste gedrückt Alt + F11 Schlüssel zum Öffnen der Microsoft Visual Basic für Applikationen Fenster.

2. Klicken Sie Insert > Modulund fügen Sie den folgenden Code in das Feld ein Modulfenster.

VBA-Code: Suchen Sie und geben Sie mehrere eindeutige übereinstimmende Werte in einer Zelle zurück

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. Klicken Sie nach dem Einfügen des Codes auf Tools > Bibliographie im geöffneten Microsoft Visual Basic für Applikationen Fenster, und dann in der herausgesprungen Referenzen - VBAProject Dialogfeld überprüfen Microsoft Scripting-Laufzeit Option in der Verfügbare Referenzen Listenfeld, siehe Screenshots:

4. Dann klick OK Um das Dialogfeld zu schließen, speichern und schließen Sie das Codefenster, kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:

Vlookup, um mehrere Werte mit einer nützlichen Funktion in eine Zelle zurückzugeben

 Wenn Sie unsere haben Kutools for ExcelMit seinen Erweiterte Zeilen kombinieren Mit dieser Funktion können Sie die Zeilen basierend auf demselben Wert schnell zusammenführen oder kombinieren und nach Bedarf einige Berechnungen durchführen.

Nach der Installation Kutools for ExcelBitte gehen Sie wie folgt vor:

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

2. Klicken Sie Kutoolen > Zusammenführen & Teilen > Erweiterte Zeilen kombinieren, siehe Screenshot:

3. In der herausgesprungen Erweiterte Zeilen kombinieren Dialogbox:

• Klicken Sie auf den Namen der Schlüsselspalte, auf der basierend 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 Kombinieren ein Trennzeichen zum Trennen der kombinierten Daten auswählen.

4. Dann klick OK Klicken Sie auf die Schaltfläche, und Sie erhalten die folgenden Ergebnisse:

Laden Sie Kutools for Excel jetzt herunter und testen Sie es kostenlos!

Weitere relative Artikel: