10 VLOOKUP eksempler for nybegynnere og avanserte brukere

VLOOKUP-funksjonen er målestokken.

Hvis du vet hvordan du bruker funksjonen VLOOKUP, vet du noe om Excel.

Hvis du ikke gjør det, bør du ikke oppgi Excel som en av styrkene dine på CV-en.

Jeg gjorde et gruppeintervju, og når en kandidat nevnte Excel som sitt ekspertiseområde, var det første som ble spurt – du skjønner – VLOOKUP-funksjonen.

Nå som vi vet viktigheten av denne Excel-funksjonen, er det fornuftig å forstå den fullt ut slik at vi stolt kan si - "Jeg vet en ting eller to i Excel."

Dette kommer til å bli en enorm VLOOKUP-opplæring (etter mine standarder).

Jeg skal dekke alt om det og deretter vise deg et nyttig og praktisk eksempel på VLOOKUP.

Når skal man bruke VLOOKUP-funksjonen i Excel?

VLOOKUP-funksjonen fungerer best når du leter etter samsvarende datapunkter i en kolonne, og når et samsvarende datapunkt er funnet, tar du kolonnen til høyre for raden og henter verdien fra det angitte antallet celler.

La oss ta et enkelt eksempel her for å forstå når du skal bruke Vlookup i Excel.

Husk at alle pleide å febrilsk lete etter navn og poeng når testresultatene kom ut og ble sittende fast på oppslagstavler (det var i alle fall det som skjedde mye da jeg gikk på skolen).

Slik fungerer det:

• Du går til oppslagstavlen og begynner å lete etter navnet ditt eller registreringsnummeret ditt (beveg fingeren opp og ned på listen).
• Når du har funnet navnet ditt, vil du flytte øynene til høyre for navnet/registreringsnummeret for å se poengsummen din.

Det er akkurat det Excel VLOOKUP-funksjonen gjør for deg (bruk gjerne dette eksempelet i ditt neste intervju).

VLOOKUP-funksjonen ser etter en spesifisert verdi i en kolonne (i eksemplet ovenfor er det navnet ditt), og når den finner et spesifisert samsvar, returnerer den en verdi (tokenet du får) i samme rad.

syntaks

=VLOOKUP(oppslagsverdi, tabellmatrise, kolonneindeksnummer, [områdeoppslag])

Inndataparametere

• oppslagsverdi –Dette er oppslagsverdien du prøver å finne i kolonnen lengst til venstre i tabellen.Det kan være en verdi, en cellereferanse eller en tekststreng.I eksempelet på resultatlisten vil dette være navnet ditt.
• table_array –Dette er utvalget av tabeller der du leter etter verdier.Dette kan være en referanse til et celleområde eller et navngitt område.I eksempelet på poengtabellen vil dette være hele tabellen som inneholder alles poengsum for hvert emne
• col_index –Dette er kolonneindeksnummeret du ønsker å få samsvarende verdier fra.I brøktabelleksemplet, hvis du vil ha en brøk for matematikk (dette er den første kolonnen i en tabell som inneholder brøker), kan du se på kolonne 1.Hvis du vil ha en fysisk poengsum, kan du se på kolonne 1 og 2.
• [range_lookup] -Her kan du spesifisere om du vil ha en eksakt match eller en omtrentlig match.Hvis utelatt, er standard til TRUE - omtrentlig samsvar(se tilleggsmerknader nedenfor).

Ytterligere merknader (kjedelige, men viktige å vite)

• Treff kan være eksakte (FALSE eller 0 i range_lookup) eller omtrentlige (TRUE eller 1).
• I et omtrentlig oppslag, sørg for at listen er sortert i stigende rekkefølge (topp til bunn), ellers kan resultatene bli unøyaktige.
• Når range_lookup er TRUE (omtrentlig oppslag) og dataene er sortert i stigende rekkefølge:
  • Hvis VLOOKUP-funksjonen ikke finner verdien, returnerer den den største verdien mindre enn lookup_value.
  • Returnerer en #N/A feil hvis oppslagsverdi er mindre enn minimumsverdien.
  • Hvis lookup_value er tekst, kan jokertegn brukes (se eksempel nedenfor).

