Note: The other languages of the website are Google-translated. Back to English
Logga in  \/ 
x
or
x
Registrera  \/ 
x

or

Hur kopplar man upp och sammanfogar flera motsvarande värden i Excel?

Som vi alla visste, LETARAD funktion i Excel kan hjälpa oss att leta upp ett värde och returnera motsvarande data i en annan kolumn, men i allmänhet kan det bara få det första relativa värdet om det finns flera matchande data. I den här artikeln kommer jag att prata om hur man kan koppla upp och sammanfoga flera motsvarande värden i endast en cell eller en vertikal lista.

Vlookup och returnera flera matchande värden vertikalt med formeln

Vlookup och sammanfoga flera matchande värden i en cell med användardefinierad funktion

Vlookup och sammanfoga flera matchande värden i en cell med Kutools för Excel


Om jag antar att jag har följande dataområde, för att få alla motsvarande värden baserat på ett visst värde vertikalt som följande skärmdump visas, kan du använda en matrisformel.

doc vlookup sammanfoga 1

1. Ange denna formel: =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))),"") till en tom cell där du vill placera resultatet, till exempel E2, och tryck sedan på Ctrl + Skift + Enter nycklar tillsammans för att få den relativa värdebasen på ett specifikt kriterium, se skärmdump:

doc vlookup sammanfoga 2

Anmärkningar: I ovanstående formel:

A1: A16 är kolumnområdet som innehåller det specifika värde du vill leta efter;

D2 indikerar det specifika värde du vill ta upp;

B1: B16 är det kolumnområde som du vill returnera motsvarande data från;

$ 1: $ 16 anger radereferensen inom intervallet.

2. Välj sedan cell E2 och dra påfyllningshanteringen ner till cellerna tills du får tomma celler och alla matchande värden listas i kolumnen som följande skärmdump visas:

doc vlookup sammanfoga 3


I stället för att få de relativa värdena vertikalt vill du ibland matcha värdena i en cell och sammanfoga dem med en specifik separator. I det här fallet kan följande användardefinierade funktion göra dig en tjänst.

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulernaoch klistra in följande kod i Modulerna Fönster.

VBA-kod: Vlookup och sammanfoga flera matchande värden i en cell

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
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. Spara och stäng sedan den här koden, gå tillbaka till kalkylbladet och ange den här formeln: = cusvlookup (D2, A1: B16,2) in i en tom cell där du vill placera resultatet och tryck på ange nyckel, alla motsvarande värden baserade på en specifik data har returnerats till en cell med mellanseparator, se skärmdump:

doc vlookup sammanfoga 4

Anmärkningar: I ovanstående formel: D2 anger de cellvärden som du vill slå upp, A1: B16 är det dataområde som du vill hämta data, numret 2 är kolumnnumret som det matchande värdet ska returneras från, kan du ändra dessa referenser till ditt behov.


Om du har Kutools för Excel, med dess Avancerade kombinera rader kan du snabbt avsluta det här jobbet med lätthet. Den här funktionen kan hjälpa dig att kombinera alla matchande värden med en specifik avgränsare baserat på samma data i en annan kolumn.

Kutools för Excel : med mer än 300 praktiska Excel-tillägg, gratis att prova utan begränsning på 30 dagar.

När du har installerat Kutools för Excel, gör så här:

1. Välj det dataområde som du vill få motsvarande värden baserat på specifika data.

2. Klicka sedan Kutools > Slå ihop och dela > Avancerade kombinera rader, se skärmdump:

3. I Avancerade kombinera rader Klicka på kolumnnamnet som du vill kombinera baserat på och klicka sedan på Primärnyckel knapp, se skärmdump:

doc vlookup sammanfoga 6

4. Klicka sedan på ett annat kolumnnamn som du vill returnera de matchade värdena och klicka på Kombinera för att välja en separator för att separera de kombinerade värdena, se skärmdump:

doc vlookup sammanfoga 7

5. Och klicka sedan på Ok -knappen, alla motsvarande värden baserade på samma värden har kombinerats med en specifik separator, se skärmdumpar:

doc vlookup sammanfoga 8 2 doc vlookup sammanfoga 9

 Ladda ner och testa gratis Kutools för Excel nu!


Kutools för Excel: med mer än 300 praktiska Excel-tillägg, gratis att prova utan begränsning på 30 dagar. Ladda ner och testa gratis nu!

De bästa Office-produktivitetsverktygen

Kutools för Excel löser de flesta av dina problem och ökar din produktivitet med 80%

  • återanvändning: Sätt snabbt i komplexa formler, diagram och allt som du har använt tidigare; Kryptera celler med lösenord; Skapa e-postlista och skicka e-post ...
  • Super Formula Bar (enkelt redigera flera rader med text och formel); Läslayout (enkelt läsa och redigera ett stort antal celler); Klistra in i filtrerat intervall...
  • Sammanfoga celler / rader / kolumner utan att förlora data; Delat cellinnehåll; Kombinera duplicerade rader / kolumner... Förhindra duplicerade celler; Jämför intervall...
  • Välj Duplicera eller Unikt Rader; Välj tomma rader (alla celler är tomma); Super Find och Fuzzy Find i många arbetsböcker; Slumpmässigt val ...
  • Exakt kopia Flera celler utan att ändra formelreferens; Skapa referenser automatiskt till flera ark; Sätt in kulor, Kryssrutor och mer ...
  • Extrahera text, Lägg till text, ta bort efter position, Ta bort mellanslag; Skapa och skriva ut personsökningstalsatser; Konvertera mellan celler innehåll och kommentarer...
  • Superfilter (spara och tillämpa filterscheman på andra ark); Avancerad sortering efter månad / vecka / dag, frekvens och mer; Specialfilter av fet, kursiv ...
  • Kombinera arbetsböcker och arbetsblad; Sammanfoga tabeller baserat på nyckelkolumner; Dela data i flera ark; Batchkonvertera xls, xlsx och PDF...
  • Mer än 300 kraftfulla funktioner. Stöder Office / Excel 2007-2019 och 365. Stöder alla språk. Enkel distribution i ditt företag eller organisation. Fullständiga funktioner 30-dagars gratis provperiod. 60-dagars pengarna tillbaka-garanti.
kte-flik 201905

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50%och minskar hundratals musklick för dig varje dag!
officetab botten
Say something here...
symbols left.
You are guest
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 · 2 years ago
    Is there any way to get the unique "name" for "class1"
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, sym-john,
      Maybe the below article can solve your problem, please view it:
      https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
  • To post as a guest, your comment is unpublished.
    Russell · 2 years 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 · 2 years 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 · 2 years 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 · 2 years ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 3 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 · 3 years ago
    Is there a way to delete the duplicate values in the concatenate?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Jacob,
      May be the following article can help you to solve your problem.
      https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        carmela · 2 years ago
        Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
        • To post as a guest, your comment is unpublished.
          Jerry · 8 months ago
          you can add two extra condition to skip blank cells and to skip duplicates:
          For i = 1 To CriteriaRange.Count
          If CriteriaRange.Cells(i).Value = Condition Then
          If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
          If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
          xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
          End If
          End If
          End If
          Next i
  • To post as a guest, your comment is unpublished.
    sreetechnical@gmail.com · 3 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 · 4 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 · 3 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 · 5 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 · 5 years ago
    How to get the result. Please help.

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