Hvordan bruke VLOOKUP i Excel

VLOOKUP er en av Excels mest nyttige funksjoner, og den er også en av de minst forstått. I denne artikkelen avmystifiserer vi VLOOKUP ved hjelp av et eksempel fra virkeligheten. Vi lager en brukbar fakturamal for et fiktivt selskap.

VLOOKUP er en Excel- funksjon . Denne artikkelen vil anta at leseren allerede har en forståelse av Excel-funksjoner, og kan bruke grunnleggende funksjoner som SUM, GJENNOMSNITT og I DAG. I den vanligste bruken er VLOOKUP en databasefunksjon , noe som betyr at den fungerer med databasetabeller - eller enklere, lister over ting i et Excel-regneark. Hva slags ting? Vel, alle slags ting. Du kan ha et regneark som inneholder en liste over ansatte, eller produkter, eller kunder, eller CD-er i CD-samlingen din, eller stjerner på nattehimmelen. Det har ikke noe å si.

Her er et eksempel på en liste eller database. I dette tilfellet er det en liste over produkter som vårt fiktive selskap selger:

Vanligvis har lister som dette en slags unik identifikator for hvert element i listen. I dette tilfellet er den unike identifikatoren i kolonnen "Varekode". Merk: For at VLOOKUP-funksjonen skal fungere med en database / liste, må den listen ha en kolonne som inneholder den unike identifikatoren (eller "nøkkel" eller "ID"), og den kolonnen må være den første kolonnen i tabellen . Eksempeldatabasen vår ovenfor oppfyller dette kriteriet.

Den vanskeligste delen av å bruke VLOOKUP er å forstå nøyaktig hva det er til. Så la oss se om vi kan få det klart først:

VLOOKUP henter informasjon fra en database / liste basert på en angitt forekomst av den unike identifikatoren.

I eksemplet ovenfor vil du sette inn VLOOKUP-funksjonen i et annet regneark med en varekode, og den vil returnere enten den tilsvarende varens beskrivelse, pris eller tilgjengelighet (dens "På lager" -mengde) som beskrevet i originalen liste. Hvilken av disse delene vil den gi deg tilbake? Vel, du får bestemme dette når du lager formelen.

Hvis alt du trenger er en del informasjon fra databasen, ville det være mye trøbbel å gå til for å konstruere en formel med en VLOOKUP-funksjon i. Vanligvis vil du bruke denne typen funksjonalitet i et regneark som kan brukes igjen, for eksempel en mal. Hver gang noen skriver inn en gyldig varekode, vil systemet hente ut all nødvendig informasjon om den tilsvarende varen.

La oss lage et eksempel på dette: En fakturamal som vi kan bruke om og om igjen i vårt fiktive selskap.

Først starter vi Excel, og vi lager en tom faktura:

Slik fungerer det: Personen som bruker fakturamalen, fyller ut en serie med varekoder i kolonne “A”, og systemet vil hente hver vares beskrivelse og pris fra produktdatabasen vår. Denne informasjonen vil bli brukt til å beregne linjesummen for hvert element (forutsatt at vi oppgir et gyldig antall).

For å holde dette eksemplet enkelt, finner vi produktdatabasen på et eget ark i samme arbeidsbok:

I virkeligheten er det mer sannsynlig at produktdatabasen ligger i en egen arbeidsbok. Det gjør liten forskjell for VLOOKUP-funksjonen, som egentlig ikke bryr seg om databasen ligger på samme ark, et annet ark eller en helt annen arbeidsbok.

Så vi har opprettet produktdatabasen vår, som ser slik ut:

For å teste VLOOKUP-formelen vi skal skrive, legger vi først inn en gyldig varekode i celle A11 på vår tomme faktura:

Deretter flytter vi den aktive cellen til cellen der vi vil at informasjon hentet fra databasen av VLOOKUP skal lagres. Interessant, dette er trinnet som folk flest tar feil. For å forklare videre: Vi er i ferd med å lage en VLOOKUP-formel som vil hente beskrivelsen som tilsvarer varekoden i celle A11. Hvor vil vi ha denne beskrivelsen når vi får den? I celle B11, selvfølgelig. Så det er der vi skriver VLOOKUP-formelen: i celle B11. Velg celle B11 nå.

