Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel VLOOKUP -funksjon i Excel for å slå opp en verdi.
Oversikt over VLOOKUP -funksjon
VLOOKUP -funksjonen Vlookup står for vertikal oppslag. Den søker etter en verdi i kolonnen lengst til venstre i en tabell. Deretter returnerer en verdi et spesifisert antall kolonner til høyre fra funnet verdi. Det er det samme som en oppkobling, bortsett fra at det ser opp verdier vertikalt i stedet for horisontalt.
(Legg merke til hvordan formelinngangene vises)
VLOOKUP -funksjon Syntaks og argumenter:
| 1 | = VLOOKUP (oppslag_verdi, tabell_array, kol_indeks_nummer, område_oppslag) |
oppslag_verdi - Verdien du vil søke etter.
tabell_array - Dataområdet som inneholder både verdien du vil søke etter og verdien du vil at vlookup skal returnere. Kolonnen som inneholder søkeverdiene må være kolonnen lengst til venstre.
col_index_num - Kolonnenummeret i dataområdet, som du vil returnere en verdi fra.
range_lookup - Sant eller usant. FALSK søker etter en eksakt treff. TRUE søker etter nærmeste treff som er mindre enn eller lik oppslagsverdi. Hvis TRUE er valgt, må kolonnen lengst til venstre (oppslagskolonnen) sorteres stigende (lavest til høyest).
Hva er VLOOKUP -funksjonen?
Som en av de eldre funksjonene i regnearkverdenen, brukes VLOOKUP -funksjonen til å gjøre Vertisk Oppslag. Den har noen begrensninger som ofte blir overvunnet med andre funksjoner, for eksempel INDEX/MATCH. Imidlertid har den fordelen av å være en enkelt funksjon som kan gjøre et eksakt samsvar i seg selv. Det pleier også å være en av de første funksjonene folk lærer om.
Grunnleggende eksempel
La oss se på et eksempel på data fra en karakterbok. Vi tar for oss flere eksempler for å trekke ut informasjon for spesifikke studenter.
Hvis vi vil finne hvilken klasse Bob er i, ville vi skrive formelen:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Viktige ting å huske er at elementet vi søker etter (Bob), må være i den første kolonnen i søkeområdet vårt (A2: C5). Vi har fortalt funksjonen at vi ønsker å returnere en verdi fra 2nd kolonne, som i dette tilfellet er kolonne B. Til slutt indikerte vi at vi ønsker å gjøre en nøyaktig treff ved å plassere False som det siste argumentet. Her vil svaret være "Lesing".
Sidetips: Du kan også bruke tallet 0 i stedet for False som det siste argumentet, ettersom de har samme verdi. Noen foretrekker dette fordi det er raskere å skrive. Bare vet at begge er akseptable.
Skiftet data
For å legge til en avklaring i vårt første eksempel, trenger ikke oppslagselementet å være i kolonne A i regnearket, bare den første kolonnen i søkeområdet. La oss bruke det samme datasettet:
La oss nå finne karakteren for vitenskapsklassen. Vår formel ville være
| 1 | = VLOOKUP ("Science", B2: C5, 2, FALSE) |
Dette er fortsatt en gyldig formel, ettersom den første kolonnen i søkeområdet vårt er kolonne B, og det er der søkeordet vårt "Science" vil bli funnet. Vi returnerer en verdi fra 2nd kolonne i søkeområdet, som i dette tilfellet er kolonne C. Svaret er da "A-".
Bruk av jokertegn
VLOOKUP -funksjonen støtter bruk av jokertegnene "*" og "?" når du søker. La oss for eksempel si at vi hadde glemt hvordan vi skulle skrive Franks navn, og bare ønsket å søke etter et navn som begynner med "F". Vi kan skrive formelen
| 1 | = VISNING ("F*", A2: C5, 2, FALSK) |
Dette vil kunne finne navnet Frank i rad 5, og deretter returnere verdien fra 2nd relativ kolonne. I dette tilfellet vil svaret være "Vitenskap".
Ikke-eksakt match
Mesteparten av tiden vil du sørge for at det siste argumentet i VLOOKUP er False (eller 0), slik at du får en eksakt samsvar. Imidlertid er det noen ganger du kan lete etter en ikke-eksakt kamp. Hvis du har en liste over sorterte data, kan du også bruke VLOOKUP til å returnere resultatet for elementet som enten er det samme eller det nest minste. Dette brukes ofte når du arbeider med økende tallområder, for eksempel i et skattebord eller provisjonsbonuser.
La oss si at du vil finne skattesatsen for en inntekt som er angitt i celle D2. Formelen i D4 kan være:
| 1 | = VISNING (D2, A2: B6, 2, SANN) |
Forskjellen i denne formelen er at vårt siste argument er "True". I vårt spesifikke eksempel kan vi se at når våre individuelle gir en inntekt på $ 45 000, vil de ha en skattesats på 15%.
Merk: Selv om vi vanligvis ønsker en eksakt samsvar med False som argument, glemmer du å angi 4th argumentet i en VLOOKUP, er standarden True. Dette kan føre til at du får uventede resultater, spesielt når du arbeider med tekstverdier.
Dynamisk kolonne
VLOOKUP krever at du gir et argument som sier hvilken kolonne du vil returnere en verdi fra, men anledningen kan oppstå når du ikke vet hvor kolonnen vil være, eller du vil tillate brukeren å endre hvilken kolonne han skal returnere fra. I disse tilfellene kan det være nyttig å bruke MATCH -funksjonen til å bestemme kolonnenummeret.
La oss vurdere vårt karakterbokeksempel igjen, med noen innganger i E2 og E4. For å få kolonnenummeret kan vi skrive en formel for
| 1 | = MATCH (E2, A1: C1, 0) |
Dette vil prøve å finne den eksakte posisjonen til “Grade” innenfor området A1: C1. Svaret vil være 3. Når vi vet dette, kan vi koble det til en VLOOKUP -funksjon og skrive en formel i E6 slik:
| 1 | = VISNING (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Så vil MATCH -funksjonen evaluere til 3, og det forteller VLOOKUP å returnere et resultat fra 3rd kolonne i A2: C5 -området. Totalt sett får vi ønsket resultat av “C”. Formelen vår er dynamisk nå ved at vi kan endre enten kolonnen å se på eller navnet vi skal søke etter.
Begrensninger for VLOOKUP
Som nevnt i begynnelsen av artikkelen, er den største ulempen med VLOOKUP at det krever at søkeordet finnes i venstre kolonne i søkeområdet. Selv om det er noen fancy triks du kan gjøre for å overvinne dette, er det vanlige alternativet å bruke INDEX og MATCH. Den kombinasjonen gir deg mer fleksibilitet, og det kan noen ganger til og med være en raskere beregning.
VLOOKUP i Google Sheets
VLOOKUP -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
Ytterligere merknader
Bruk VLOOKUP -funksjonen til å utføre et VERTISK oppslag. VLOOKUP søker etter en eksakt samsvar (range_lookup = FALSK) eller den nærmeste kampen som er lik eller mindre enn oppslagsverdi (range_lookup = SANN, bare numeriske verdier) i den første raden i tabell_arrayen. Den returnerer deretter en tilsvarende verdi, n antall rader under kampen.
Når du bruker en VLOOKUP for å finne en eksakt samsvar, definerer du først en identifiserende verdi du vil søke etter som oppslag_verdi. Denne identifiseringsverdien kan være en SSN, ansatt -ID, navn eller en annen unik identifikator.
Deretter definerer du området (kalt tabell_array) som inneholder identifikatorene i den øverste raden og hvilke verdier du til slutt vil søke etter i radene under den. VIKTIG: De unike identifikatorene må være i den øverste raden. Hvis de ikke er det, må du enten flytte raden til toppen, eller bruke MATCH / INDEX i stedet for VLOOKUP.
Tredje, definer radnummer (row_index) av tabell_array som du ønsker å returnere. Husk at den første raden, som inneholder de unike identifikatorene, er rad 1. Den andre raden er rad 2, etc.
Sist må du angi om du vil søke etter en eksakt treff (FALSK) eller nærmeste treff (SANN) i range_lookup. Hvis alternativet for eksakt samsvar er valgt, og det ikke blir funnet en eksakt samsvar, returneres en feil (#N/A). For å få formelen tilbake tom eller “ikke funnet”, eller en annen verdi i stedet for feilverdien (#N/A), bruk IFERROR -funksjonen med VLOOKUP.
Slik bruker du VLOOKUP -funksjonen for å returnere et omtrentlig samsvarssett: range_lookup = SANN. Dette alternativet er bare tilgjengelig for numeriske verdier. Verdiene må sorteres i stigende rekkefølge.
For å utføre et horisontalt oppslag, bruk HLOOKUP -funksjonen i stedet.
VLOOKUP Eksempler i VBA
Du kan også bruke VLOOKUP -funksjonen i VBA. Type:application.worksheetfunction.vlookup (oppslag_verdi, tabell_rute, kol_indeks_nummer, område_oppslag)
For funksjonsargumentene (oppslagsverdi osv.) Kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.
Gå tilbake til listen over alle funksjoner i Excel
