Excel

Excel VLOOKUP -funktion

Excel Vlookup Function

Excel VLOOKUP -funktionResumé

VLOOKUP er en Excel -funktion til at slå data op i en tabel organiseret lodret. VLOOKUP understøtter omtrentlig og præcis matchning, og jokertegn (*?) for delvise kampe. Opslagsværdier skal vises i først kolonne i tabellen blev sendt til VLOOKUP.





Formål Slå en værdi op i en tabel ved at matche på den første kolonne Returværdi Den matchede værdi fra en tabel. Syntaks = VLOOKUP (værdi, tabel, col_index, [range_lookup]) Argumenter
  • værdi - Værdien, der skal kigges efter i den første kolonne i en tabel.
  • bord - Tabellen, hvorfra en værdi skal hentes.
  • col_index - Kolonnen i tabellen, hvorfra en værdi skal hentes.
  • rækkevidde_opslag - [valgfrit] TRUE = omtrentlig match (standard). FALSK = nøjagtig match.
Version Excel 2003 Brugsnotater

VLOOKUP er en Excel -funktion til at få data fra en tabel organiseret lodret. Opslagsværdier skal vises i først kolonne i tabellen blev sendt til VLOOKUP. VLOOKUP understøtter omtrentlig og præcis matchning, og jokertegn (*?) for delvise kampe.

Lodrette data | Kolonnenumre | Ser kun rigtigt ud | Matchende tilstande | Præcis match | Omtrentlig match | Første kamp | Wildcard Match | 2-vejs opslag | Flere kriterier | #N/A fejl | Videoer





V er for lodret

Formålet med VLOOKUP er at få information fra en tabel organiseret sådan:

VLOOKUP er til lodrette data



Ved at bruge ordrenummeret i kolonne B som en opslagsværdi kan VLOOKUP få kunde -id, beløb, navn og tilstand for enhver ordre. For eksempel for at få kundens navn til ordre 1004 er formlen:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

For vandrette data kan du bruge HLOOKUP , INDEX og MATCH , eller XLOOKUP .

hvordan man beregner intern afkast på excel

VLOOKUP er baseret på kolonnetal

Når du bruger VLOOKUP, skal du forestille dig, at hver kolonne i bord er nummereret, startende fra venstre. For at få en værdi fra en bestemt kolonne skal du angive det relevante nummer som 'kolonneindeks'. For eksempel er kolonneindekset for at hente fornavn nedenfor 2:

Eksempel på VLOOKUP eksakt match

Efternavn og e -mail kan hentes med kolonner 3 og 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

VLOOKUP ser kun rigtigt ud

VLOOKUP kan kun se til højre. De data, du vil hente (resultatværdier), kan vises i en hvilken som helst kolonne til højre af opslagsværdierne:

VLOOKUP kan kun se til højre

Hvis du har brug for at slå værdier til venstre, se INDEX og MATCH , eller XLOOKUP .

Præcis og omtrentlig matchning

VLOOKUP har to matchningsmåder, præcis og omtrentlig. Navnet på det argument, der styrer matchning, er ' rækkevidde_opslag '. Dette er et forvirrende navn, fordi det ser ud til at have noget at gøre med celleområder som A1: A10. Faktisk refererer ordet 'område' i dette tilfælde til 'værdiområde' - når rækkevidde_opslag er SAND, matcher VLOOKUP a værdier frem for en nøjagtig værdi. Et godt eksempel på dette er at bruge VLOOKUP for at beregne karakterer .

Det er vigtigt at forstå det rækkevidde_opslag som standard til TRUE , hvilket betyder, at VLOOKUP som standard vil bruge omtrentlig matchning, hvilket kan være farligt . Sæt rækkevidde_opslag til FALSK for at tvinge præcis matchning:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Bemærk: Du kan også angive nul (0) i stedet for FALSK for en nøjagtig matchning.

Præcis match

I de fleste tilfælde vil du sandsynligvis bruge VLOOKUP i nøjagtig match -tilstand. Dette giver mening, når du har en unik nøgle til brug som opslagsværdi, for eksempel filmtitlen i disse data:

VLOOKUP nøjagtig match med film

Formlen i H6 at finde År , baseret på en nøjagtig match af filmtitel, er:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Omtrentlig match

I de tilfælde, hvor du vil have bedste match , ikke nødvendigvis en præcis match , vil du gerne bruge omtrentlig tilstand. For eksempel vil vi nedenfor slå en provisionssats op i tabellen G5: H10. Opslagsværdierne kommer fra kolonne C. I dette eksempel skal vi bruge VLOOKUP i omtrentlige match tilstand, fordi der i de fleste tilfælde aldrig vil blive fundet et eksakt match. VLOOKUP -formlen i D5 er konfigureret til at udføre en omtrentlig match ved at indstille det sidste argument til TRUE:

VLOOKUP omtrentlig matchprovisionssats

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

VLOOKUP scanner værdier i kolonne G efter opslagsværdien. Hvis der findes et nøjagtigt match, vil VLOOKUP bruge det. Hvis ikke, vil VLOOKUP 'træde tilbage' og matche den foregående række.

Bemærk: data skal sorteres i stigende rækkefølge efter opslagsværdi, når du bruger omtrentlig matchningstilstand med VLOOKUP.

Første kamp

I tilfælde af dublerede værdier finder VLOOKUP første kamp når matchningstilstanden er nøjagtig. I skærmbilledet herunder er VLOOKUP konfigureret til at finde prisen for farven 'Grøn'. Der er tre poster med farven Grøn, og VLOOKUP returnerer prisen for først indgang, $ 17. Formlen i celle F5 er:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

VLOOKUP returnerer første kamp

Wildcard -match

VLOOKUP -funktionen understøtter jokertegn , som gør det muligt at udføre en delvis match på en opslagsværdi. For eksempel kan du bruge VLOOKUP til at hente værdier fra en tabel efter kun at have indtastet en del af en opslagsværdi. Hvis du vil bruge jokertegn med VLOOKUP, skal du angive nøjagtig match -tilstand ved at angive FALSK eller 0 for det sidste argument, rækkevidde_opslag . Formlen i H7 henter fornavnet 'Michael' efter at have skrevet 'Aya' i celle H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

VLOOKUP wildcard match

Læs mere detaljeret forklaring her .

2-vejs opslag

Inde i VLOOKUP-funktionen er kolonneindeksargumentet normalt hårdt kodet som et statisk tal. Du kan dog også oprette en dynamisk kolonneindeks ved at bruge funktionen MATCH til at finde den højre kolonne. Denne teknik giver dig mulighed for at oprette et dynamisk tovejsopslag, der matcher på begge rækker og kolonner. På skærmen herunder er VLOOKUP konfigureret til at udføre et opslag baseret på navn og måned. Formlen i H6 er:

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

VLOOKUP tovejsopslag

For flere detaljer, se dette eksempel .

Bemærk: Generelt INDEX og MATCH er en mere fleksibel måde at udføre tovejsopslag .

Flere kriterier

VLOOKUP -funktionen håndterer ikke flere kriterier indbygget. Du kan dog bruge en hjælperesøjle at forbinde flere felter sammen og bruge disse felter som flere kriterier inde i VLOOKUP. I eksemplet herunder er kolonne B en hjælperkolonne, der sammenkobles for- og efternavne sammen med denne formel:

 
=C5&D5 // helper column

VLOOKUP er konfigureret til at gøre det samme for at oprette en opslagsværdi. Formlen i H6 er:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

VLOOKUP med flere kriterier

For detaljer, se dette eksempel .

Bemærk: INDEX og MATCH og XLOOKUP er mere robuste måder at håndtere opslag baseret på flere kriterier.

VLOOKUP og #N/A fejl

Hvis du bruger VLOOKUP, støder du uundgåeligt på fejlen #N/A. Fejlen #N/A betyder bare 'ikke fundet'. På skærmen herunder findes f.eks. Opslagsværdien 'Toy Story 2' ikke i opslagstabellen, og alle tre VLOOKUP -formler returnerer #N/A:

VLOOKUP #N/A fejleksempel

En måde at 'fælde' NA -fejlen er at bruge IFNA funktion sådan her:

VLOOKUP #N/A fejleksempel - rettet

Formlen i H6 er:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Beskeden kan tilpasses efter ønske. For ikke at returnere noget (dvs. at vise et tomt resultat) når VLOOKUP returnerer #N/A, kan du bruge en tom streng som denne:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Fejl #N/A er nyttig, fordi den fortæller dig, at noget er galt. I praksis er der mange grunde til, at du kan se denne fejl, herunder:

  • Opslagningsværdien findes ikke i tabellen
  • Opslagsværdien er stavet forkert eller indeholder ekstra plads
  • Match -tilstand er nøjagtig, men bør være omtrentlig
  • Tabelområdet er ikke indtastet korrekt
  • Du kopierer VLOOKUP og tabellen referencen er ikke låst

Læs mere: VLOOKUP uden #N/A fejl

Mere om VLOOKUP

Andre noter

  • Range_lookup styrer, om værdi skal matche nøjagtigt eller ej. Standarden er SAND = tillad ikke-eksakt match.
  • Sæt rækkevidde_opslag at FALSE til kræve et nøjagtigt match og SANDT til tillade et ikke-eksakt match .
  • Hvis rækkevidde_opslag er SAND (standardindstillingen), vil et ikke-eksakt match få VLOOKUP-funktionen til at matche den nærmeste værdi i tabellen, der er stadig mindre end værdi .
  • Hvornår rækkevidde_opslag udelades, tillader VLOOKUP-funktionen et ikke-eksakt match, men det vil bruge et eksakt match, hvis der findes en.
  • Hvis rækkevidde_opslag er SAND (standardindstillingen), skal du sørge for, at opslagsværdier i den første række i tabellen er sorteret i stigende rækkefølge. Ellers returnerer VLOOKUP en forkert eller uventet værdi.
  • Hvis rækkevidde_opslag er FALSK (kræver nøjagtigt match), værdier i den første kolonne af bord behøver ikke at blive sorteret.


^