Vi må finne listen over alle tilgjengelige funksjoner som Excel har å tilby, slik at vi kan velge VLOOKUP og få litt hjelp til å fylle ut formelen. Dette blir funnet ved først å klikke på fanen Formler , og deretter klikke på Sett inn funksjon :

Det vises en rute som lar oss velge noen av funksjonene som er tilgjengelige i Excel.

For å finne den vi leter etter, kan vi skrive et søkeord som "oppslag" (fordi funksjonen vi er interessert i er en oppslagsfunksjon ). Systemet vil gi oss en liste over alle oppslagsrelaterte funksjoner i Excel.  VLOOKUP er den andre på listen. Velg det og klikk OK .

De funksjonsargumenter vises, spørre oss for alle de argumenter (eller parametre ) som trengs for å fullføre FINN.RAD-funksjonen. Du kan tenke på denne boksen som funksjonen som stiller oss følgende spørsmål:

  1. Hvilken unik identifikator leter du opp i databasen?
  2. Hvor er databasen?
  3. Hvilken informasjon fra databasen, knyttet til den unike identifikatoren, vil du ha hentet til deg?

De tre første argumentene vises med fet skrift , noe som indikerer at de er obligatoriske argumenter (VLOOKUP-funksjonen er ufullstendig uten dem og vil ikke returnere en gyldig verdi). Det fjerde argumentet er ikke fet, noe som betyr at det er valgfritt:

Vi vil fullføre argumentene i rekkefølge, topp til bunn.

Det første argumentet vi trenger å fullføre er Lookup_value- argumentet. Funksjonen trenger at vi forteller hvor den unike identifikatoren ( varekoden i dette tilfellet) skal finne at den skal returnere beskrivelsen av. Vi må velge varekoden vi skrev inn tidligere (i A11).

Klikk på velgerikonet til høyre for det første argumentet:

Klikk deretter en gang på cellen som inneholder varekoden (A11), og trykk Enter :

Verdien av “A11” settes inn i det første argumentet.

Nå må vi oppgi en verdi for Table_array- argumentet. Med andre ord, må vi fortelle VLOOKUP hvor du finner databasen / listen. Klikk på velgerikonet ved siden av det andre argumentet:

Finn nå databasen / listen, og velg hele listen - ikke inkludert overskriftslinjen. I vårt eksempel ligger databasen på et eget regneark, så vi klikker først på regnearkfanen:

Deretter velger vi hele databasen, ikke inkludert overskriftslinjen:

