Hoe vlookup om meerdere waarden in één cel in Excel te 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.

Vlookup om meerdere waarden in één cel te retourneren met de TEXTJOIN-functie (Excel 2019 en Office 365)

• Vlookup om alle overeenkomende waarden in één cel te retourneren
• Vlookup om alle overeenkomende waarden zonder duplicaten in één cel te retourneren

Vlookup om meerdere waarden in één cel te retourneren met een handige functie

Vlookup om meerdere waarden in één cel te retourneren met door de gebruiker gedefinieerde functie

• Vlookup om alle overeenkomende waarden in één cel te retourneren
• Vlookup om alle overeenkomende waarden zonder duplicaten in één cel te retourneren

Vlookup om meerdere waarden in één cel te retourneren met de TEXTJOIN-functie (Excel 2019 en Office 365)

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

  Vlookup om alle overeenkomende waarden in één cel te retourneren

Pas de onderstaande formule toe in een lege cel waar u het resultaat wilt plaatsen en druk vervolgens op Ctrl + Shift + Enter toetsen samen om het eerste resultaat te krijgen en sleep vervolgens de vulgreep naar de cel waarin u deze formule wilt gebruiken, en u krijgt alle bijbehorende waarden zoals hieronder afgebeeld:

  Vlookup om alle overeenkomende waarden zonder duplicaten in één cel te 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 op Ctrl + Shift + Enter sleutels samen om het eerste resultaat te krijgen, en kopieer vervolgens deze formule om andere cellen te vullen, en je krijgt alle corresponderende waarden zonder de dulpicatie zoals onderstaand screenshot getoond:

Vlookup om meerdere waarden in één cel te retourneren met een handige functie

met Kutools for Excel's Geavanceerd Combineer rijen functie, kunt u eenvoudig meerdere overeenkomende waarden in één cel ophalen, zonder dat er complexe formules nodig zijn! 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 for Excel maakt het allemaal mogelijk!

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. klikken Kutools > Samenvoegen en splitsen > Geavanceerd Combineer rijen, zie screenshot:

3. In de pop-out Geavanceerd Combineer rijen dialoog venster:

• Klik op de naam van de sleutelkolom die u wilt combineren op basis van, en klik vervolgens op Hoofdsleutel.
• Klik vervolgens op een andere kolom waarvan u de gegevens wilt combineren op basis van de sleutelkolom en klik op de vervolgkeuzelijst in de Werking veld, kies één scheidingsteken om de gecombineerde gegevens van de Combineren pagina.
• Dan klikken OK knop.

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 eenvoudigweg het selectievakje aan Dubbele waarden verwijderen optie in het dialoogvenster. Dit zorgt ervoor dat alleen unieke items worden gecombineerd in één cel, waardoor uw gegevens schoner en beter georganiseerd worden zonder extra moeite. Zie screenshots:

Download en gratis proef Kutools voor Excel nu!

Vlookup om meerdere waarden in één cel te retourneren met 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.

  Vlookup om alle overeenkomende waarden in één cel te retourneren

1. Houd de toets ingedrukt ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.

2. klikken Invoegen > Moduleen 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:

  Vlookup om alle overeenkomende waarden zonder duplicaten in één cel te retourneren

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

1. Houd de toets ingedrukt Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. klikken Invoegen > Moduleen 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 ingevoerd, klikt u op Tools > Referenties in het geopende Microsoft Visual Basic for Applications-venster en vervolgens in het uitgevouwen venster Referenties - VBAProject dialoogvenster, vink aan Microsoft Scripting-runtime optie in het Beschikbare referenties keuzelijst, zie screenshots:

4. Dan klikken OK om het dialoogvenster te sluiten, slaat u het codevenster op en sluit u het, keert u terug naar het werkblad en voert u deze formule in: =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:

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: