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

Hur kan jag koppla upp för att returnera flera värden i en cell i Excel?

Normalt, i Excel, när du använder VLOOKUP-funktionen, om det finns flera värden som matchar kriterierna, kan du bara få den första. Men ibland vill du returnera alla motsvarande värden som uppfyller kriterierna i en cell enligt följande skärmdump, hur kan du lösa det?

Vlookup för att returnera flera värden till en cell med TEXTJOIN-funktionen (Excel 2019 och Office 365)

Vlookup för att returnera flera värden i en cell med användardefinierad funktion

Vlookup för att returnera flera värden till en cell med en användbar funktion


Vlookup för att returnera flera värden till en cell med TEXTJOIN-funktionen (Excel 2019 och Office 365)

Om du har den högre versionen av Excel som Excel 2019 och Office 365 finns det en ny funktion - TEXTGÅ MED, med den här kraftfulla funktionen kan du snabbt söka efter och returnera alla matchande värden i en cell.

Vlookup för att returnera alla matchande värden i en cell

Använd nedanstående formel i en tom cell där du vill placera resultatet och tryck sedan på Ctrl + Skift + Enter tangenter tillsammans för att få det första resultatet och dra sedan fyllningshandtaget ner till cellen du vill använda den här formeln, så får du alla motsvarande värden enligt nedanstående skärmdump:

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

Notera: I ovanstående formel, A2: A11 är uppslagsintervallet innehåller uppslagsdata, E2 är uppslagsvärdet, C2: C11 är det dataområde som du vill returnera matchande värden från, ","är separator för att separera flera poster.

Vlookup för att returnera alla matchande värden utan dubbletter till en cell

Om du vill returnera alla matchande värden baserat på uppslagsdata utan dubbletter kan formeln nedan hjälpa dig.

Kopiera och klistra in följande formel i en tom cell och tryck sedan på Ctrl + Skift + Enter nycklar tillsammans för att få det första resultatet och kopiera sedan den här formeln för att fylla andra celler, så får du alla motsvarande värden utan dulpikat som visas nedan:

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

Notera: I ovanstående formel, A2: A11 är uppslagsintervallet innehåller uppslagsdata, E2 är uppslagsvärdet, C2: C11 är det dataområde som du vill returnera matchande värden från, ","är separator för att separera flera poster.

Vlookup för att returnera flera värden i en cell med användardefinierad funktion

Ovanstående TEXTJOIN-funktion är endast tillgänglig för Excel 2019 och Office 365, om du har andra lägre Excel-versioner, bör du använda några koder för att slutföra den här uppgiften.

Vlookup för att returnera alla matchande värden i en cell

1. Håll ner ALT + F11 knapparna och det öppnar Microsoft Visual Basic för applikationer fönster.

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

VBA-kod: Vlookup för att returnera flera värden i en cell

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. Spara och stäng sedan den här koden, gå tillbaka till kalkylbladet och ange den här formeln: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") in i en specifik tom cell där du vill placera resultatet, dra sedan ned fyllningshandtaget för att få alla motsvarande värden i en cell som du vill, se skärmdump:

Notera: I ovanstående formel, A2: A11 är uppslagsintervallet innehåller uppslagsdata, E2 är uppslagsvärdet, C2: C11 är det dataområde som du vill returnera matchande värden från, ","är separator för att separera flera poster.

Vlookup för att returnera alla matchande värden utan dubbletter till en cell

För att ignorera dubbletterna i de returnerade matchande värdena gör du med koden nedan.

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 Modulfönster.

VBA-kod: Vlookup och returnera flera unika matchade värden i en cell

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. När du har infogat koden klickar du sedan på verktyg > Referenser i det öppnade Microsoft Visual Basic för applikationer fönstret och sedan, i poppade ut Referenser - VBAProject dialogruta, kontrollera Microsoft Scripting Runtime alternativet i Tillgängliga referenser listruta, se skärmdumpar:

4. Klicka sedan OK för att stänga dialogrutan, spara och stäng kodfönstret, gå tillbaka till kalkylbladet och skriv in denna formel: =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:

Notera: I ovanstående formel, A2: C11 är det dataområde du vill använda, E2 är uppslagsvärdet, numret 3 är kolumnnumret som innehåller de returnerade värdena.

Vlookup för att returnera flera värden till en cell med en användbar funktion

 Om du har vår Kutools för Excel, med dess Avancerade kombinera rader kan du snabbt slå samman eller kombinera raderna baserat på samma värde och göra några beräkningar som du behöver.

Notera:Att tillämpa detta Avancerade kombinera raderFör det första bör du ladda ner Kutools för Excel, och använd sedan funktionen snabbt och enkelt.

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

1. Välj det dataområde som du vill kombinera en kolumninformation baserat på en annan kolumn.

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