Nå, forhåpentligvis har du en grunnleggende forståelse av hva VLOOKUP-funksjonen kan gjøre, la oss skrelle løken og se noen praktiske eksempler på VLOOKUP-funksjonen.

10 Excel VLOOKUP eksempler (grunnleggende og avanserte)

Her er 10 nyttige eksempler på bruk av Excel Vlookup for å vise deg hvordan du bruker det i ditt daglige arbeid.

Eksempel 1 - Finn Brads matematikkpoeng

I VLOOKUP-eksemplet nedenfor har jeg en liste med elevnavn i kolonnen lengst til venstre og karakterer for forskjellige fag i kolonne B til E.

La oss nå begynne å jobbe og bruke VLOOKUP-funksjonen for å gjøre det den gjør best.Fra dataene ovenfor trenger jeg å vite hva Brad scoret i matematikk.

Fra dataene ovenfor trenger jeg å vite hva Brad scoret i matematikk.

Her er VLOOKUP-formelen som returnerer Brads mattepoengsum:

=OPSLAKK("Brad",$A$3:$E$10,2,0)

Formelen ovenfor har fire parametere:

• "Brad": – Dette er oppslagsverdien.
• $A$3:$E$10 – Dette er celleutvalget vi ser på.Husk at Excel ser etter oppslagsverdier i kolonnen lengst til venstre.I dette eksemplet ser den etter navnet Brad i A3:A10, som er kolonnen lengst til venstre i den angitte matrisen.
• 2 – Når funksjonen finner Brads navn, går den til den andre kolonnen i matrisen og returnerer verdien i samme rad som Brad.En verdi på 2 her betyr at vi ser etter poengsum fra den andre kolonnen i den angitte matrisen.
• 0 – Dette forteller VLOOKUP-funksjonen å kun se etter eksakte treff.

Slik fungerer VLOOKUP-formelen i eksemplet ovenfor.

Først ser den etter verdien Brad i kolonnen lengst til venstre.Den ser opp verdien i celle A6 fra topp til bunn.

Når den finner verdien, beveger den seg til høyre i den andre kolonnen og tar tak i verdien i den.

Du kan bruke den samme formelkonstruksjonen for å få noens poengsum i ethvert fag.

For å finne Marias poengsum i kjemi, bruk for eksempel følgende VLOOKUP-formel:

=OPSLAKK("Maria",$A$3:$E$10,4,0)

I eksemplet ovenfor er oppslagsverdien (studentnavn) angitt i doble anførselstegn.Du kan også bruke cellereferanser som inneholder oppslagsverdier.

Fordelen med å bruke cellereferanser er at det gjør formelen dynamisk.

For eksempel, hvis du har en celle med en elevs navn og du får karakterer for matematikk, når du endrer elevens navn, oppdateres resultatene automatisk (som vist nedenfor):

Hvis du angir en oppslagsverdi som ikke finnes i kolonnen lengst til venstre, returneres en #N/A-feil.

Eksempel 2 - Toveis oppslag

I eksempel 1 ovenfor har vi hardkodet kolonneverdiene.Derfor vil formelen alltid returnere Maths poengsum fordi vi bruker 2 som kolonneindeksnummer.

Men hva om du vil gjøre både VLOOKUP-verdien og kolonneindeksnummeret dynamiske.For eksempel, som vist nedenfor, kan du endre elevnavnet eller emnenavnet og VLOOKUP-formelen vil få riktig poengsum.Her er et eksempel på en toveis VLOOKUP-formel.

Her er et eksempel på en toveis VLOOKUP-funksjon.

