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 alle matchende værdier i en celle
• Vlookup for at returnere alle matchende værdier uden dubletter i en celle

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

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

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:

  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:

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:

  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øjer > 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:

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.

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: