Hoe meerdere overeenkomstige waarden in Excel opvullen en samenvoegen?
Wanneer u VLOOKUP in Excel gebruikt, retourneert het doorgaans de eerste overeenkomende waarde uit een dataset. Er zijn echter scenario's waarin u mogelijk meerdere overeenkomstige waarden moet retourneren en samenvoegen (bijvoorbeeld alle namen die aan een specifieke klasse zijn gekoppeld). Omdat VLOOKUP dit alleen niet kan bereiken, zullen we geavanceerde technieken verkennen om het gewenste resultaat te bereiken.

Meerdere overeenkomstige waarden in Excel vergelijken en samenvoegen
Vlookup en koppel meerdere overeenkomstige waarden met TEXTJOIN en FILTER-functies
Als u Excel 365 of Excel 2021 gebruikt, biedt de combinatie van de functies TEXTJOIN en FILTER een krachtige en efficiënte manier om meerdere overeenkomstige waarden samen te voegen.
Voer in de doelcel de volgende formule in en sleep de formule vervolgens naar andere cellen om de cellen te vullen. Alle overeenkomstige overeenkomende waarden worden geëxtraheerd en gecombineerd in één cel. Zie screenshot:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, ""): Retourneert een array met alle waarden die aan de voorwaarde voldoen.
- $ B $ 2: $ B $ 16: Het gegevensbereik dat gefilterd moet worden.
- $ A $ 2: $ A $ 16 = D2: De filtervoorwaarde, waarbij waarden in $A$2:$A$16 gelijk moeten zijn aan de waarde in cel D2.
- TEXTJOIN(", ", WAAR, ...): Combineert alle overeenkomende waarden tot één tekenreeks, gescheiden door een komma en spatie.
- ", ": Het scheidingsteken dat wordt gebruikt om de waarden te scheiden (in dit geval een komma en een spatie).
- TRUE: Negeert lege waarden om ervoor te zorgen dat er geen extra scheidingstekens worden opgenomen.
Vlookup en koppel meerdere overeenkomstige waarden met Kutools voor Excel
Bent u de beperkingen van Excel's VLOOKUP bij het werken met meerdere overeenkomstige waarden beu? Maar met de One-to-Many Lookup-functie van Kutools voor Excel kunt u moeiteloos meerdere overeenkomstige waarden opzoeken en samenvoegen in slechts een paar klikken!
Na het installeren van Kutools for Excel, doe dit als volgt:
Klik Kutools > Super opzoeken > Eén-op-veel-opzoeken (geeft meerdere resultaten) om het dialoogvenster te openen. Geef in het dialoogvenster de onderstaande bewerkingen op:
- Selecteer het Output range en Zoek waarden op in de tekstvakken apart;
- Selecteer het tabelbereik dat u wilt gebruiken;
- Geef de sleutelkolom en de retourkolom op uit de Sleutelkolomn en Kolom retourneren apart naar beneden laten vallen;
- Klik ten slotte op OK knop.
Resultaat: Nu worden alle overeenkomende waarden samengevoegd in één cel. Zie screenshot:
Vlookup en koppel meerdere overeenkomstige waarden met behulp van een door de gebruiker gedefinieerde functie
Als u vertrouwd bent met VBA (Visual Basic for Applications), kunt u een User Defined Function (UDF) maken om meerdere overeenkomstige waarden samen te voegen. Deze methode is zeer aanpasbaar en werkt in alle versies van Excel.
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 Module Venster.
VBA-code: Vlookup en voeg meerdere overeenkomende waarden in een cel samen
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Sla vervolgens deze code op en sluit deze, ga terug naar het werkblad en voer deze formule in: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in een lege cel waar u het resultaat wilt plaatsen, sleep vervolgens de vulgreep omlaag om de formule naar andere cellen te vullen. Alle overeenkomstige waarden op basis van specifieke gegevens zijn geretourneerd in één cel met komma's en spaties als scheidingstekens, zie screenshot:
- D2: Dit is de waarde die u zoekt in het opgegeven bereik (LookupValue).
- A2: A16: Het bereik waarin de functie zoekt naar de opzoekwaarde (LookupRange).
- B2: B16: Het bereik met de waarden die moeten worden samengevoegd wanneer de opzoekwaarde overeenkomt (ReturnRange).
Het samenvoegen van meerdere overeenkomstige waarden in Excel kan worden bereikt via verschillende methoden, elk met zijn eigen voordelen. Of u nu de voorkeur geeft aan ingebouwde functies zoals TEXTJOIN en FILTER, externe tools zoals Kutools of aangepaste VBA-oplossingen, de sleutel is om de methode te kiezen die het beste past bij uw Excel-versie, vaardigheidsniveau en specifieke vereisten. Als u geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, onze website biedt duizenden tutorials.
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!