3. I poppade ut Avancerade kombinera rader dialog ruta:

  • Klicka på nyckelkolonnnamnet som ska kombineras baserat på och klicka sedan på Primärnyckel
  • Klicka sedan på en annan kolumn som du vill kombinera dess data baserat på nyckelkolumnen och klicka Kombinera för att välja en separator för att separera de kombinerade uppgifterna.

4. Klicka sedan på OK -knappen och du får följande resultat:

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


Fler relativa artiklar:

  • VLOOKUP-funktion med några grundläggande och avancerade exempel
  • I Excel är VLOOKUP-funktionen en kraftfull funktion för de flesta Excel-användare, som används för att leta efter ett värde längst till vänster i dataområdet och returnera ett matchande värde i samma rad från en kolumn du angav. Denna handledning talar om hur man använder VLOOKUP-funktionen med några grundläggande och avancerade exempel i Excel.
  • Returnera flera matchande värden baserat på ett eller flera kriterier
  • Normalt är det enkelt för de flesta av oss att leta upp ett visst värde och returnera det matchande objektet med hjälp av VLOOKUP-funktionen. Men har du någonsin försökt att returnera flera matchande värden baserat på ett eller flera kriterier? I den här artikeln kommer jag att presentera några formler för att lösa denna komplexa uppgift i Excel.
  • Vlookup och returnera flera värden vertikalt
  • Normalt kan du använda Vlookup-funktionen för att få det första motsvarande värdet, men ibland vill du returnera alla matchande poster baserat på ett specifikt kriterium. Den här artikeln kommer jag att prata om hur man slår på och returnerar alla matchande värden vertikalt, horisontellt eller i en enda cell.
  • Vlookup och returnera flera värden från rullgardinslistan
  • I Excel, hur kan du söka efter och returnera flera motsvarande värden från en rullgardinslista, vilket innebär att när du väljer ett objekt från rullgardinsmenyn visas alla dess relativa värden på en gång. Den här artikeln presenterar jag lösningen steg för steg.

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-2021 och 365. Stöder alla språk. Enkel implementering 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
Sortera kommentarer efter
Kommentarer (43)
Inga betyg än. Bli först med att betygsätta!
Denna kommentar minimerades av moderatoren på webbplatsen
Hur skulle jag justera den här formeln för att separera varje returnerat värde men ", " samt bara returnera unika värden?
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för koden!!

När det gäller jokertecken är en väg runt att använda INSTR

