Note: The other languages of the website are Google-translated. Back to English

Hoe vlookup om meerdere waarden in één cel in Excel te retourneren?

Normaal gesproken kunt u in Excel, wanneer u de functie VERT.ZOEKEN gebruikt, als er meerdere waarden zijn die aan de criteria voldoen, gewoon de eerste krijgen. Maar soms wilt u alle corresponderende waarden die aan de criteria voldoen, in één cel retourneren zoals in het onderstaande screenshot, hoe zou u dit kunnen oplossen?

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

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

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

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:

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

Opmerking: In de bovenstaande formule, A2: A11 is het opzoekbereik de opzoekgegevens bevat, E2 is de opzoekwaarde, C2: C11 is het gegevensbereik waarvan u de overeenkomende waarden wilt retourneren, ","is het scheidingsteken om de meerdere records te scheiden.
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:

=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, ""))

Opmerking: In de bovenstaande formule, A2: A11 is het opzoekbereik de opzoekgegevens bevat, E2 is de opzoekwaarde, C2: C11 is het gegevensbereik waarvan u de overeenkomende waarden wilt retourneren, ","is het scheidingsteken om de meerdere records te scheiden.

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 ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

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

Opmerking: In de bovenstaande formule, A2: A11 is het opzoekbereik de opzoekgegevens bevat, E2 is de opzoekwaarde, C2: C11 is het gegevensbereik waarvan u de overeenkomende waarden wilt retourneren, ","is het scheidingsteken om de meerdere records te scheiden.
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 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 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 heeft ingevoerd, klikt u op Tools > Referenties in de geopende Microsoft Visual Basic voor toepassingen venster, en dan, in de pop-out 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:

Opmerking: In de bovenstaande formule, A2: C11 is het gegevensbereik dat u wilt gebruiken, E2 is de opzoekwaarde, het nummer 3 is het kolomnummer dat de geretourneerde waarden bevat.

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

 Als u ons Kutools for Excel, Met Geavanceerd Combineer rijen functie, kunt u snel de rijen samenvoegen of combineren op basis van dezelfde waarde en enkele berekeningen uitvoeren als u nodig hebt.

Opmerking:Om dit toe te passen Geavanceerd Combineer rijen, ten eerste moet u het Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.

Na het installeren van Kutools for Excelgaat u als volgt te werk:

1. Selecteer het gegevensbereik waarvoor u de ene kolomgegevens 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 Combineren om een ​​scheidingsteken te kiezen voor het scheiden van de gecombineerde gegevens.

4. Dan klikken OK knop, en je krijgt de volgende resultaten:

Download en gratis proefversie Kutools for Excel Nu!

Meer relatieve artikelen:

  • VERT.ZOEKEN-functie met enkele eenvoudige en geavanceerde voorbeelden
  • In Excel is de functie VERT.ZOEKEN een krachtige functie voor de meeste Excel-gebruikers, die wordt gebruikt om naar een waarde uiterst links in het gegevensbereik te zoeken en een overeenkomende waarde in dezelfde rij te retourneren vanuit een kolom die u hebt opgegeven. Deze tutorial heeft het over het gebruik van de functie VERT.ZOEKEN met enkele basis- en geavanceerde voorbeelden in Excel.
  • Retourneer meerdere overeenkomende waarden op basis van een of meerdere criteria
  • Normaal gesproken is het opzoeken van een specifieke waarde en het retourneren van het overeenkomende item voor de meesten van ons eenvoudig met de functie VERT.ZOEKEN. Maar heb je ooit geprobeerd om meerdere overeenkomende waarden te retourneren op basis van een of meer criteria? In dit artikel zal ik enkele formules introduceren om deze complexe taak in Excel op te lossen.
  • Bekijk en retourneer meerdere waarden verticaal
  • Normaal gesproken kunt u de functie Vlookup gebruiken om de eerste overeenkomstige waarde te krijgen, maar soms wilt u alle overeenkomende records retourneren op basis van een specifiek criterium. In dit artikel zal ik het hebben over het opvullen en retourneren van alle overeenkomende waarden verticaal, horizontaal of in één enkele cel.
  • Bekijk en retourneer meerdere waarden uit de vervolgkeuzelijst
  • Hoe kunt u in Excel meerdere overeenkomstige waarden opvragen en retourneren uit een vervolgkeuzelijst, wat betekent dat wanneer u één item uit de vervolgkeuzelijst kiest, alle relatieve waarden tegelijk worden weergegeven. In dit artikel zal ik de oplossing stap voor stap introduceren.

De beste tools voor kantoorproductiviteit

Kutools for Excel Lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2021 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Volledige functies Gratis proefperiode van 30 dagen. 60 dagen geld-terug-garantie.
kte tabblad 201905

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!
officetab onderkant
Heb je vragen? Stel ze hier. (43)
Nog geen beoordelingen. Beoordeel als eerste!
David Awel
David Awel
Deze opmerking is gemaakt door de moderator op de site
Hoe zou ik deze formule aanpassen om elke geretourneerde waarde te scheiden, maar ", " en alleen unieke waarden te retourneren?
Antwoorden
0
0
Michael
Michael     David Awel
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor de code!!

Wat betreft wildcards, een manier om dit te omzeilen is het gebruik van INSTR

U kunt de [ If rng = pValue Then ] vervangen door [ InStr(1, rng.Value, pValue) Then ] en als u niet wilt dat het hoofdlettergevoelig is, gebruik dan [ InStr(1, rng.Value, pValue, vbTextCompare) Dan ]
Antwoorden
0
0
David V
David V
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor de VBA-code hierboven. Kun je me vertellen hoe ik de resultaten op een nieuwe regel in de cel kan laten invoeren, bijv. Alt-Enter 300 400 1000 1300
Antwoorden
0
0
Colleen
Colleen
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor het delen van bovenstaande code. Ik gebruik dit nu al een aantal maanden, maar vandaag lijkt het niet te werken. Ik krijg lege cellen in plaats van de gebruikelijke fout wanneer er gegevens moeten worden geretourneerd. Nog ideeën?
Antwoorden
0
0
Ajay
Ajay
Deze opmerking is gemaakt door de moderator op de site
Geweldig werk.. Heb precies wat ik wil!!! Hou ervan !!
Antwoorden
0
0
Ankit Jaïn
Ankit Jaïn
Deze opmerking is gemaakt door de moderator op de site
Hallo, ik ben echt onder de indruk van het werk en het is zo gemakkelijk om er een te maken om deze functie te gebruiken. maar ik heb verdere ondersteuning nodig. Mijn ? is dat hoe kan ik een nummer selecteren uit een cel met meerdere cellen in mijn vlookup-array. dwz als cel A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Appel D2 = banaan Dus hoe kan ik 100 selecteren uit mijn tabelarraykolom C om correspondent af te leiden D1 = appel Houd er rekening mee dat ik 7-cijferige getallen heb in mijn opzoekwaarde en tabelarray die wordt gescheiden door een ";". Ik zou het erg op prijs stellen als je dit kunt oplossen en me kunt helpen om veel tijd te besparen.
Antwoorden
0
0
Ari
Ari
Deze opmerking is gemaakt door de moderator op de site
Bedankt voor de VBA-code. Ik heb precies wat ik wil! Ik heb alleen de code "rng.Offset(0, pIndex - 1)" gewijzigd in "rng.Offset(0, pIndex - 2)". Zo is MYVLOOKUP in staat om van rechts naar links te zoeken.
Antwoorden
0
0
Vlaamse gaai
Vlaamse gaai
Deze opmerking is gemaakt door de moderator op de site
Dit is precies wat ik zocht en er niet aan dacht om gewoon mijn eigen UDF te maken. Het werkt echter niet precies zoals VERT.ZOEKEN. Als de string die u zoekt niet alleen in de eerste kolom staat, kan deze u gegevens opleveren die buiten het oorspronkelijke bereik vallen. Naam Nummer Andere naam Kolom niet binnen bereik gepasseerd Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Als de bovenstaande tabel cellen A1:D7 was als u alleen A1:C7 passeerde, De functie "MYVLOOKUP" retourneert 1 1 2 2 5 5 terwijl u zou verwachten dat deze 1 2 5 zou retourneren. De onderstaande wijzigingen lossen het probleem op: Functie MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6 /9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1). Waarde = pWaarde Dan xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'Voor elke rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Antwoorden
0
0
cory
cory
Deze opmerking is gemaakt door de moderator op de site
Dit werkt prima, maar ik heb hulp nodig met de opdracht om duplicaten uit de resultaten te verwijderen. Maar serieus, geweldig werk.
Antwoorden
0
0
cory
cory
Deze opmerking is gemaakt door de moderator op de site
Dit werkt prima, maar ik heb nog steeds hulp nodig met de opdrachtfunctie om duplicaten uit de resultaten te verwijderen.
Antwoorden
0
0
Beroven
Beroven
Deze opmerking is gemaakt door de moderator op de site
Houd mij op de hoogte van vervolgopmerkingen
Antwoorden
0
0
Ajay
Ajay
Deze opmerking is gemaakt door de moderator op de site
Retourneer niets! na het toepassen van MYLOOKUP geeft geen resultaat maar blanco.
Antwoorden
0
0
Spyros Tzortzis
Spyros Tzortzis
Deze opmerking is gemaakt door de moderator op de site
Hallo, het werkt goed. Wat ik zou willen doen is de code aanpassen om de waarderesultaten te scheiden met "///" of een andere markering (om technische redenen wil ik niet slechts een enkel tekenscheidingsteken). Ook merkte ik dat deze formule niet werkt met een wildcard. Ik weet dat ik te veel vraag, maar het werkt niet omdat vlookup kan werken als ik zoek naar =myvlookup("*"&E6&"*",$A$2:$C$15,2) wat het zou doen/zou kunnen doen. Enige hulp?
Antwoorden
0
0
Spyros Tzortzis
Spyros Tzortzis
Deze opmerking is gemaakt door de moderator op de site
Let op. Ik heb ontdekt hoe ik een scheidingsteken in die uitvoer kan krijgen. Het is rudimentair. Maar ik kwam erachter. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Het laatste en meest gewenste ding is echter om het te laten werken met wildcards in de zoekcriteria. Nogmaals bedankt voor deze mooie en briljante oplossing. Uiterst behulpzaam. Ik wil nu gewoon dat de macro wordt uitgevoerd en permanent in mijn Excel wordt geïnstalleerd, wat ik ook doe, zodat ik het kan gebruiken wanneer dat nodig is. En wildcards! Hartelijk bedankt. Wildcards zijn het enige dat u nog hoeft te doen.
Antwoorden
0
0
Alex Chow
Alex Chow
Deze opmerking is gemaakt door de moderator op de site
Om een ​​uniek record te krijgen, kunt u het onderstaande gebruiken: (gewijzigd door te verwijzen naar andere gebruikerscode) Functie MYVLOOKUP (pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310 'Updated 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim Rows As Long, i As Long Rows = pWorkRng.Rows.Count For i = 1 To Rows If pWorkRng.Cells(i, 1).Value = pValue Then xResult = xResult & "," & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i Dim varSection As Variant Dim sTemp As String Dim sDelimiter As String sDelimiter = "," For Each varSection In Split (xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Then sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) + 1) EndDelimiter
Antwoorden
0
0
Curtis
Curtis
Deze opmerking is gemaakt door de moderator op de site
Dit werkte perfect, maar het kostte me wat tijd om de functie correct te laten werken in mijn spreadsheet met 20 tabbladen en 50k+ regels. Nu is de GROTE vraag hoe je die begrensde string moet nemen en vervolgens elk item als een Index/Match (niet gehuwd met Index/Match, maar het lijkt sneller) opzoekwaarde in een andere dataset, waarbij de SOM-waarde van alle rendementen in één cel wordt geretourneerd . Mijn scenario is dat ik een enkele bestelling heb met meerdere facturen. Uw MYVLOOKUP-functie werkt uitstekend om alle facturen in één cel te rapporteren. Wat ik nu wil doen, is elke aaneengeschakelde aangifte met de gerapporteerde cel nemen, door die array draaien en de bedragen van elke factuur terug in de formulecel optellen. Ik waardeer alle hulp die u hierbij kunt bieden en bedankt voor de MYVLOOKUP-functie!
Antwoorden
0
0
Vlaamse gaai
Vlaamse gaai
Deze opmerking is gemaakt door de moderator op de site
Wat ik ook doe, ik krijg altijd #waarde! geretourneerd in plaats van een resultaat. vlookup werkt prima, dus de gegevens werken. Heb het proces van het inschakelen van macro's al gevolgd. Ik heb zelfs alles samengevoegd tot één vel. Enig idee??
Antwoorden
0
0
Kabilankumar
Kabilankumar
Deze opmerking is gemaakt door de moderator op de site
Mooie macro, handig. Maar moet weten of het kan worden aangepast om 2 criteria te controleren en heeft iemand het toch gevonden om wildcards eraan te laten werken. Alle hulp?
Antwoorden
0
0
Zullen
Zullen
Deze opmerking is gemaakt door de moderator op de site
Is er een manier om het resultaat aan te passen, zodat in plaats van 1000 1000 -1000 het bijvoorbeeld 1,000/1,000/(1,000) laat zien?
Antwoorden
0
0
Markeer
Markeer
Deze opmerking is gemaakt door de moderator op de site
Geweldige functie, maar het doorspitten van 100,000 records is een beetje veel voor mijn arme laptop, ik moet hem 's nachts laten werken!
Antwoorden
0
0
Tom T.
Tom T.
Deze opmerking is gemaakt door de moderator op de site
Dit is geweldig, bedankt!
Antwoorden
0
0
Er zijn nog geen reacties geplaatst
Laad meer

Volg ons

Copyright © 2009 - www.extendoffice.com. | Alle rechten voorbehouden. Aangedreven door ExtendOffice. | Sitemap
Microsoft en het Office-logo zijn handelsmerken of gedeponeerde handelsmerken van Microsoft Corporation in de Verenigde Staten en / of andere landen.
Beschermd door Sectigo SSL