For å lage denne toveisoppslagsformelen må du også gjøre kolonnen dynamisk.Så når brukeren endrer emne, velger formelen automatisk riktig kolonne (2 for matematikk, 3 for fysikk osv..).

For å gjøre dette, må du bruke MATCH-funksjonen som en kolonneparameter.

Her er VLOOKUP-formelen som vil gjøre dette:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Formelen ovenfor bruker MATCH(H3,$A$2:$E$2,0) som kolonnenummer.MATCH-funksjonen tar emnenavnet som oppslagsverdi (i H3) og returnerer posisjonen i A2:E2.Så hvis du bruker Math, vil den returnere 2 fordi Math finnes i B2 (som er den andre cellen i det angitte matriseområdet).

Eksempel 3 - Bruk av rullegardin som oppslagsverdi

I eksemplet ovenfor må vi legge inn dataene manuelt.Dette kan være tidkrevende og utsatt for feil, spesielt hvis du har mange oppslagsverdier.

I dette tilfellet vil en god idé være å lage en rullegardinliste med oppslagsverdier (i dette tilfellet kan det være elevnavn og emne) og ganske enkelt velge fra listen.

Avhengig av utvalget vil formelen automatisk oppdatere resultatet.

Som vist under:

Dette er en flott dashbordkomponent fordi du kan ha et stort datasett med hundrevis av elever i backend, men sluttbrukere (f.eks. lærere) kan ganske enkelt slippe ned fra .

Hvordan gjør jeg dette:

VLOOKUP-formelen brukt i dette eksemplet er den samme som i eksempel 2.

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

Oppslagsverdier er konvertert til nedtrekkslister.

Her er trinnene for å lage en rullegardinliste:

• Velg cellen som trenger en rullegardinliste.I dette eksemplet, i G4, trenger vi elevnavnet.
• Gå til Data -> Dataverktøy -> Datavalidering.
• På fanen Innstillinger i dialogboksen Datavalidering velger du Liste fra rullegardinlisten Tillat.
• I Kilde velger du $A$3:$A$10
• Klikk OK.

Du vil nå se rullegardinlisten i celle G4.På samme måte kan du lage en i H3 for et tema.

Eksempel 4 - Treveis oppslag

Hva er et treveis oppslag?

I eksempel 2 brukte vi en oppslagstabell som inneholder poengsum for elever i ulike fag.Dette er et eksempel på et toveis oppslag fordi vi bruker to variabler for å få skårene (elevens navn og emnenavn).

Nå, la oss si om et år, skal en student ta tre forskjellige nivåer av eksamener, enhetseksamener, midtveiseksamener og avsluttende eksamener (dette er eksamen fra studenttiden min).

Et treveis oppslag vil kunne få en student poengsum for et spesifisert emne fra et spesifisert eksamensnivå.

Som vist under:

I eksemplet ovenfor kan OPSLAKK-funksjonen slå opp og returnere poengsummene til de spesifiserte studentene i de spesifiserte fagene i tre forskjellige tabeller (enhetstester, midtterminer og finaler).

Her er formelen som brukes i celle H4:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

Denne formelen bruker funksjonen VELG for å sikre at riktig tabell refereres.La oss analysere VELG-delen av formelen:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

Det første argumentet til formelen er IF(H2=”Enhetstest”,1,IF(H2=”Midterm”,2,3)), som undersøker celle H2 og ser det refererte testnivået.Hvis det er en enhetstest, returneres $A$3:$E$7, som inneholder enhetstestresultatet.Returnerer $A$11:$E$15 hvis det er en midtveiseksamen, ellers returnerer $A$19:$E$23.

Ved å gjøre dette blir VLOOKUP-tabellmatrisen dynamisk, noe som gjør den til et treveis oppslag.

Eksempel 5 - Hent den siste verdien fra en liste

Du kan lage en VLOOKUP-formel for å få den siste verdien i en liste.

Det største positive tallet du kan bruke i Excel er 9.99999999999999E + 307 . Dette betyr også at det største oppslagsnummeret i VLOOKUP-nummeret er det samme.

Jeg tror aldri du vil trenge noen beregninger som involverer så store tall.Det er akkurat dette vi kan bruke for å få det siste tallet på listen.

Anta at du har et datasett som følgende(i A1:A14), og du ønsker å få det siste tallet i listen.

Her er formelen du kan bruke:

=SØK OPP(9.99999999999999E+307,$A$1:$A$14,TRUE)

Merk at formelen ovenfor bruker en omtrentlig samsvarende VLOOKUP  (Legg merke til TRUE på slutten av formelen, ikke FALSE eller 0).Vær også oppmerksom på at denne VLOOKUP-formelen fungerer uten å sortere listen.

Her er hvordan den omtrentlige VLOOKUP-funksjonen fungerer.Den skanner kolonnen lengst til venstre fra topp til bunn.

• Denne verdien returneres hvis et eksakt samsvar blir funnet.
• Hvis den finner en verdi høyere enn oppslagsverdien, returnerer den verdien i cellen over den.
• Hvis oppslagsverdien er større enn alle verdiene i listen, returneres den siste verdien.

I eksemplet ovenfor er det tredje tilfellet på jobb.

på grunn av9.99999999999999E + 307er det største tallet som kan brukes i Excel, så når det brukes som oppslagsverdi, returnerer det det siste tallet i listen.

På samme måte kan du også bruke den til å returnere det siste tekstelementet i listen.Her er formelen som kan gjøre det:

=SØKOPPSLAG("zzz",$A$1:$A$8,1, TRUE )

Følg samme logikk.Excel ser på alle navn, og siden zzz anses som større enn et hvilket som helst navn/tekst som begynner med en bokstav før zzz, vil den returnere det siste elementet i listen.

Eksempel 6 - Delvis oppslag ved bruk av jokertegn og VLOOKUP

Excel-jokertegn er nyttige i mange situasjoner.

Det er denne magiske trylledrikken som gir oppskriften din superkrefter.

Et delvis oppslag kreves når du skal slå opp en verdi i en liste og det ikke er noe eksakt samsvar.

Anta for eksempel at du har et datasett som det nedenfor, og du vil finne selskapets ABC i en liste, men listen har ABC Ltd i stedet for ABC.

Du kan ikke bruke ABC som oppslagsverdi fordi det ikke er noe eksakt samsvar i kolonne A.Omtrentlig treff kan også føre til feil resultater, og listen må sorteres i stigende rekkefølge.

Du kan imidlertid bruke jokertegn i VLOOKUP-funksjonen for å få treff.

Skriv inn følgende formel i celle D2 og dra den til andre celler:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

Hvordan fungerer denne formelen?

I formelen ovenfor, i stedet for å bruke oppslagsverdien som den er, er den omgitt av jokertegn (*) - "*"&C2&"*"

Stjerner er jokertegn i Excel som kan representere et hvilket som helst antall tegn.

Bruk stjerner rundt oppslagsverdien for å fortelle Excel at den trenger å finne tekst som inneholder ordet i C2.Den kan ha et hvilket som helst antall tegn før eller etter teksten i C2.

For eksempel inneholder celle C2 ABC, så VLOOKUP-funksjonen ser på navnene i A2:A8 og søker etter ABC.Den finner et samsvar i celle A2 fordi den inneholder ABC fra ABC Ltd. Det spiller ingen rolle om det er noen tegn til venstre eller høyre for ABC.Det vil bli betraktet som et samsvar til det er ABC i tekststrengen.

Merk: VLOOKUP-funksjonen returnerer alltid den første samsvarende verdien og stopper ytterligere oppslag.Så hvis det er ABC på listen Ltdog ABC Corporation, vil den returnere den første og ignorere resten.

Eksempel 7 - VLOOKUP returnerer feil til tross for samsvar med oppslagsverdi