Du kan ersätta [ If rng = pValue Then ] med [ InStr(1, rng.Value, pValue) Then ] och om du inte vill att det ska vara skiftlägeskänsligt, använd [ InStr(1, rng.Value, pValue, vbTextCompare) Sedan ]
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för VBA-koden ovan. Kan du berätta för mig hur jag får resultaten att komma in på en ny rad i cellen, dvs som Alt-Enter 300 400 1000 1300
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för att du delar med dig av ovanstående kod. Jag har använt detta i flera månader nu men idag verkar det inte fungera. Jag får tomma celler istället för det vanliga felet när det finns data som ska returneras. Några tankar?
Denna kommentar minimerades av moderatoren på webbplatsen
Fantastiskt jobb.. Fick precis vad jag vill ha!!! Älskar det !!
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, jag är verkligen imponerad av arbetet och det är så enkelt att skapa en för att använda den här funktionen. men jag behöver ytterligare stöd. Min ? är att hur kan jag välja ett nummer från en cell med flera celler i min vlookup-array. dvs om cell A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Äpple D2 = banan Så hur kan jag välja 100 från min tabellmatris kolumn C för att härleda korrespondent D1 = äpple Observera att jag har sjusiffriga nummer i mitt uppslagsvärde och tabellmatris som är åtskilda av ett ";". Jag skulle verkligen uppskatta om du kan lösa detta och hjälpa mig att spara mycket tid.
Denna kommentar minimerades av moderatoren på webbplatsen
Tack för VBA-koden. Jag fick precis vad jag vill ha! Jag modifierade bara koden "rng.Offset(0, pIndex - 1)" till "rng.Offset(0, pIndex - 2) ". Så kan MYVLOOKUP söka från höger till vänster.
Denna kommentar minimerades av moderatoren på webbplatsen
Detta är precis vad jag letade efter och tänkte inte på att bara göra min egen UDF. Det kommer dock inte att fungera exakt som VLOOKUP. Om strängen du letar efter inte bara finns i den första kolumnen kan den ge dig data utanför det ursprungliga intervallet. Namn Nummer Annat namn Kolumn inte inom intervallet passerad Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Om tabellen ovan var cellerna A1:D7 om du bara klarade A1:C7 "MYVLOOKUP"-funktionen returnerar 1 1 2 2 5 5 när du förväntar dig att den ska returnera 1 2 5. Ändringarna nedan åtgärdar problemet: Funktion MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Uppdatering 20150310' Uppdaterad 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). Värde = pValue Sedan xResult = xResult & " " & pWorkRng.Cells(i, 1).Offset(0, pIndex - 1) End If Next i 'For Every rng In pWorkRng ' If rng = pValue Then ' xResult = xResult & " " & rng.Offset(0, pIndex - 1) ' End If 'Next MYVLOOKUP = xResult End Function
Denna kommentar minimerades av moderatoren på webbplatsen
Det här fungerar utmärkt, men jag behöver hjälp med kommandot för att ta bort dubbletter från resultaten. Men allvarligt, bra jobbat.
Denna kommentar minimerades av moderatoren på webbplatsen
Detta fungerar utmärkt, men jag behöver fortfarande hjälp med kommandofunktionen för att ta bort dubbletter från resultaten.
Denna kommentar minimerades av moderatoren på webbplatsen
Meddela mig om uppföljande kommentarer
Denna kommentar minimerades av moderatoren på webbplatsen
Lämna ingenting tillbaka! efter applicering av MYLOOKUP ger inget resultat men tomt.
Denna kommentar minimerades av moderatoren på webbplatsen
Hej, det fungerar bra. Vad jag skulle vilja göra är att anpassa koden för att separera dina värderesultat med "///" eller någon annan markör (av tekniska skäl vill jag inte bara ha en enstaka teckenseparator). Jag märkte också att den här formeln inte fungerar med ett jokertecken. Jag vet att jag frågar för mycket, men det gör det inte eftersom vlookup kan fungera när jag söker efter =myvlookup("*"&E6&"*",$A$2:$C$15,2) vilket det skulle göra/kunde göra. Någon hjälp?
Denna kommentar minimerades av moderatoren på webbplatsen
Se upp. Jag kom på hur man får en separator i den utgången. Det är rudimentärt. Men jag kom på det. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Den sista och mest önskade saken är dock att den kan arbeta med jokertecken i sökkriterierna. Tack igen för denna vackra och lysande lösning. Extremt hjälpsam. Nu vill jag bara få makrot att köra och installeras i mitt excel permanent oavsett vad jag gör så jag kan använda det när jag behöver. Och jokertecken! Tack så mycket. Jokertecken är allt som återstår att göra.
Denna kommentar minimerades av moderatoren på webbplatsen
För att få unik post kan du använda nedan: (modifierad med hänvisa till annan användarkod) Funktion MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Update 20150310' Uppdaterad 6/9/16 Jay Coltrain 'Dim rng As Range Dim xResult As String xResult = "" Dim rader så långa, i så långa rader = pWorkRng.Rows.Count For i = 1 till rader Om 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 = "," För varje varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Då sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) Fun +
Denna kommentar minimerades av moderatoren på webbplatsen
Detta fungerade perfekt, men det tog mig lite tid att få funktionen att fungera ordentligt i mitt kalkylblad med 20 flikar, 50k+ rader. Nu är den STORA frågan hur man tar den avgränsade strängen och sedan använder varje post som ett Index/Match (inte gift med Index/Match, men det verkar snabbare) uppslagsvärde i en annan datauppsättning, vilket returnerar SUM-värdet för alla returer till en cell . Mitt scenario är att jag har en enda order som har flera fakturor. Din MYVLOOKUP-funktion fungerar utmärkt för att rapportera tillbaka alla fakturor i en cell. Vad jag vill göra nu är att ta varje sammanhängande retur med den rapporterade cellen, snurra genom den matrisen och summera betalningsbeloppen för varje faktura tillbaka till formelcellen. Jag uppskattar all hjälp du kan erbjuda med detta och tack för MYVLOOKUP-funktionen!
Denna kommentar minimerades av moderatoren på webbplatsen
Oavsett vad jag gör får jag alltid #värde! returnerade istället för ett resultat. vlookup fungerar bra, så data fungerar. Har redan följt processen med att aktivera makron. Jag kombinerade till och med allt till ett enda ark. Några idéer??
Denna kommentar minimerades av moderatoren på webbplatsen
Bra makro, användbart. Men behöver veta kan det ändras för att kontrollera 2 kriterier och finns det någon som ändå kan få jokertecken att fungera på det. Någon hjälp?
Denna kommentar minimerades av moderatoren på webbplatsen
Finns det något sätt att ändra resultatet så istället för att visa 1000 1000 -1000 skulle det visa till exempel 1,000/1,000/(1,000)?
Denna kommentar minimerades av moderatoren på webbplatsen
Utmärkt funktion, men chunking genom 100,000 XNUMX poster visar lite mycket för min stackars bärbara dator, måste låta den köra över natten!
Denna kommentar minimerades av moderatoren på webbplatsen
Det här är fantastiskt, tack!
Det finns inga kommentarer här ännu
Ladda fler
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser

Följ oss

Copyright © 2009 - www.extendoffice.com. | Alla rättigheter förbehållna. Drivs av ExtendOffice. | | Sitemap
Microsoft och Office-logotypen är varumärken eller registrerade varumärken som tillhör Microsoft Corporation i USA och / eller andra länder.
Skyddad av Sectigo SSL