Se video - Slå opp den andre, den tredje eller den niende matchende verdien
Når det gjelder å slå opp data i Excel, er det to fantastiske funksjoner som jeg ofte bruker - VLOOKUP og INDEX (for det meste i forbindelse med MATCH -funksjonen).
Imidlertid er disse formlene designet for å finne bare den første forekomsten av oppslagsverdien.
Men hva om du vil slå opp den andre, tredje, fjerde eller Nth-verdien.
Vel, det kan gjøres med litt ekstra arbeid.
I denne opplæringen vil jeg vise deg forskjellige måter (med eksempler) på hvordan du kan slå opp den andre eller den niende verdien i Excel.
Slå opp andre, tredje eller nte verdi i Excel
I denne opplæringen vil jeg dekke to måter å slå opp den andre eller den niende verdien i Excel:
- Ved hjelp av en hjelpesøyle.
- Bruke matriseformler.
La oss komme i gang og dykke rett inn.
Bruke hjelperkolonne
Anta at du er en opplæringskoordinator i en organisasjon og har et datasett som vist nedenfor. Du vil liste all opplæringen foran en ansattes navn.
I datasettet ovenfor har de ansatte fått opplæring i forskjellige Microsoft Office -verktøy (Excel, PowerPoint og Word).
Nå kan du bruke VLOOKUP -funksjonen eller INDEX/MATCH -kombinasjonen for å finne opplæringen en ansatt har fullført. Imidlertid vil den bare returnere den første matchende forekomsten.
For eksempel, for John, har han tatt alle de tre treningene, men når jeg ser opp navnet hans med VLOOKUP eller INDEX/MATCH, vil det alltid returnere 'Excel', som er den første treningen for navnet hans på listen .
For å få dette gjort kan vi bruke en hjelperkolonne og lage unike oppslagsverdier i den.
Her er trinnene:
- Sett inn en kolonne foran kolonnen som viser opplæringen.
- Skriv inn følgende formel i celle B2:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- I celle F2 skriver du inn følgende formel og kopierer inn for alle de andre cellene:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Formelen ovenfor vil returnere opplæringen for hver ansatt i den rekkefølgen den vises på listen. Hvis det ikke er oppført noen opplæring for en ansatt, returnerer det et tomt.
Hvordan fungerer denne formelen?
COUNTIF -formelen i hjelperkolonnen gjør hver ansattes navn unikt ved å legge et tall til det. For eksempel blir den første forekomsten av John John1, den andre forekomsten blir John2 og så videre.
VLOOKUP -formelen bruker nå disse unike ansattnavnene for å finne den matchende opplæringen.
Vær oppmerksom på at $ E2 & COLUMNS ($ F $ 1: F1) er oppslagsverdien i formelen. Dette vil legge til et tall i ansattnavnet basert på kolonnenummeret. For eksempel, når denne formelen brukes i celle F2, blir oppslagsverdien "John1". I celle G2 blir det “John2” og så videre.
Bruke Array Formula
Hvis du ikke vil endre det opprinnelige datasettet ved å legge til hjelperkolonner, kan du også bruke en matriseformel til å slå opp den andre, tredje eller nte -verdien.
Anta at du har det samme datasettet som vist nedenfor:
Her er formelen som returnerer den riktige oppslagsverdien:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1) : E1))), "")
Kopier denne formelen og lim den inn i celle E2.
Vær oppmerksom på at dette er en matriseformel, og du må bruke Ctrl + Shift + Enter (hold Ctrl- og Shift -tastene og trykk Enter -tasten), i stedet for å trykke Enter.
Klikk her for å laste ned eksempelfilen.
Hvordan fungerer denne formelen?
La oss dele denne formelen i deler og se hvordan den fungerer.
$ A $ 2: $ A $ 14 = $ D2
Den ovennevnte delen av formelen sammenligner hver celle i A2: A14 med verdien i D2. I dette datasettet sjekker den om en celle inneholder navnet "John" eller ikke.
Den returnerer en rekke TRUE av FALSE. Hvis cellen har navnet 'John' ville det være sant, ellers ville det være usant.
Nedenfor er matrisen du vil få i dette eksemplet:
{SANN; FALSK; FALSK; FALSK; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; FALSK; FALSK}
Vær oppmerksom på at den har SANN i 1., 7. og 111. posisjon, ettersom navnet John vises i datasettet.
IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
IF-formelen ovenfor bruker matrisen TRUE og FALSE, og erstatter TRUE med posisjonen til forekomsten i listen (gitt av ROW ($ A $ 2: $ A $ 14) -1) og FALSE med “” (blank). Følgende er den resulterende matrisen du får med denne IF -formelen:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Merk enn 1, 7 og 11 er posisjonen til forekomsten av John i listen.
LITEN (HVIS ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1))
SMALL -funksjonen velger nå det første minste, nest minste, tredje minste tallet fra denne matrisen. Vær oppmerksom på at den bruker funksjonen KOLONNER til å generere kolonnetallet. I celle E2 returnerer COLUMNS -funksjonen 1 og SMALL -funksjonen returnerer 1. I celle F2 returnerer COLUMNS -funksjonen 2 og SMALL -funksjonen returnerer 7.
INDEKS ($ B $ 2: $ B $ 14, LITEN (HVIS ($ A $ 2: $ A $ 14 = $ D2, RAD ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1) ))
INDEX -funksjonen returnerer nå verdien fra listen i kolonne B basert på posisjonen som returneres av SMALL -funksjonen. Derfor returnerer den i celle E2 ‘Excel’, som er det første elementet i B2: B14. I celle F2 returnerer den PowerPoint, som er det syvende elementet i listen.
Siden det er tilfeller hvor det bare er en eller to opplæring for noen ansatte, vil INDEX -funksjonen returnere en feil. IFERROR -funksjonen brukes til å returnere et tomt i stedet for feilen.
Vær oppmerksom på at i disse eksemplene har jeg brukt områdereferanser. I praktiske eksempler er det imidlertid fordelaktig å konvertere dataene til en Excel -tabell. Ved å konvertere til en Excel -tabell kan du bruke strukturerte referanser, noe som gjør det enklere å lage formler. En Excel -tabell kan også automatisk redegjøre for nye opplæringselementer som legges til i listen (slik at du ikke trenger å justere formlene hver gang).
Hva gjør du når du må slå opp den andre, tredje eller Nth-verdien? Jeg er sikker på at det er flere måter å gjøre dette på. Hvis du bruker noe enklere enn det som er oppført her, må du dele med oss alle i kommentarfeltet.
Klikk her for å laste ned eksempelfilen.
