Direkt zum Inhalt

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 mehrere Werte mit einer benutzerdefinierten Funktion in eine 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:

=TEXTJOIN(",",TRUE,IF($A$2:$A$11=E2,$C$2:$C$11,""))

Hinweis: In der obigen Formel A2: A11 Ist der Suchbereich enthält die Suchdaten, E2 ist der Suchwert, C2: C11 ist der Datenbereich, aus dem Sie die übereinstimmenden Werte zurückgeben möchten. ","ist das Trennzeichen zum Trennen der mehreren Datensätze.

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:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$11, IF(E2=$A$2:$A$11, $C$2:$C$11, ""), 0),"")=MATCH(ROW($C$2:$C$11), ROW($C$2:$C$11)), $C$2:$C$11, ""))

Hinweis: In der obigen Formel A2: A11 Ist der Suchbereich enthält die Suchdaten, E2 ist der Suchwert, C2: C11 ist der Datenbereich, aus dem Sie die übereinstimmenden Werte zurückgeben möchten. ","ist das Trennzeichen zum Trennen der mehreren Datensätze.

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):

Hinweis: In der obigen Formel A2: A11 Ist der Suchbereich enthält die Suchdaten, E2 ist der Suchwert, C2: C11 ist der Datenbereich, aus dem Sie die übereinstimmenden Werte zurückgeben möchten. ","ist das Trennzeichen zum Trennen der mehreren Datensätze.

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:

Hinweis: In der obigen Formel A2: C11 ist der Datenbereich, den Sie verwenden möchten, E2 ist der Suchwert, die Zahl 3 ist die Spaltennummer, die die zurückgegebenen Werte enthält.

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.

Hinweis:Um dies anzuwenden Erweiterte Zeilen kombinierenZunächst sollten Sie die herunterladen Kutools for Excelund wenden Sie die Funktion dann schnell und einfach an.

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:

  • VLOOKUP-Funktion mit einigen grundlegenden und erweiterten Beispielen
  • In Excel ist die VLOOKUP-Funktion für die meisten Excel-Benutzer eine leistungsstarke Funktion, mit der nach einem Wert ganz links im Datenbereich gesucht und ein übereinstimmender Wert in derselben Zeile aus einer von Ihnen angegebenen Spalte zurückgegeben wird. In diesem Tutorial wird die Verwendung der VLOOKUP-Funktion anhand einiger grundlegender und erweiterter Beispiele in Excel erläutert.
  • Gibt mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurück
  • Normalerweise ist es für die meisten von uns einfach, einen bestimmten Wert zu suchen und den passenden Artikel zurückzugeben, indem sie die VLOOKUP-Funktion verwenden. Haben Sie jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückzugeben? In diesem Artikel werde ich einige Formeln zur Lösung dieser komplexen Aufgabe in Excel vorstellen.
  • Mehrere Werte vertikal anzeigen und zurückgeben
  • Normalerweise können Sie die Vlookup-Funktion verwenden, um den ersten entsprechenden Wert abzurufen. Manchmal möchten Sie jedoch alle übereinstimmenden Datensätze basierend auf einem bestimmten Kriterium zurückgeben. In diesem Artikel werde ich darüber sprechen, wie alle übereinstimmenden Werte vertikal, horizontal oder in einer einzelnen Zelle angezeigt und zurückgegeben werden.
  • Anzeigen und Zurückgeben mehrerer Werte aus der Dropdown-Liste
  • Wie können Sie in Excel mehrere entsprechende Werte aus einer Dropdown-Liste anzeigen und zurückgeben? Wenn Sie also ein Element aus der Dropdown-Liste auswählen, werden alle relativen Werte gleichzeitig angezeigt. In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.

Beste Office-Produktivitätstools

Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate   |  Leere Zeilen löschen   |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren   |   Runde ohne Formel ...
Super-Lookup: VLookup mit mehreren Kriterien    VLookup mit mehreren Werten  |   VLookup über mehrere Blätter hinweg   |   Unscharfe Suche ....
Erweiterte Dropdown-Liste: Erstellen Sie schnell eine Dropdown-Liste   |  Abhängige Dropdown-Liste   |  Mehrfachauswahl Dropdown-Liste ....
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  |  Vergleichen Sie Bereiche und Spalten ...
Ausgewählte Funktionen: Rasterfokus   |  Designansicht   |   Große Formelleiste    Arbeitsmappen- und Blattmanager   |  Ressourcen (Autotext)   |  Datumsauswahl   |  Arbeitsblätter kombinieren   |  Zellen verschlüsseln/entschlüsseln    Senden Sie E-Mails nach Liste   |  Superfilter   |   Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen, ...)   |   50+ Chart Typen (Gantt-Diagramm, ...)   |   40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag, ...)   |   19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen, ...)   |   12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion, ...)   |   7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Zellen teilen, ...)   |   ... und mehr

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, um die Produktivität zu steigern und Zeit zu sparen.  Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen ...

kte tab 201905


Office Tab Bringt die Oberfläche mit Registerkarten in Office und erleichtert Ihnen die Arbeit erheblich

  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
Comments (43)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I have created a problem.
"I" have combined a "Textjoin" end "Vlookup" to return multiple values in to one single cell.
My problem is that the formula have to have an exact value to look for and I want it to lookup an "almost" match or Partial match.

Example: I have made a schedule how we ate going to work and a D1 is working from 07:30-16:00. And to lookup D1 is not the problem, the problem is that my boss sometimes puts other stuff togeather with the D1... Like "D1 +" or "D1 meeting".
Since my formula only lookup "D1" it misses for example the "D1 +".