... og trykk Enter . Celleområdet som representerer databasen (i dette tilfellet "'Produktdatabase"! A2: D7 ") blir automatisk lagt inn for oss i det andre argumentet.

Nå må vi angi det tredje argumentet, Col_index_num . Vi bruker dette argumentet til å spesifisere til VLOOKUP hvilken informasjon fra databasen, som er knyttet til varekoden vår i A11, vi ønsker å ha returnert til oss. I dette spesielle eksemplet ønsker vi å få varens beskrivelse returnert til oss. Hvis du ser på databladets regneark, vil du legge merke til at kolonnen "Beskrivelse" er den andre kolonnen i databasen. Dette betyr at vi må angi verdien "2" i Col_index_num- boksen:

Det er viktig å merke seg at vi ikke skriver inn en "2" her fordi "Beskrivelse" -kolonnen er i B- kolonnen på arbeidsarket. Hvis databasen tilfeldigvis startet i kolonne K i regnearket, ville vi fremdeles legge inn en "2" i dette feltet fordi "Beskrivelse" -kolonnen er den andre kolonnen i settet med celler vi valgte da vi spesifiserte "Table_array".

Til slutt må vi bestemme om vi skal legge inn en verdi i det endelige VLOOKUP-argumentet, Range_lookup . Dette argumentet krever enten en sann eller falsk verdi, eller den skal stå tom. Når du bruker VLOOKUP med databaser (som det er sant 90% av tiden), kan måten å bestemme hva du skal legge i dette argumentet tenkes på følgende måte:

Hvis den første kolonnen i databasen (kolonnen som inneholder de unike identifikatorene) er sortert alfabetisk / numerisk i stigende rekkefølge, er det mulig å legge inn en verdi av true i dette argumentet, eller la den være tom.

Hvis den første kolonnen i databasen er ikke sortert, eller det er sortert i synkende rekkefølge, så du skrive inn en verdi av falsk i dette argumentet

Da den første kolonnen i databasen vår ikke er sortert, skriver vi falsk inn i dette argumentet:

Det er det! Vi har skrevet inn all informasjonen som kreves for VLOOKUP for å returnere verdien vi trenger. Klikk på OK- knappen og legg merke til at beskrivelsen som tilsvarer varekoden “R99245” er riktig lagt inn i celle B11:

Formelen som ble opprettet for oss ser slik ut:

Hvis vi legger inn en annen varekode i celle A11, begynner vi å se kraften til VLOOKUP-funksjonen: Beskrivelsescellen endres for å matche den nye varekoden:

Vi kan utføre et lignende sett med trinn for å få varens pris returnert til celle E11. Merk at den nye formelen må opprettes i celle E11. Resultatet vil se slik ut:

... og formelen vil se slik ut:

Merk at den eneste forskjellen mellom de to formlene er det tredje argumentet ( Col_index_num ) har endret seg fra "2" til "3" (fordi vi ønsker at data skal hentes fra 3. kolonne i databasen).

Hvis vi bestemte oss for å kjøpe 2 av disse varene, ville vi legge inn en "2" i celle D11. Vi vil da legge inn en enkel formel i celle F11 for å få linjetotalen:

= D11 * E1

... som ser slik ut ...

Fullfører fakturamalen

Vi har lært mye om VLOOKUP så langt. Vi har faktisk lært alt vi skal lære i denne artikkelen. Det er viktig å merke seg at VLOOKUP kan brukes under andre omstendigheter enn databaser. Dette er mindre vanlig, og kan dekkes i fremtidige How-To Geek-artikler.

Fakturamalen vår er ikke fullført ennå. For å fullføre det, vil vi gjøre følgende:

  1. Vi fjerner eksempelkodekoden fra celle A11 og “2” fra celle D11. Dette vil føre til at de nyopprettede VLOOKUP-formlene viser feilmeldinger:



    Vi kan avhjelpe dette ved fornuftig bruk av Excels funksjoner IF () og ISBLANK () . Vi endrer formelen fra dette ...      = VLOOKUP (A11, 'Produktdatabase'! A2: D7,2, FALSE) ... til dette ... = IF (ISBLANK (A11), "", VLOOKUP (A11, 'Product Database'! A2) : D7,2, FALSE))


  2. Vi kopierer formlene i cellene B11, E11 og F11 ned til resten av varelinjene på fakturaen. Merk at hvis vi gjør dette, vil de resulterende formlene ikke lenger referere riktig til databasetabellen. Vi kan fikse dette ved å endre cellereferansene for databasen til absolutte cellereferanser. Alternativt - og enda bedre - kan vi opprette et navn for hele produktdatabasen (for eksempel "Produkter"), og bruke dette serienavnet i stedet for cellereferansene. Formelen vil endre seg fra dette ...      = IF (ISBLANK (A11), ””, VLOOKUP (A11, 'Produktdatabase'! A2: D7,2, FALSE)) ... til dette…       = IF (ISBLANK (A11), ”” , VLOOKUP (A11, Products, 2, FALSE)) ... og deretter kopier formlene ned til resten av fakturaen.
  3. Vi vil sannsynligvis "låse" cellene som inneholder formlene (eller rettere sagt låse opp de andre cellene), og deretter beskytte regnearket, for å sikre at våre nøye konstruerte formler ikke ved et uhell overskrives når noen kommer til å fylle ut fakturaen.
  4. Vi lagrer filen som en mal , slik at den kan brukes på nytt av alle i selskapet vårt

Hvis vi følte oss veldig flinke, ville vi lage en database med alle våre kunder i et annet regneark, og deretter bruke kunde-ID-en som ble angitt i celle F5 for automatisk å fylle ut kundens navn og adresse i cellene B6, B7 og B8.

Hvis du vil øve med VLOOKUP, eller bare se vår resulterende fakturamal, kan den lastes ned herfra.