Det vil gjøre deg gal når du ser at det er en samsvarende oppslagsverdi og VLOOKUP-funksjonen returnerer en feil.

For eksempel, i tilfellet nedenfor, er det et samsvar (Matt), men funksjonen VLOOKUP returnerer fortsatt en feil.

Nå mens vi kan se at det er en kamp, ​​er det vi ikke kan se med det blotte øye at det kan være ledende eller etterfølgende mellomrom.Hvis du har disse ekstra mellomrommene før, etter eller mellom oppslagsverdien, er det ikke et eksakt samsvar.

Dette skjer vanligvis når du importerer data fra en database eller får data fra noen andre.Disse ledende/etterfølgende plassene har en tendens til å krype inn.

Løsningen her er TRIM-funksjonen.Den fjerner eventuelle innledende eller etterfølgende mellomrom eller ekstra mellomrom mellom ord.

Her er formelen som vil gi deg det riktige resultatet.

=VLOOKUP("Matt",TRIM($A$2:$A$9),1,0)

Siden dette er en matriseformel, bruk Control+Shift+Enter i stedet for Enter.

En annen tilnærming kan være å først behandle oppslagsmatrisen med TRIM-funksjonen for å sikre at alle de ekstra mellomrommene er borte, og deretter bruke VLOOKUP-funksjonen som vanlig.

Eksempel 8 - Gjør et oppslag som skiller mellom store og små bokstaver

Som standard er ikke oppslagsverdier i VLOOKUP-funksjonen skille mellom store og små bokstaver.For eksempel, hvis oppslagsverdien din er MATT, matt eller Matt, er alt det samme for VLOOKUP-funksjonen.Den returnerer den første samsvarende verdien uavhengig av tilfelle.

Men hvis du vil ha et oppslag som skiller mellom store og små bokstaver, må du bruke EXACT-funksjonen og VLOOKUP-funksjonen.

Dette er et eksempel:

Som du kan se, har de tre cellene samme navn (i A2, A4 og A5), men med forskjellige store bokstaver.Til høyre har vi tre navn (Matt, MATT og matt) og deres score i matematikk.

VLOOKUP-funksjonen er for øyeblikket ikke utstyrt for å håndtere store og små bokstaver for oppslagsverdier.I eksemplet ovenfor returnerer den alltid 38, som er Matts poengsum i A2.

For å gjøre det skille mellom store og små bokstaver, må vi bruke en hjelpekolonne (som vist nedenfor):

For å få verdien i hjelpekolonnen, bruk =ROW()-funksjonen.Den vil bare få radnummeret i cellen.

Når du har hjelpekolonnen, her er formelen som gir et oppslagsresultat som skiller mellom store og små bokstaver.

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

La oss dele det ned og forstå hva det gjør:

• EXACT(E2,$A$2:$A$9) – Denne delen sammenligner oppslagsverdien i E2 med alle verdiene i A2:A9.Den returnerer en matrise av TRUE/FALSE, der TRUE returneres ved eksakt samsvar.I dette tilfellet vil den returnere følgende matrise: {TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) - denne delen multipliserer TRUE/FALSE-matrisen med radnummeret. Så lenge det er TRUE, vil det gi radnummeret , ellers gir den 0. I dette tilfellet returnerer den {2;0;0;0;0;0;0;0}.
• MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。I dette tilfellet vil den returnere 2 (som er det eksakte linjenummeret).
• Nå bruker vi bare dette tallet som oppslagsverdi og bruker oppslagsmatrisen som B2:C9

Merk: Siden dette er en matriseformel, bruk Control + Shift + Enter i stedet for bare å skrive.

Eksempel 9 - Bruk av VLOOKUP med flere betingelser

Excel VLOOKUP-funksjonen, i sin grunnleggende form, slår opp en oppslagsverdi og returnerer den tilsvarende verdien fra en spesifisert rad.

Men vanligvis er VLOOKUP nødvendig i Excel med flere kriterier.

