Få flere oppslagsverdier i en enkelt celle (uten og uten gjentagelse)

Kan vi slå opp og returnere flere verdier i en celle i Excel (atskilt med komma eller mellomrom)?

Jeg har blitt stilt dette spørsmålet flere ganger av mange av mine kolleger og lesere.

Excel har noen fantastiske oppslagsformler, for eksempel VLOOKUP, INDEX/MATCH (og nå XLOOKUP), men ingen av disse tilbyr en måte å returnere flere matchende verdier. Alle disse fungerer ved å identifisere den første kampen og returnere den.

Så jeg gjorde litt VBA -koding for å komme opp med en egendefinert funksjon (også kalt en brukerdefinert funksjon) i Excel.

Oppdater: Etter at Excel ga ut dynamiske matriser og fantastiske funksjoner som UNIKT og TEKSTJOIN, er det nå mulig å bruke en enkel formel og returnere alle matchende verdier i en celle (dekket i denne opplæringen).

I denne opplæringen vil jeg vise deg hvordan du gjør dette (hvis du bruker den nyeste versjonen av Excel - Microsoft 365 med alle de nye funksjonene), samt en måte å gjøre dette på hvis du bruker eldre versjoner ( bruker VBA).

Så la oss komme i gang!

Slå opp og returner flere verdier i én celle (ved hjelp av formel)

Hvis du bruker Excel 2016 eller tidligere versjoner, går du til neste avsnitt hvor jeg viser hvordan du gjør dette ved hjelp av VBA.

Med Microsoft 365 -abonnement har Excel nå mye kraftigere funksjoner og funksjoner som ikke er der i tidligere versjoner (for eksempel XLOOKUP, dynamiske matriser, UNIQUE/FILTER -funksjoner, etc.)

Så hvis du bruker Microsoft 365 (tidligere kjent som Office 365), kan du bruke metodene som er dekket i denne delen, for å slå opp og returnere flere verdier i en enkelt celle i Excel.

Og som du vil se, er det en veldig enkel formel.

Nedenfor har jeg et datasett der jeg har navnene på personene i kolonne A og opplæringen de har tatt i kolonne B.

Klikk her for å laste ned eksempelfilen og følge med

For hver person vil jeg finne ut hvilken trening de har fullført. I kolonne D har jeg listen over unike navn (fra kolonne A), og jeg vil raskt slå opp og trekke ut all treningen som hver person har gjort og få disse i et enkelt sett (atskilt med komma).

Nedenfor er formelen som gjør dette:

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

Etter at du har angitt formelen i celle E2, kopierer du den for alle cellene der du vil ha resultatene.

Hvordan fungerer denne formelen?

La meg dekonstruere denne formelen og forklare hver del i hvordan den kommer sammen gir oss resultatet.

Den logiske testen i IF -formelen (D2 = $ A $ 2: $ A $ 20) kontrollerer om navnecellen D2 er den samme som i området A2: A20.

Den går gjennom hver celle i området A2: A20, og sjekker om navnet er det samme i celle D2 eller ikke. hvis det er samme navn, returnerer det TRUE, ellers returnerer det FALSE.

Så denne delen av formelen vil gi deg en matrise som vist nedenfor:

{SANN; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}

Siden vi bare ønsker å få opplæringen for Bob (verdien i celle D2), må vi få all tilsvarende trening for cellene som returnerer SANN i ovennevnte matrise.

Dette gjøres enkelt ved å spesifisere [value_if_true] en del av IF -formelen som området som har treningen. Dette sikrer at hvis navnet i celle D2 samsvarer med navnet i området A2: A20, vil IF -formelen returnere all treningen personen har tatt.

Og uansett hvor matrisen returnerer en FALSE, har vi spesifisert [value_if_false] -verdien som “” (blank), så den returnerer en blank.

IF -delen av formelen returnerer matrisen som vist nedenfor:

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

Hvor det har navnene på treningen Bob har tatt og blanks hvor navnet ikke var Bob.

Alt vi trenger å gjøre er å kombinere dette treningsnavnet (atskilt med komma) og returnere det i en celle.

Og det kan enkelt gjøres ved hjelp av den nye TEXTJOIN-formelen (tilgjengelig i Excel2021-2022 og Excel i Microsoft 365)

TEXTJOIN -formelen tar tre argumenter:

• skilletegn - som er "," i vårt eksempel, ettersom jeg vil at treningen skal skilles med et komma og et mellomrom
• TRUE - som forteller TEXTJOIN -formelen å ignorere tomme celler og bare kombinere de som ikke er tomme
• If -formelen som returnerer teksten som må kombineres

Hvis du bruker Excel i Microsoft 365 som allerede har dynamiske matriser, kan du bare skrive inn formelen ovenfor og trykke enter. Og hvis du bruker Excel2021-2022, må du skrive inn formelen, holde inne Ctrl og Shift-tasten og deretter trykke Enter

Klikk her for å laste ned eksempelfilen og følge med

Få flere oppslagsverdier i en enkelt celle (uten gjentagelse)

Siden den UNIKE formelen bare er tilgjengelig fra Excel i Microsoft 365, kan du ikke bruke denne metoden i Excel2021-2022

Hvis det er repetisjoner i datasettet ditt, som vist nedenfor, må du endre formelen litt slik at du bare får en liste over unike verdier i en enkelt celle.

I datasettet ovenfor har noen tatt trening flere ganger. For eksempel har Bob og Stan tatt Excel -opplæringen to ganger, og Betty har tatt MS Word -opplæring to ganger. Men i vårt resultat ønsker vi ikke å ha et treningsnavn gjentatt.

Du kan bruke formelen nedenfor for å gjøre dette:

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

Formelen ovenfor fungerer på samme måte, med en liten endring. vi har brukt IF -formelen i UNIQUE -funksjonen slik at hvis det er repetisjoner i if -formelresultatet, ville UNIQUE -funksjonen fjerne den.

Klikk her for å laste ned eksempelfilen

Slå opp og returner flere verdier i én celle (ved hjelp av VBA)

Hvis du bruker Excel 2016 eller tidligere versjoner, har du ikke tilgang til TEXTJOIN -formelen. Så den beste måten å slå opp og få flere matchende verdier i en enkelt celle er ved å bruke en tilpasset formel som du kan opprette ved hjelp av VBA.

For å få flere oppslagsverdier i en enkelt celle, må vi opprette en funksjon i VBA (lik VLOOKUP -funksjonen) som sjekker hver celle i en kolonne, og hvis oppslagsverdien blir funnet, legger den til resultatet.

Her er VBA -koden som kan gjøre dette:

'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 (Result, Len (Result) - 1) Sluttfunksjon

Hvor skal jeg sette denne koden?

1. Åpne en arbeidsbok og klikk Alt + F11 (dette åpner VBA Editor -vinduet).
2. I dette VBA Editor -vinduet, til venstre, er det en prosjektutforsker (der alle arbeidsbøkene og regnearkene er oppført). Høyreklikk på et objekt i arbeidsboken der du vil at denne koden skal fungere, og gå til Sett inn -> Modul.
3. I modulvinduet (som vises til høyre), kopier og lim inn koden ovenfor.
4. Nå er du klar. Gå til hvilken som helst celle i arbeidsboken og skriv = SingleCellExtract og koble til de nødvendige inndataargumentene (dvs. LookupValue, LookupRange, ColumnNumber).

Hvordan fungerer denne formelen?

Denne funksjonen fungerer på samme måte som VLOOKUP -funksjonen.

Det tar 3 argumenter som innspill:

1. Oppslagverdi - En streng som vi trenger for å slå opp i en rekke celler.
2. LookupRange - En rekke celler hvor vi trenger å hente dataene ($ B3: $ C18 i dette tilfellet).
3. KolonneNummer - Det er kolonnenummeret i tabellen/matrisen som matchende verdi skal returneres fra (2 i dette tilfellet).

Når du bruker denne formelen, sjekker den hver celle i kolonnen lengst til venstre i oppslagsområdet og når den finner en treff, legger den til resultatet i cellen du har brukt formelen i.

Huske: Lagre arbeidsboken som en makroaktivert arbeidsbok (.xlsm eller .xls) for å bruke denne formelen igjen. Denne funksjonen vil også være tilgjengelig bare i denne arbeidsboken og ikke i alle arbeidsbøkene.

Klikk her for å laste ned eksempelfilen

Lær hvordan du automatiserer kjedelige repeterende oppgaver med VBA i Excel. Bli med Excel VBA -kurs

Få flere oppslagsverdier i en enkelt celle (uten gjentagelse)

Det er en mulighet for at du kan ha gjentagelser i dataene.

Hvis du bruker koden som er brukt ovenfor, vil den også gi deg repetisjoner i resultatet.

Hvis du vil få resultatet der det ikke er gjentakelser, må du endre koden litt.

Her er VBA -koden som vil gi deg flere oppslagsverdier i en enkelt celle uten gjentagelser.

'Code by Sumit Bansal (https://trumpexcel.com) Function MultipleLookupNoRept (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i Som 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å deretter til Hopp over slutten hvis slutten hvis neste J resultat = resultat & "" & LookupRange.Cells (i, kolonnenummer) & "," Hopp over: slutt hvis neste i MultipleLookupNoRept = venstre (resultat, Len (resultat) - 1) slutt Funksjon

Når du har plassert denne koden i VB Editor (som vist ovenfor i opplæringen), vil du kunne bruke MultipleLookupNoRept funksjon.

Her er et øyeblikksbilde av resultatet du får med dette MultipleLookupNoRept funksjon.

Klikk her for å laste ned eksempelfilen

I denne opplæringen dekket jeg hvordan jeg bruker formler og VBA i Excel for å finne og returnere flere oppslagsverdier i en celle i Excel.

Selv om det enkelt kan gjøres med en enkel formel hvis du bruker Excel i Microsoft 365 -abonnement, hvis du bruker tidligere versjoner og ikke har tilgang til funksjoner som TEXTJOIN, kan du fortsatt gjøre dette ved hjelp av VBA ved å opprette din egen tilpasset funksjon.