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

Hvordan vlookup for at returnere flere værdier i en celle i Excel?

Normalt, i Excel, når du bruger VLOOKUP-funktionen, hvis der er flere værdier, der matcher kriterierne, kan du bare få den første. Men nogle gange vil du returnere alle de tilsvarende værdier, der opfylder kriterierne, i en celle som vist nedenstående skærmbillede, hvordan kunne du løse det?

Vlookup for at returnere flere værdier i en celle med TEXTJOIN-funktionen (Excel 2019 og Office 365)

Vlookup for at returnere flere værdier i en celle med brugerdefineret funktion

Vlookup for at returnere flere værdier til en celle med en nyttig funktion


Vlookup for at returnere flere værdier i en celle med TEXTJOIN-funktionen (Excel 2019 og Office 365)

Hvis du har den højere version af Excel som Excel 2019 og Office 365, er der en ny funktion - TEKSTJOIN, med denne kraftfulde funktion kan du hurtigt vlookup og returnere alle matchende værdier i en celle.

Vlookup for at returnere alle matchende værdier i en celle

Anvend nedenstående formel i en tom celle, hvor du vil placere resultatet, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få det første resultat, og træk derefter fyldhåndtaget ned til den celle, du vil bruge denne formel, og du får alle tilsvarende værdier som vist nedenstående skærmbillede:

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

Bemærk: I ovenstående formel A2: A11 er opslagsområdet indeholder opslagsdata, E2 er opslagsværdien, C2: C11 er det dataområde, som du vil returnere de matchende værdier fra, ","er separatoren for at adskille flere poster.

Vlookup for at returnere alle matchende værdier uden dubletter i en celle

Hvis du vil returnere alle matchende værdier baseret på opslagsdata uden dubletter, kan nedenstående formel hjælpe dig.

Kopier og indsæt følgende formel i en tom celle, og tryk derefter på Ctrl + Skift + Enter nøgler sammen for at få det første resultat, og kopier derefter denne formel for at udfylde andre celler, og du får alle tilsvarende værdier uden dulpikat som vist nedenfor:

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

Bemærk: I ovenstående formel A2: A11 er opslagsområdet indeholder opslagsdata, E2 er opslagsværdien, C2: C11 er det dataområde, som du vil returnere de matchende værdier fra, ","er separatoren for at adskille flere poster.

Vlookup for at returnere flere værdier i en celle med brugerdefineret funktion

Ovenstående TEXTJOIN-funktion er kun tilgængelig for Excel 2019 og Office 365, hvis du har andre lavere Excel-versioner, skal du bruge nogle koder til at afslutte denne opgave.

Vlookup for at returnere alle matchende værdier i en celle

1. Hold nede ALT + F11 tasterne, og det åbner Microsoft Visual Basic til applikationer vindue.

2. Klik indsatte > Moduler, og indsæt følgende kode i Modul vindue.

VBA-kode: Vlookup for at returnere flere værdier til en celle

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. Gem derefter og luk denne kode, gå tilbage til regnearket, og indtast denne formel: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") ind i en bestemt tom celle, hvor du vil placere resultatet, og træk derefter fyldhåndtaget ned for at få alle de tilsvarende værdier i en celle, du vil have, se skærmbillede:

Bemærk: I ovenstående formel A2: A11 er opslagsområdet indeholder opslagsdata, E2 er opslagsværdien, C2: C11 er det dataområde, som du vil returnere de matchende værdier fra, ","er separatoren for at adskille flere poster.

Vlookup for at returnere alle matchende værdier uden dubletter i en celle

For at ignorere duplikaterne i de returnerede matchende værdier skal du gøre med nedenstående kode.

1. Hold nede Alt + F11 nøgler til at åbne Microsoft Visual Basic til applikationer vindue.

2. Klik indsatte > Moduler, og indsæt følgende kode i Modul vindue.

VBA-kode: Vlookup og returner flere unikke matchede værdier i en celle

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 indsat koden, skal du klikke på Værktøj > Referencer i det åbnede Microsoft Visual Basic til applikationer vindue og derefter i det dukkede ud Referencer - VBAProject dialogboks, tjek Microsoft Scripting Runtime valgmulighed i Tilgængelige referencer listefelt, se skærmbilleder:

4. Klik derefter på OK for at lukke dialogboksen, gemme og lukke kodevinduet, vende tilbage til regnearket og indtaste denne 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:

Bemærk: I ovenstående formel A2: C11 er det dataområde, du vil bruge, E2 er opslagsværdien, antallet 3 er kolonnetallet, der indeholder de returnerede værdier.

Vlookup for at returnere flere værdier til en celle med en nyttig funktion

 Hvis du har vores Kutools til Excel, Med Avancerede kombinere rækker funktion, kan du hurtigt flette eller kombinere rækkerne baseret på den samme værdi og foretage nogle beregninger, som du har brug for.

Bemærk:At anvende dette Avancerede kombinere rækkerFor det første skal du downloade Kutools til Excel, og anvend derefter funktionen hurtigt og nemt.

Efter installation Kutools til Excelskal du gøre som følger:

1. Vælg det dataområde, som du vil kombinere en kolonnedata baseret på en anden kolonne.

2. Klik Kutools > Flet og del > Avancerede kombinere rækker, se skærmbillede:

3. I poppet ud Avancerede kombinere rækker dialog boks:

  • Klik på det nøglekolonnens navn, der skal kombineres baseret på, og klik derefter på Primærnøgle
  • Klik derefter på en anden kolonne, som du vil kombinere dens data baseret på nøglekolonnen, og klik Kombiner for at vælge en separator til adskillelse af de kombinerede data.

4. Klik derefter på OK knappen, og du får følgende resultater:

Download og gratis prøveversion Kutools til Excel nu!


Flere relative artikler:

  • VLOOKUP-funktion med nogle grundlæggende og avancerede eksempler
  • I Excel er VLOOKUP-funktionen en kraftig funktion for de fleste af Excel-brugere, som bruges til at lede efter en værdi i venstre række af dataområdet og returnere en matchende værdi i samme række fra en kolonne, du har angivet. Denne vejledning taler om, hvordan du bruger VLOOKUP-funktionen med nogle grundlæggende og avancerede eksempler i Excel.
  • Returner flere matchende værdier baseret på et eller flere kriterier
  • Normalt er det let for de fleste af os at søge efter en bestemt værdi og returnere den matchende vare ved hjælp af funktionen VLOOKUP. Men har du nogensinde prøvet at returnere flere matchende værdier baseret på et eller flere kriterier? I denne artikel vil jeg introducere nogle formler til løsning af denne komplekse opgave i Excel.
  • Vlookup og returner flere værdier lodret
  • Normalt kan du bruge Vlookup-funktionen til at få den første tilsvarende værdi, men nogle gange vil du returnere alle matchende poster baseret på et specifikt kriterium. Denne artikel vil jeg tale om, hvordan man vlookup og returnerer alle matchende værdier lodret, vandret eller i en enkelt celle.
  • Vlookup og returner flere værdier fra rullelisten
  • Hvordan kunne du i Excel oprette og returnere flere tilsvarende værdier fra en rulleliste, hvilket betyder, at når du vælger et element fra rullelisten, vises alle dets relative værdier på én gang. Denne artikel vil jeg introducere løsningen trin for trin.

De bedste Office-produktivitetsværktøjer

Kutools til Excel løser de fleste af dine problemer og øger din produktivitet med 80%

  • Genbruge: Indsæt hurtigt komplekse formler, diagrammer og alt, hvad du har brugt før; Krypter celler med adgangskode Opret postliste og send e-mails ...
  • Super formel bar (let redigere flere linjer med tekst og formel); Læsning Layout (let at læse og redigere et stort antal celler); Indsæt til filtreret rækkevidde...
  • Flet celler / rækker / kolonner uden at miste data; Split celler indhold; Kombiner duplikerede rækker / kolonner... Forhindre duplikerede celler; Sammenlign områder...
  • Vælg Duplicate eller Unique Rækker; Vælg tomme rækker (alle celler er tomme); Super Find og Fuzzy Find i mange arbejdsbøger; Tilfældig valg ...
  • Præcis kopi Flere celler uden at ændre formelreference; Auto Opret referencer til flere ark; Indsæt kugler, Afkrydsningsfelter og mere ...
  • Uddrag tekst, Tilføj tekst, Fjern efter position, Fjern mellemrum; Opret og udskriv personsøgningssubtotaler; Konverter mellem celler indhold og kommentarer...
  • Superfilter (gem og anvend filterskemaer på andre ark); Avanceret sortering efter måned / uge / dag, hyppighed og mere; Specielt filter af fed, kursiv ...
  • Kombiner arbejdsbøger og arbejdsark; Fletabeller baseret på nøglekolonner; Opdel data i flere ark; Batch Konverter xls, xlsx og PDF...
  • Mere end 300 kraftfulde funktioner. Understøtter Office / Excel 2007-2019 og 365. Understøtter alle sprog. Nem implementering i din virksomhed eller organisation. Fuld funktioner 30-dages gratis prøveperiode. 60-dages pengene tilbage garanti.