My formula (that I have gotten from the web) =TEXTJOIN(" och ";SANT;OM($B$3:$B$15=$C$22:$F$22;$A$3:$A$15;""))It´s in swedish so "SANT" is "TRUE" and "OM" is "IF".

How can I make the formula lookup all the cells that have some form of "D1" in it and return all those to the same cell?
No matter if it says "D1 +" or "D1 meeting" or whatever.
The reson I want this, is because the boss always leave "D1" but can add other text behind the "D1"... and just because of that, my boss messes up my formula.
This comment was minimized by the moderator on the site
Hi!
This is a great VBA-Code which could help me a lot.But when I start the Function MultipleLookupNoRept Excel crashs...I´ve got a Dataset with about 6.000 Rows (Excel 2013).... is this too much for the VBA Function?

Thanks!
This comment was minimized by the moderator on the site
Hello Mr.XXL,Sorry to hear that. The row limit for Excel 2013 is 1048576. Therefore, maybe the VBA code is the reason for the crash.
Anyway, I would love to offer you another VBA code for Vlookup To Return All Matching Values Without Duplicates Into One Cell. Please use the VBA code below:
Option Explicit

Function Lookup_concat(Search_string As String, _
Search_in_col As Range, Return_val_col As Range)

Dim i As Long
Dim temp() As Variant
Dim result As String
ReDim temp(0)

For i = 1 To Search_in_col.Count
If Search_in_col.Cells(i, 1) = Search_string Then
temp(UBound(temp)) = Return_val_col.Cells(i, 1).Value
ReDim Preserve temp(UBound(temp) + 1)
End If
Next

If temp(0) <> "" Then
ReDim Preserve temp(UBound(temp) - 1)
Unique temp
For i = LBound(temp) To UBound(temp)
result = result & " " & temp(i)
Next i
Lookup_concat = Trim(result)
Else
Lookup_concat = ""
End If

End Function

Function Unique(tempArray As Variant)

Dim coll As New Collection
Dim Value As Variant

On Error Resume Next
For Each Value In tempArray
If Len(Value) > 0 Then coll.Add Value, CStr(Value)
Next Value
On Error GoTo 0

ReDim tempArray(0)

For Each Value In coll
tempArray(UBound(tempArray)) = Value
ReDim Preserve tempArray(UBound(tempArray) + 1)
Next Value

End Function

After you insert this VBA code in the Module, please type the formula =Lookup_concat(E2,$A$2:$A$14,$C$2:$C$14) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values. Please see the file I uploaded in this comment. Hope it solves your problem. 
Sincerely,Mandy

This comment was minimized by the moderator on the site
Hi, Thanks so much this worked!I used it to pull dates, that populated in the serial number format (<span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">Changing the format to short date format using =TEXT(A2,”mm/dd/yy”) OR =DATEVALUE(A2) are not working. Do you have any solutions?</span>
This comment was minimized by the moderator on the site
Thank you for the explanations, however the function 'MultipleLookupNoRept' does not work on my file, could you tell me if an error exists.
This comment was minimized by the moderator on the site
Hi, Hasnae,Please check if you miss the third step -  check Microsoft Scripting Runtime option in the Available References list box.

This comment was minimized by the moderator on the site
Thank you so much for the code. Is there a way I can use the code to look up multiple values from multiple sheets? I tried to combine your function with IFERROR function but it doesn't seem to work.
This comment was minimized by the moderator on the site
Can this be modified to place the sum of those values? Instead of (400 400 400 400 400 400), can it sum them to show (2400)?
This comment was minimized by the moderator on the site
How with HLookUp function?
This comment was minimized by the moderator on the site
thanks for the code. I have modified it to allow you to optionally specify your own separator, Default is " ", if you specify the separator as"#cr" it will insert a CR/LF so the values will be on a separate line in the cell. It only applies the separator if there are multiple values

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long, Optional ByVal pSep As Variant)

' ### Returns multiple values from a table into 1 cell ###

' pValue is the key value to lookup

' WorkRng is the Table you want to look up

' pIndex is the column # for the values to be returned from the pWorkRng

' pSep (optional) is the separator to be used. if omitted then default is a space (it doesn't apply the separator for the 1st entry)

' if the separtor = "#cr" it will separate the values on different line in the cell

Dim rng As Range

Dim sSep As String

Dim xResult As String

Dim Item1 As Boolean

Item1 = True



If IsMissing(pSep) = True Then

sSep = vbCr

Else

If pSep = "#cr" Then

sSep = vbCrLf

Else

sSep = pSep

End If

End If



xResult = ""

For Each rng In pWorkRng

If rng = pValue Then

If Item1 Then

xResult = xResult & rng.Offset(0, pIndex - 1)

Item1 = False

Else

xResult = xResult & sSep & rng.Offset(0, pIndex - 1)

End If

End If

Next

MYVLOOKUP = xResult

End Function
This comment was minimized by the moderator on the site
Thank you for this, the line breaks are what i needed to top this formula off! Question, is there a way to modify the code so that two values are compared? For example, similar to what we see with index and match, can i look for Product and Quantity columns, and based on those parameters it outputs results from the Region Column?
This comment was minimized by the moderator on the site
Thanks a lot for this code, it is very helpful. Does anyone know away to sum the values in the cell to just have at total of them.
Cheers
This comment was minimized by the moderator on the site
Hello, James, to sum values based on the corresponding items, the following article may help you, please chek it:
https://www.extendoffice.com/documents/excel/1268-excel-combine-duplicate-rows-and-sum.html
This comment was minimized by the moderator on the site
I have a server, it has connected with multiple applications. I want to compare compare two column and get the related applications details for that server.

What is the command for that.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations