Hoppa till huvudinnehåll

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!

Bästa kontorsproduktivitetsverktyg

Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50 + Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

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!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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 |
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
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!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations