Få flera uppslagsvärden i en enda cell (utan och utan upprepning)

Kan vi slå upp och returnera flera värden i en cell i Excel (åtskilda med komma eller mellanslag)?

Jag har fått denna fråga flera gånger av många av mina kollegor och läsare.

Excel har några fantastiska uppslagsformler, till exempel VLOOKUP, INDEX/MATCH (och nu XLOOKUP), men ingen av dessa erbjuder ett sätt att returnera flera matchande värden. Alla dessa fungerar genom att identifiera den första matchen och returnera den.

Så jag gjorde lite VBA -kodning för att komma med en anpassad funktion (även kallad en användardefinierad funktion) i Excel.

Uppdatering: Efter att Excel släppt dynamiska matriser och fantastiska funktioner som UNIKT och TEXTJOIN är det nu möjligt att använda en enkel formel och returnera alla matchande värden i en cell (täcks av denna handledning).

I den här självstudien visar jag dig hur du gör detta (om du använder den senaste versionen av Excel - Microsoft 365 med alla nya funktioner), samt ett sätt att göra detta om du använder äldre versioner ( med VBA).

Så låt oss komma igång!

Leta upp och returnera flera värden i en cell (med hjälp av formel)

Om du använder Excel 2016 eller tidigare versioner, gå till nästa avsnitt där jag visar hur du gör detta med VBA.

Med Microsoft 365 -prenumeration har din Excel nu mycket mer kraftfulla funktioner och funktioner som inte finns i tidigare versioner (till exempel XLOOKUP, dynamiska matriser, UNIKA/FILTER -funktioner, etc.)

Så om du använder Microsoft 365 (tidigare känd som Office 365) kan du använda metoderna som täcks i det här avsnittet och kan leta upp och returnera flera värden i en enda cell i Excel.

Och som du kommer att se är det en riktigt enkel formel.

Nedan har jag en datauppsättning där jag har namnen på personerna i kolumn A och den utbildning som de har tagit i kolumn B.

Klicka här för att ladda ner exempelfilen och följa med

För varje person vill jag ta reda på vilken utbildning de har gått. I kolumn D har jag listan över unika namn (från kolumn A), och jag vill snabbt leta upp och extrahera all träning som varje person har utfört och få dessa i en enda uppsättning (åtskilda med ett kommatecken).

Nedan är formeln som gör detta:

= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

När du har angett formeln i cell E2, kopiera den för alla celler där du vill ha resultaten.

Hur fungerar denna formel?

Låt mig dekonstruera denna formel och förklara varje del i hur den går ihop ger oss resultatet.

Det logiska testet i IF -formeln (D2 = $ A $ 2: $ A $ 20) kontrollerar om namncellen D2 är densamma som i intervallet A2: A20.

Den går igenom varje cell i intervallet A2: A20 och kontrollerar om namnet är detsamma i cell D2 eller inte. om det är samma namn returnerar det SANT, annars returnerar det FALSKT.

Så den här delen av formeln ger dig en array som visas nedan:

