Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe te vlookup en concatenate meerdere overeenkomstige waarden in Excel?

Zoals we allemaal weten, de Vlookup functie in Excel kan ons helpen een waarde op te zoeken en de bijbehorende gegevens in een andere kolom te retourneren, maar in het algemeen kan deze alleen de eerste relatieve waarde krijgen als er meerdere overeenkomende gegevens zijn. In dit artikel zal ik het hebben over het vlookup en samenvoegen van meerdere corresponderende waarden in slechts één cel of een verticale lijst.

Vlookup en retourneer meerdere overeenkomende waarden verticaal met de formule

Vlookup en voeg meerdere overeenkomende waarden in een cel samen met User Defined Function

Vlookup en voeg meerdere overeenkomende waarden samen in een cel met Kutools voor Excel


Vlookup en combineer meerdere overeenkomende waarden in een cel:

Doordat uw bureau Kutools for Excel's Geavanceerd Combineer rijen utiliteit, u kunt snel meerdere dubbele rijen combineren in één record op basis van sleutelkolommen en het kan ook enkele berekeningen toepassen, zoals som, gemiddelde, telling enzovoort voor andere kolommen.

  • 1. Geef de sleutelkolom op waarvan u de andere kolom wilt combineren op basis van;
  • 2. Kies het scheidingsteken voor uw gecombineerde gegevens.

doc combineer tekst op basis van criteria 10

Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!



Stel dat ik het volgende gegevensbereik heb om alle bijbehorende waarden op basis van een specifieke waarde verticaal te krijgen, zoals bij het volgende screenshot, kunt u een matrixformule toepassen.

doc vlookup concatenate 1

1. Voer deze formule in: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") in een lege cel waar u het resultaat wilt plaatsen, bijvoorbeeld E2, en druk dan op Ctrl + Shift + Enter sleutels samen om de relatieve waarde op een bepaald criterium te baseren, zie screenshot:

doc vlookup concatenate 2

Notes: In de bovenstaande formule:

A1: A16 is het kolombereik dat de specifieke waarde bevat waarnaar u wilt zoeken;

D2 geeft de specifieke waarde aan die u wilt opvouwen;

B1: B16 is het kolombereik waarvan u de bijbehorende gegevens wilt retourneren;

$ 1: $ 16 geeft de rijenverwijzing binnen het bereik aan.

2. Selecteer vervolgens cel E2 en sleep de vulgreep naar de cellen totdat u lege cellen krijgt en alle overeenkomende waarden in de kolom worden weergegeven zoals in de volgende schermafbeelding wordt weergegeven:

doc vlookup concatenate 3


In plaats van de relatieve waarden verticaal te krijgen, wilt u soms de overeenkomende waarden in één cel en ze samenvoegen met een specifiek scheidingsteken. In dit geval kan de volgende door de gebruiker gedefinieerde functie u een plezier doen.

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

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: Vlookup en voeg meerdere overeenkomende waarden in een cel samen

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Sla deze code vervolgens op en sluit deze, ga terug naar het werkblad en voer deze formule in: = Cusvlookup (D2, A1: B16,2) in een lege cel waar u het resultaat wilt plaatsen en druk op invoeren sleutel, alle bijbehorende waarden op basis van een specifieke gegevens zijn geretourneerd in een cel met scheidingsteken, zie screenshot:

doc vlookup concatenate 4

Notes: In de bovenstaande formule: D2 geeft de celwaarden aan die u wilt opzoeken, A1: B16 is het gegevensbereik dat u de gegevens wilt ophalen, het nummer 2 is het kolomnummer waarnaar de overeenkomende waarde moet worden teruggestuurd, u kunt deze verwijzingen naar uw behoefte wijzigen.


Als je Kutools for Excel, Met Geavanceerd Combineer rijen functie, kunt u deze taak snel voltooien met gemak. Met deze functie kunt u alle overeenkomende waarden combineren met een specifiek scheidingsteken op basis van dezelfde gegevens in een andere kolom.

Kutools for Excel : met meer dan 300 handige Excel-invoegtoepassingen, gratis om zonder beperking te proberen in 60-dagen.

Na het installeren van Kutools for Excel, alsjeblieft als volgt doen:

1. Selecteer het gegevensbereik waarvoor u de bijbehorende waarden wilt ontvangen op basis van de specifieke gegevens.

2. Dan klikken Kutools > Content > Geavanceerd Combineer rijenzie screenshot:

doc vlookup concatenate 5

3. In de Geavanceerd Combineer rijen dialoogvenster, klik op de kolomnaam die u wilt combineren op basis van en klik vervolgens op Hoofdsleutel knop, zie screenshot:

doc vlookup concatenate 6

4. Klik vervolgens op een andere kolomnaam waarvan u de overeenkomende waarden wilt retourneren en klik op Combineren om een ​​scheidingsteken te kiezen om de gecombineerde waarden te scheiden, zie screenshot:

doc vlookup concatenate 7

5. En klik vervolgens op Ok knop, alle bijbehorende waarden op basis van dezelfde waarden zijn gecombineerd met een specifiek scheidingsteken, zie screenshots:

doc vlookup concatenate 8  2 doc vlookup concatenate 9

Download en proef Kutools nu voor Excel!


Kutools for Excel: met meer dan 200 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering 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...
  • Werkt met Office 2007-2019 en 365 en ondersteunt alle talen. Het is eenvoudig te implementeren in uw bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, 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 verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    sym-john · 4 days ago
    Is there any way to get the unique "name" for "class1"
  • To post as a guest, your comment is unpublished.
    Russell · 4 months ago
    This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
    Column A: Dependencies (eg 10003 10004 10008)
    Column B: Task Reference (eg 10001)
    Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

    E.g:

    Row | Column A | Column B | Column C
    1 | | 10001 | 10002 10003
    2 | 10001 | 10002 | 10003
    3 | 10001 10002 | 10003 |
    • To post as a guest, your comment is unpublished.
      Jeff F · 7 days ago
      you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
  • To post as a guest, your comment is unpublished.
    jeff · 6 months ago
    The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

    Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 5 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 1 years ago
    I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
    I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
    My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

    Thank you so much for your help!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 years ago
    This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
  • To post as a guest, your comment is unpublished.
    Brett Bieker · 2 years ago
    Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values

    Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :)

    Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long)
    Dim rng As Range
    Dim xResult As String
    xResult = ""
    For Each rng In pWorkRng
    If rng = pValue Then
    xResult = xResult & ";" & rng.Offset(0, pIndex - 1)
    If Left(xResult, 1) = ";" Then
    xResult = MID(xResult,2,255)
    End If
    End If
    Next
    CusVlookup = xResult
    End Function
    • To post as a guest, your comment is unpublished.
      Anand · 1 years ago
      Make if condition for result if empty.

      Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
      'updateby Extendoffice 20151118
      Dim x As Range
      Dim result As String
      result = ""
      For Each x In lookuprange
      If x = lookupval Then
      If Not result = "" Then
      result = result & " " & x.Offset(0, indexcol - 1)
      Else
      result = x.Offset(0, indexcol - 1)
      End If
      Next x
      CusVlookup = result
      End Function
  • To post as a guest, your comment is unpublished.
    slohman · 2 years ago
    When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
  • To post as a guest, your comment is unpublished.
    Deepan Saha · 3 years ago
    How to get the result. Please help.

    data data1 result
    a 1 a1
    b 2 a2
    c b1
    b2
    c1
    c2