kte-fane 201905

Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50% og reducerer hundredvis af museklik for dig hver dag!
officetab bund
Sorter kommentarer efter
Kommentarer (43)
Ingen vurderinger endnu. Vær den første til at bedømme!
Denne kommentar blev minimeret af moderatoren på webstedet
Hvordan vil jeg justere denne formel for at adskille hver returnerede værdi, men ", " samt kun returnere unikke værdier?
Denne kommentar blev minimeret af moderatoren på webstedet
Tak for koden!!

Hvad angår jokertegn, er en vej rundt ved at bruge INSTR

Du kan erstatte [ If rng = pValue Then ] med [ InStr(1, rng.Value, pValue) Then ] og hvis du ikke ønsker, at det skal skelne mellem store og små bogstaver, så brug [ InStr(1, rng.Value, pValue, vbTextCompare) Så ]
Denne kommentar blev minimeret af moderatoren på webstedet
Tak for VBA-koden ovenfor. Kan du fortælle mig, hvordan man får resultaterne til at komme ind på en ny linje i cellen, dvs. Alt-Enter 300 400 1000 1300
Denne kommentar blev minimeret af moderatoren på webstedet
Tak fordi du deler ovenstående kode. Jeg har brugt dette i flere måneder nu, men i dag ser det ikke ud til at virke. Jeg får tomme celler i stedet for den sædvanlige fejl, når der er data, der skal returneres. nogen tanker?
Denne kommentar blev minimeret af moderatoren på webstedet
Fantastisk arbejde.. Fik præcis hvad jeg vil have!!! Elsker det !!
Denne kommentar blev minimeret af moderatoren på webstedet
Hej, jeg er virkelig imponeret over arbejdet, og det er så nemt at oprette en for at bruge denne funktion. men jeg har brug for yderligere støtte. Min ? er, hvordan kan jeg vælge et tal fra en celle med flere celler i mit vlookup-array. dvs. Hvis celle A1 = 100, A2 = 350, A3 = 69 C1 = 100; 1222; 12133 C2 = 69; 222 D1 = Æble D2 = banan Så hvordan kan jeg vælge 100 fra min tabelmatrix kolonne C for at udlede korrespondent D1 = æble Bemærk venligst, at jeg har 7-cifrede tal i min opslagsværdi og tabelarray, som er adskilt af et ";". Jeg vil virkelig sætte pris på, hvis du kan løse dette og hjælpe mig med at spare en masse tid.
Denne kommentar blev minimeret af moderatoren på webstedet
Tak for VBA-koden. Jeg fik præcis, hvad jeg vil have! Jeg ændrede kun koden "rng.Offset(0, pIndex - 1)" til "rng.Offset(0, pIndex - 2) ". Så er MYVLOOKUP i stand til at søge fra højre til venstre.
Denne kommentar blev minimeret af moderatoren på webstedet
Det er præcis, hvad jeg ledte efter og ikke tænkte på bare at lave min egen UDF. Det vil dog ikke fungere nøjagtigt som VLOOKUP. Hvis den streng, du leder efter, ikke kun er i den første kolonne, kan den give dig data, der ligger uden for det oprindelige område. Navn Nummer Andet navn Kolonne ikke inden for rækkevidde Jay 1 Jay 1 Jay 2 Jay 2 Chris 3 Chris 3 Jorge 4 Jorge 4 Jay 5 Jay 5 Jorge 6 Jorge 6 Hvis ovenstående tabel var cellerne A1:D7, hvis du kun bestod A1:C7 "MYVLOOKUP"-funktionen returnerer 1 1 2 2 5 5, når du forventer, at den returnerer 1 2 5. Ændringerne nedenfor løser problemet: Funktion MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Opdatering 20150310' Opdateret 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ærdi = pValue Derefter 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
Denne kommentar blev minimeret af moderatoren på webstedet
Dette fungerer godt, men jeg har brug for hjælp med kommandoen til at fjerne dubletter fra resultaterne. Men seriøst, flot arbejde.
Denne kommentar blev minimeret af moderatoren på webstedet
Dette fungerer godt, men jeg har stadig brug for hjælp til kommandofunktionen til at fjerne dubletter fra resultaterne.
Denne kommentar blev minimeret af moderatoren på webstedet
Giv mig besked om opfølgende kommentarer
Denne kommentar blev minimeret af moderatoren på webstedet
Returner intet! efter påføring af MYLOOKUP giver intet resultat, men tomt.
Denne kommentar blev minimeret af moderatoren på webstedet
Hej, det fungerer godt. Det, jeg gerne vil gøre, er at tilpasse koden for at adskille dine værdiresultater med "///" eller en hvilken som helst anden markør (af tekniske årsager ønsker jeg ikke kun en enkelt tegnseparator). Jeg har også bemærket, at denne formel ikke virker med et wild card. Jeg ved, at jeg spørger for meget, men det gør det ikke, da vlookup kan fungere, når jeg søger efter =myvlookup("*"&E6&"*",$A$2:$C$15,2), hvilket det ville gøre/kunne gøre. Nogen hjælp?
Denne kommentar blev minimeret af moderatoren på webstedet
Heads-up. Jeg fandt ud af, hvordan man får en separator i det output. Den er rudimentær. Men jeg fandt ud af det. xResult = xResult & "///" & rng.Offset(0, pIndex - 1) Den sidste og mest ønskede ting er dog at sætte den i stand til at arbejde med jokertegn i søgekriterierne. Endnu en gang tak for denne smukke og geniale løsning. Yderst hjælpsom. Vil nu bare få makroen til at køre og blive installeret i mit excel permanent, uanset hvad jeg laver, så jeg kan bruge den når jeg har brug for det. Og jokertegn! Mange tak. Wildcards er alt, der er tilbage at gøre.
Denne kommentar blev minimeret af moderatoren på webstedet
For at få en unik registrering kan du bruge nedenfor: (ændret ved at henvise til anden brugerkode) Funktion MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long) 'Opdatering 20150310' Opdateret 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 Every varSection In Split(xResult, sDelimiter) If InStr(1, sDelimiter & sTemp & sDelimiter, sDelimiter & varSection & sDelimiter, vbTextCompare) = 0 Så sTemp = sTemp & sDelimiter & varSection End If Next varSection MYVLOOKUP = Mid(sTemp, Len(sDelimiter) Function +
Denne kommentar blev minimeret af moderatoren på webstedet
Dette fungerede perfekt, men det tog mig noget tid at få funktionen til at fungere ordentligt i mit regneark med 20 faner, 50k+ linier. Nu er det STORE spørgsmål, hvordan man tager den afgrænsede streng og derefter bruger hver post som en indeks/match (ikke gift med indeks/match, men det virker hurtigere) opslagsværdi i et andet datasæt, hvilket returnerer SUM-værdien af ​​alle returneringer i én celle . Mit scenario er, at jeg har en enkelt ordre, der har flere fakturaer. Din MYVLOOKUP-funktion fungerer fremragende til at rapportere alle fakturaer tilbage i én celle. Det, jeg vil gøre nu, er at tage hver sammenkædet retur med den rapporterede celle, snurre gennem det array og summere betalingsbeløbene for hver faktura tilbage i formelcellen. Jeg sætter pris på al hjælp, du kan tilbyde med dette, og tak for MYVLOOKUP-funktionen!
Denne kommentar blev minimeret af moderatoren på webstedet
Lige meget hvad jeg gør, får jeg altid #værdi! returneres i stedet for et resultat. vlookup fungerer fint, så dataene virker. Har allerede fulgt processen med at aktivere makroer. Jeg kombinerede endda alt til et enkelt ark. Nogle ideer??
Denne kommentar blev minimeret af moderatoren på webstedet
Fantastisk makro, nyttig. Men har brug for at vide, kan det ændres til at kontrollere 2 kriterier, og er der nogen, der alligevel kan få jokertegn til at fungere på det. Nogen hjælp?
Denne kommentar blev minimeret af moderatoren på webstedet
Er der en måde at ændre resultatet på, så i stedet for at vise 1000 1000 -1000 ville det for eksempel vise 1,000/1,000/(1,000)?
Denne kommentar blev minimeret af moderatoren på webstedet
Fantastisk funktion, men at stykke gennem 100,000 poster viser lidt meget for min stakkels bærbare computer, bliver nødt til at lade den køre natten over!
Denne kommentar blev minimeret af moderatoren på webstedet
Det er fantastisk, tak!
Der er endnu ingen kommentarer her
Load More
Efterlad dine kommentarer
Sender som gæst
×
Bedøm dette indlæg:
0   Tegn
Foreslåede steder