{SANT; FALSKT; FALSKT; SANT; FALSKT; FALSKT; FALSKT; FALSKT; FALSKT

Eftersom vi bara vill få träningen för Bob (värdet i cell D2), måste vi få all motsvarande utbildning för de celler som returnerar SANT i ovanstående matris.

Detta görs enkelt genom att ange [value_if_true] en del av IF -formeln som intervallet som har träningen. Detta säkerställer att om namnet i cell D2 matchar namnet i intervallet A2: A20, skulle IF -formeln returnera all träning som personen har tagit.

Och överallt där matrisen returnerar ett FALSKT, har vi angett [value_if_false] -värdet som “” (tomt), så det returnerar ett tomt.

IF -delen av formeln returnerar matrisen enligt nedan:

{"Excel"; ""; ""; "PowerPoint"; ""; "" ""; "" ""; ""; ""; "" "" "" ""; "" "" "; ””; ””; ””}

Där det har namnen på den träning Bob har tagit och tomma ämnen där namnet inte var Bob.

Allt vi behöver göra är att kombinera dessa träningsnamn (separerade med ett komma) och returnera det i en cell.

Och det kan enkelt göras med den nya TEXTJOIN-formeln (tillgänglig i Excel2021-2022 och Excel i Microsoft 365)

Formeln TEXTJOIN tar tre argument:

• avgränsaren - vilket är "," i vårt exempel, eftersom jag vill att träningen ska separeras av ett komma och ett mellanslag
• TRUE - som säger till TEXTJOIN -formeln att ignorera tomma celler och bara kombinera sådana som inte är tomma
• If -formeln som returnerar texten som måste kombineras

Om du använder Excel i Microsoft 365 som redan har dynamiska matriser kan du bara ange ovanstående formel och trycka på enter. Och om du använder Excel2021-2022 måste du ange formeln och hålla ned Ctrl och Shift-tangenten och sedan trycka på Retur

Klicka här för att ladda ner exempelfilen och följa med

Få flera uppslagsvärden i en enda cell (utan upprepning)

Eftersom UNIK formel endast är tillgänglig från Excel i Microsoft 365 kan du inte använda den här metoden i Excel2021-2022

Om det finns repetitioner i din datamängd, som visas nedan, måste du ändra formeln lite så att du bara får en lista med unika värden i en enda cell.

I ovanstående datamängd har vissa personer tagit utbildning flera gånger. Till exempel har Bob och Stan tagit Excel -utbildningen två gånger, och Betty har tagit MS Word -utbildning två gånger. Men i vårt resultat vill vi inte att ett träningsnamn ska upprepas.

Du kan använda nedanstående formel för att göra detta:

= TEXTJOIN (",", TRUE, UNIQUE (IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

Ovanstående formel fungerar på samma sätt, med en mindre ändring. vi har använt IF -formeln inom UNIK -funktionen så att om det finns repetitioner i if -formelresultatet skulle UNIK -funktionen ta bort den.

Klicka här för att ladda ner exempelfilen

Leta upp och returnera flera värden i en cell (med VBA)

Om du använder Excel 2016 eller tidigare versioner har du inte tillgång till TEXTJOIN -formeln. Så det bästa sättet att sedan slå upp och få flera matchande värden i en enda cell är med hjälp av en anpassad formel som du kan skapa med VBA.

För att få flera uppslagsvärden i en enda cell måste vi skapa en funktion i VBA (liknande VLOOKUP -funktionen) som kontrollerar varje cell i en kolumn och om uppslagsvärdet hittas lägger det till resultatet.

Här är VBA -koden som kan göra detta:

'Code by Sumit Bansal (https://trumpexcel.com) Function SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Resultat, Len (Resultat) - 1) Avsluta funktion

Var ska man lägga denna kod?

1. Öppna en arbetsbok och klicka på Alt + F11 (detta öppnar fönstret VBA Editor).
2. I detta VBA Editor -fönster till vänster finns en projektutforskare (där alla arbetsböcker och kalkylblad listas). Högerklicka på ett objekt i arbetsboken där du vill att den här koden ska fungera och gå till Infoga -> modul.
3. Kopiera och klistra in ovanstående kod i modulfönstret (som visas till höger).
4. Nu är du klar. Gå till valfri cell i arbetsboken och skriv = SingleCellExtract och anslut de nödvändiga inmatningsargumenten (dvs. LookupValue, LookupRange, ColumnNumber).

Hur fungerar denna formel?

Denna funktion fungerar på samma sätt som VLOOKUP -funktionen.

Det tar 3 argument som ingångar:

1. Sökvärde - En sträng som vi behöver leta upp i ett antal celler.
2. LookupRange - En rad celler varifrån vi behöver hämta data ($ B3: $ C18 i det här fallet).
3. Kolumnummer - Det är kolumnnumret i tabellen/matrisen från vilket matchningsvärdet ska returneras (2 i detta fall).

När du använder den här formeln kontrollerar den varje cell i kolumnen längst till vänster i uppslagningsområdet och när den hittar en matchning läggs den till resultatet i cellen där du har använt formeln.

Kom ihåg: Spara arbetsboken som en makroaktiverad arbetsbok (.xlsm eller .xls) för att återanvända denna formel igen. Denna funktion skulle också vara tillgänglig endast i denna arbetsbok och inte i alla arbetsböcker.

Klicka här för att ladda ner exempelfilen

Lär dig hur du automatiserar tråkiga repetitiva uppgifter med VBA i Excel. Gå med i Excel VBA -kurs

Få flera uppslagsvärden i en enda cell (utan upprepning)

Det finns en möjlighet att du kan upprepa data.

Om du använder koden som används ovan kommer det att ge dig repetitioner i resultatet också.

Om du vill få resultatet där det inte finns några repetitioner måste du ändra koden lite.

Här är VBA -koden som ger dig flera uppslagsvärden i en enda cell utan några repetitioner.

'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 If LookupRange.Cells (J, 1) = LookupRevue.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Gå sedan till Hoppa över End Om End If Next J Resultat = Resultat & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Fungera

När du har placerat den här koden i VB Editor (som visas ovan i självstudien) kommer du att kunna använda MultipleLookupNoRept fungera.

Här är en ögonblicksbild av resultatet du kommer att få med detta MultipleLookupNoRept fungera.

Klicka här för att ladda ner exempelfilen

I denna handledning täckte jag hur jag använder formler och VBA i Excel för att hitta och returnera flera uppslagsvärden i en cell i Excel.

Även om det enkelt kan göras med en enkel formel om du använder Excel i Microsoft 365 -prenumeration, om du använder tidigare versioner och inte har tillgång till funktioner som TEXTJOIN, kan du fortfarande göra detta med VBA genom att skapa din egen anpassad funktion.