Anta at du har data som inneholder elevnavn, testtyper og matematikkpoeng (vist nedenfor):

Å bruke VLOOKUP-funksjonen for å få hver elevs mattepoengsum på deres respektive testnivå kan være en utfordring.

For eksempel, hvis du prøver å bruke VLOOKUP med Matt som oppslagsverdi, vil den alltid returnere 91, poengsummen for den første forekomsten av Matt i listen.For å få Matt-poengsum for hver eksamenstype (enhet, midtveis og avsluttende), må du opprette en unik oppslagsverdi.

Dette kan gjøres ved hjelp av hjelpekolonner.Det første trinnet er å sette inn en hjelpekolonne til venstre for brøken.

Nå, for å lage en unik kvalifikator for hver forekomst av et navn, bruk følgende formel i C2: =A2&”|”&B2

Kopier denne formelen til alle cellene i hjelpekolonnen.Dette vil skape en unik oppslagsverdi for hver forekomst av navnet (som vist nedenfor):

Nå, mens det er dupliserte navn, er det ingen duplikater når navn kombineres med eksamensnivåer.

Dette er enkelt fordi nå kan du bruke hjelpekolonneverdien som oppslagsverdi.

Dette er formelen som gir deg resultatet i G3:I8.

=VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0)

Her kombinerer vi elevnavn og eksamensnivå for å få oppslagsverdien, denne oppslagsverdien bruker vi for å sjekke i hjelpekolonnen for samsvarende poster.

Merk: I eksemplet ovenfor brukte vi | Brukes som skilletegn når du legger til tekst i hjelpekolonner.I noen svært sjeldne (men mulige) tilfeller kan du ha to forskjellige kriterier, men å kombinere dem vil ende opp med å gi samme resultat.Dette er et eksempel:

Merk at mens A2 og A3 er forskjellige og B2 og B3 er forskjellige, er kombinasjonen til syvende og sist den samme.Men hvis du bruker skilletegn, vil selv kombinasjonen være annerledes (D2 og D3).

Her er en veiledning om hvordan du bruker VLOOKUP med flere forhold uten å bruke hjelpekolonner.

Eksempel 10 - Håndtering av feil ved bruk av VLOOKUP-funksjonen

Excel VLOOKUP-funksjonen returnerer en feil når den angitte oppslagsverdien ikke kan bli funnet.Hvis VLOOKUP ikke finner en verdi, vil du sannsynligvis ikke at stygge feilverdier skal forstyrre estetikken til dataene dine.

Du kan enkelt fjerne feilverdier med fulltekst uansett betydning, for eksempel "Ikke tilgjengelig" eller "Ikke funnet".

For eksempel, i eksemplet nedenfor, når du prøver å finne Brads poengsum i listen, returnerer det en feil fordi Brads navn ikke er på listen.

For å fjerne denne feilen og erstatte den med noe meningsfullt, pakk VLOOKUP-funksjonen inn i en IFERROR-funksjon.

Her er formelen:

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),"Ikke funnet")

IFERROR-funksjonen sjekker om verdien returnert av det første argumentet (i dette tilfellet VLOOKUP-funksjonen) er en feil.Hvis det ikke er en feil, returner verdien gjennom VLOOKUP-funksjonen, ellers returner ikke funnet.

IFERROR-funksjonen har vært tilgjengelig siden Excel 2007.Hvis du bruker en tidligere versjon, vennligst bruk følgende funksjoner:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

Det er alt for denne VLOOKUP-opplæringen.

Jeg prøver å presentere hovedeksemplet på bruk av Vlookup-funksjonen i Excel.Gi meg beskjed i kommentarfeltet hvis du vil se flere eksempler lagt til denne listen.

Relaterte Excel-funksjoner:

• Excel HLOOKUP-funksjon.
• Excel XLOOKUP-funksjon
• Excel-indeksfunksjon.
• Excel indirekte funksjon.
• Excel match funksjon.
• Excel offset funksjon.