Hoe kan ik met Vlookup meerdere waarden in één cel in Excel retourneren?

VLOOKUP is een krachtige functie in Excel, maar standaard retourneert het alleen de eerste overeenkomende waarde. Wat als u alle overeenkomende waarden wilt ophalen en ze in één cel wilt combineren? Dit is een veelvoorkomende vereiste bij het analyseren van datasets of het samenvatten van informatie. In deze handleiding leiden we u stapsgewijs door methoden om meerdere waarden in één cel te retourneren met behulp van zowel formules als handige functies.

Meerdere waarden in één cel weergeven met de functie TEKST.COMBINEREN (Excel 2019 en Office 365)

• Alle overeenkomende waarden in één cel terugzetten
• Alle overeenkomende waarden zonder duplicaten in één cel retourneren

Meerdere waarden in één cel terugzetten met Kutools

Retourneer meerdere waarden in één cel met een door de gebruiker gedefinieerde functie

• Alle overeenkomende waarden in één cel terugzetten
• Alle overeenkomende waarden zonder duplicaten in één cel retourneren

Meerdere waarden in één cel weergeven met de functie TEKST.COMBINEREN (Excel 2019 en Office 365)

Als je de hogere versie van Excel hebt, zoals Excel 2019 en Office 365, is er een nieuwe functie - TEXTJOINMet deze krachtige functie kunt u snel alle overeenkomende waarden opvouwen en in één cel retourneren.

  Alle overeenkomende waarden in één cel terugzetten

Pas de onderstaande formule toe op een lege cel waarin u het resultaat wilt plaatsen en druk vervolgens tegelijkertijd op de toetsen Ctrl + Shift + Enter om het eerste resultaat te krijgen. Sleep vervolgens de vulgreep naar beneden naar de cel waarin u de formule wilt gebruiken. U krijgt dan alle bijbehorende waarden zoals weergegeven in onderstaande schermafbeelding:

  Alle overeenkomende waarden zonder duplicaten in één cel retourneren

Als u alle overeenkomende waarden wilt retourneren op basis van de opzoekgegevens zonder duplicaten, kan de onderstaande formule u helpen.

Kopieer en plak de volgende formule in een lege cel en druk vervolgens tegelijkertijd op de toetsen Ctrl + Shift + Enter om het eerste resultaat te krijgen. Kopieer vervolgens deze formule om de andere cellen in te vullen. U krijgt dan alle overeenkomstige waarden zonder de dubbele, zoals in onderstaande schermafbeelding:

Meerdere waarden in één cel terugzetten met Kutools

Met de functie "Geavanceerd rijen combineren" van Kutools voor Excel kunt u eenvoudig meerdere overeenkomende waarden in één cel ophalen - geen ingewikkelde formules nodig! Zeg vaarwel tegen handmatige oplossingen en ontgrendel een efficiëntere manier om uw opzoektaken in Excel af te handelen. Laten we eens kijken hoe Kutools voor Excel dit allemaal mogelijk maakt!

Ga als volgt te werk na het installeren van Kutools voor Excel:

1. Selecteer het gegevensbereik waarvan u de gegevens uit één kolom wilt combineren op basis van een andere kolom.

2. Klik op "Kutools" > "Samenvoegen en splitsen" > "Geavanceerd rijen combineren", zie screenshot:

3. In het geopende dialoogvenster 'Geavanceerd rijen combineren':

• Klik op de naam van de sleutelkolom waarop u de sleutel wilt combineren en klik vervolgens op 'Primaire sleutel'.
• Klik vervolgens op een andere kolom waarvan u de gegevens wilt combineren op basis van de sleutelkolom en klik op de vervolgkeuzelijst in het veld 'Bewerking'. Kies een scheidingsteken om de gecombineerde gegevens uit de sectie 'Combineren' te scheiden.
• Klik vervolgens op de knop OK.

Alle overeenkomstige waarden uit een andere kolom, gebaseerd op dezelfde waarde, worden gecombineerd in één cel. Zie screenshots:

Tips: Als u dubbele inhoud wilt verwijderen tijdens het samenvoegen van cellen, vinkt u eenvoudig de optie 'Dubbele waarden verwijderen' in het dialoogvenster aan. Dit zorgt ervoor dat alleen unieke waarden in één cel worden gecombineerd, waardoor uw gegevens overzichtelijker en beter georganiseerd zijn zonder extra moeite. Zie screenshots:

Download en gratis proef Kutools voor Excel nu!

Retourneer meerdere waarden in één cel met een door de gebruiker gedefinieerde functie

De bovenstaande TEXTJOIN-functie is alleen beschikbaar voor Excel 2019 en Office 365, als u andere lagere Excel-versies heeft, moet u enkele codes gebruiken om deze taak te voltooien.

  Alle overeenkomende waarden in één cel terugzetten

1. Houd de toetsen "ALT + F11" ingedrukt; het venster "Microsoft Visual Basic for Applications" wordt geopend.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Vlookup om meerdere waarden in één cel te retourneren

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. Sla deze code op en sluit deze, ga terug naar het werkblad en voer deze formule in: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in een specifieke lege cel waar u het resultaat wilt plaatsen en sleep vervolgens de vulgreep naar beneden om alle overeenkomstige waarden in één cel te krijgen die u wilt, zie screenshot:

  Alle overeenkomende waarden zonder duplicaten in één cel retourneren

Gebruik de onderstaande code om de duplicaten in de geretourneerde overeenkomende waarden te negeren.

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op 'Invoegen' > 'Module' en plak de volgende code in het modulevenster.

VBA-code: Vlookup en retourneer meerdere unieke overeenkomende waarden in één cel

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. Nadat u de code hebt ingevoegd, klikt u in het geopende venster 'Microsoft Visual Basic for Applications' op 'Extra' > 'Verwijzingen' en vinkt u in het geopende dialoogvenster 'Verwijzingen – VBAProject' de optie 'Microsoft Scripting Runtime' aan in de keuzelijst 'Beschikbare verwijzingen'. Zie de schermafbeeldingen:

4. Klik vervolgens op OK om het dialoogvenster te sluiten, sla de code op en sluit deze, ga terug naar het werkblad en voer deze formule in: =MultipleLookupNoRept(E2,$A$2:$C$11,3) in een lege cel waar u het resultaat wilt weergeven en sleep vervolgens de vulgreep naar beneden om alle overeenkomende waarden te krijgen, zie schermafbeelding:

Of u nu kiest voor formules zoals TEXTJOIN gecombineerd met arrayfuncties, tools zoals Kutools voor Excel of User Defined Function, alle benaderingen helpen complexe opzoektaken te vereenvoudigen. Kies de methode die het beste bij uw behoeften past. Als u geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, onze website biedt duizenden tutorials.

Meer relatieve artikelen: