Hoe retourneer je meerdere opzoekwaarden gescheiden door komma's in één cel?

Bij het werken met gegevens in Excel kun je een situatie tegenkomen waarin je meerdere overeenkomende waarden moet retourneren voor een zoekopdracht en deze wilt weergeven in één cel, gescheiden door komma's, streepjes, enz. Hoewel de ingebouwde opzoekfuncties van Excel zoals "VLOOKUP" en "INDEX-MATCH" uitstekend zijn voor het vinden van één overeenkomst, vereist het combineren van meerdere resultaten in één cel een meer geavanceerde aanpak. Dit artikel introduceert enkele nuttige manieren om deze taak in Excel op te lossen.

Retourneer meerdere opzoekwaarden in een cel gescheiden door komma's

• Met behulp van TEXTJOIN en FILTER (Excel 365 of Excel 2021 en later)
• Met behulp van Kutools voor Excel (alle Excel-versies)
• Met behulp van een door de gebruiker gedefinieerde functie

Retourneer meerdere opzoekwaarden in een cel gescheiden door komma's met TEXTJOIN en FILTER-functies

Als je toegang hebt tot Excel 365 of Excel 2021 en latere versies, is het gebruik van de dynamische matrixformule de eenvoudigste methode.

Pas de onderstaande formule toe in een lege cel om het resultaat weer te geven, en druk vervolgens op de "Enter"-toets om het resultaat te krijgen.

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))

Sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

Retourneer meerdere opzoekwaarden in een cel gescheiden door komma's met Kutools voor Excel

"Kutools voor Excel" is een invoegtoepassing ontworpen om complexe taken in Excel te vereenvoudigen. De geavanceerde functie "Eén-op-veel-zoeken" stelt je in staat om moeiteloos alle overeenkomende waarden in één cel te extraheren met een specifiek scheidingsteken - geen ingewikkelde formules of VBA-codering nodig.

Nadat je Kutools voor Excel hebt geïnstalleerd, doe dan het volgende:

Klik op "Kutools" > "Super ZOEKEN" > "Eén-op-veel-zoeken (retourneert meerdere resultaten)" om het dialoogvenster te openen. Specificeer in het dialoogvenster de bewerkingen als volgt:

1. Selecteer het "Plaatsingsgebied lijst en Bereik van waarden dat moet worden doorzocht" apart in de tekstvakken;
2. Selecteer het tabelbereik dat je wilt gebruiken;
3. Specificeer de sleutelkolom en retourkolom afzonderlijk in de vervolgkeuzelijsten "Sleutelkolom" en "Retourkolom";
4. Klik ten slotte op de knop "OK".
   

Resultaat:

Alle corresponderende cellen met dezelfde waarde zijn samengevoegd in één cel, gescheiden door een komma, zie screenshot:

Tips: Als je een ander scheidingsteken wilt gebruiken om de gegevens te scheiden, kun je op "Opties" klikken en je gewenste scheidingsteken selecteren. Daarnaast kun je andere bewerkingen uitvoeren op de resultaten, zoals optellen, gemiddelde berekenen, en meer.

Retourneer meerdere opzoekwaarden in een cel gescheiden door komma's met een door de gebruiker gedefinieerde functie

Als je geen Excel 365 of Excel 2021 hebt, zal deze sectie je stap voor stap begeleiden bij het maken en gebruiken van een UDF om meerdere opzoekwaarden in één cel te retourneren, gescheiden door komma's.

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: Retourneer meerdere opzoekwaarden in één cel gescheiden door komma's

Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
    'Updateby Extendoffice
    Dim result As String
    Dim i As Long
    result = ""
    For i = 1 To lookupRange.Rows.Count
        If lookupRange.Cells(i, 1).Value = lookupValue Then
            result = result & returnRange.Cells(i, 1).Value & delimiter
        End If
    Next i
    If Len(result) > 0 Then
        JoinLookupValues = Left(result, Len(result) - Len(delimiter))
    Else
        JoinLookupValues = ""
    End If
End Function

3. Sla deze code vervolgens op en sluit het Modulevenster, ga terug naar je werkblad en voer de onderstaande formule in een lege cel in waar je het resultaat wilt retourneren. Sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren, zie screenshot:

=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")

Kortom, het retourneren van meerdere opzoekwaarden in één cel gescheiden door komma's kan efficiënt worden bereikt met verschillende methoden, afhankelijk van je Excel-versie en behoeften. Door de methode te selecteren die het beste bij je vereisten past, kun je je gegevensanalyse stroomlijnen en je productiviteit verbeteren. Als je geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, onze website biedt duizenden tutorials.

Gerelateerde artikelen: