Hvordan bruke en VLOOKUP-formel for å hente data fra en Excel-liste. Feilsøk VISNING når ting går galt. Steg-for-trinn-videoer og Excel-arbeidsbøker med VLOOKUP-eksempler.
VLOOKUP-video
Se trinnene for å lage en VLOOKUP-formel i produktprisoppslagsvideoen, se nedenfor. De skriftlige instruksjonene er under videoen. Få en prøvefil for produktprisen som skal følges sammen med videoen (last ned fil nr. 4).
Velg et sted for en oppslagstabell
Det er en god idé å lagre hvert oppslag Når du legger til og sletter rader i oppslagstabellene, vil du ikke tilfeldigvis legge til eller slette rader i en hvilken som helst annen tabell.
I dette eksemplet, oppslagstabellen er på et ark som heter Produkter.
Opprett en oppslagstabell
Oppslagsformler kan fungere vertikalt, på jakt etter verdier nedover i en kolonne, eller de kan jobbe horisontalt og se etter verdier over en rad . I dette eksemplet vil informasjonen bli lagret vertikalt, med verdier nede i en kolonne, og senere vil vi bruke en VLookup-formel til å gjøre et vertikalt oppslag.
- Angi overskriftene i første rad
- Den første kolonnen skal inneholde de unike nøkkelverdiene du vil basere oppslaget på. I dette eksemplet kan du finne pris for en bestemt produktkode.
- Hvis du har andre data på regnearket, la du minst en tom rad nederst i tabellen og en tom kolonne til høyre for tabellen for å skille oppslagstabell fra de andre dataene.
Merk: For å gjøre det enklere å referere til tabellen, kan du navngi området. Det er instruksjoner her: Navngi et område ▲ TOPP
VLOOKUP-funksjonsargumenter
VLOOKUP-funksjonen har fire argumenter:
- lookup_value : Hvilken verdi vil du slå opp? I dette eksemplet er produktkoden i celle A7, og du vil finne dens pr produktnavn.
- table_array: Hvor er oppslagstabellen? Hvis du bruker en absolutt referanse ($ A $ 2: $ C $ 5), i stedet for en relativ referanse (A2: C5), vil det være lettere å kopiere til formel til andre celler. Eller gi oppslagstabellen et navn, og referer til den ved navn.
- col_index_num: Hvilken kolonne har verdien du vil returnere? I dette eksemplet er produktnavnene i den andre kolonnen i oppslagstabellen.
- : Vil du ha en nøyaktig samsvar? Er en omtrentlig kamp ok?
Hvis du bruker SANT som siste argument, eller utelater det siste argumentet, kan en omtrentlig kamp returneres. Dette eksemplet har FALSE som det siste argumentet, så hvis produktkoden ikke blir funnet, blir resultatet # N / A. (Merk: Excel er ganske tilgivende, og godtar 0 i stedet for FALSE, og 1 i stedet for SANT.) ▲ TOPP
Opprett en VLookup-formel
Når du har opprettet oppslagstabellen, kan du opprette andre formler i arbeidsboken og hente informasjon fra produktlisten. For å forberede en faktura kan du for eksempel skrive inn en produktkode, og formler får produktnavnet eller prisen fra produkttabellen.
I dette eksemplet opprettes fakturaen på et ark som heter Faktura. VLOOKUP-formelen skal finne en nøyaktig samsvar med produktkoden og returnere produktnavnet.
For å opprette VLOOKUP-formelen som beregner produktprisen, følg disse trinnene:
- Velg fakturaark
- Skriv inn produktkode A23 i celle A7
- I celle B7 starter du VLOOKUP-formelen:
= VLOOKUP ( - Klikk på celle A7 for å angi referansen.
- Skriv et komma for å avslutte det første argumentet
- Velg produktarket
- Velg celler A2: C5 (produktlisten )
- Trykk på F4-tasten for å endre cellereferansene til absolutt. Formelen skal nå se slik ut:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5 - Skriv inn et komma for å avslutte det andre argumentet.
- Skriv inn en 2, som er kolonnen i oppslagstabellen som inneholder produktnavnet.
- Skriv inn et komma for å avslutte tredje argument.
- Skriv FALSE, for å spesifisere at en nøyaktig samsvar for produktkoden blir funnet, og legg til sluttbraketten.
- Formelen skal nå se ut l som dette:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,2, FALSE) - Trykk Enter-tasten for å fullføre formelen. Produktnavnet vises.
Merk: For å returnere produktprisen, opprett en VLOOKUP-formel som refererer til kolonne 3 i oppslagstabellen. Skriv for eksempel inn følgende formel i celle C7:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOPP
VLOOKUP-formel for verdiområde
I noen situasjoner foretrekkes en omtrentlig samsvar, så flere verdier vil gi samme resultat. For eksempel, når du vurderer studentoppgaver, skal alle oppgaver med karakteren 85 eller eldre få A-karakter.
Hvis du vil se trinnene for å lage denne formelen, kan du se VLOOKUP-videoen vist nedenfor. De skriftlige instruksjonene er under videoen.
I dette eksemplet opprettes oppslagstabellen på et ark som heter Karakterer. For å opprette oppslagstabellen, skriv inn minimumsscore for hver karakter i kolonne A. Angi samsvarende karakter i kolonne B. Sorter resultatene i stigende rekkefølge.
Celler A2: B6 ble kåret til GradeList.
Poengene legges inn på et ark som heter Rapportkort, der en VLOOKUP-formel beregner karakteren.
I skjermbilde nedenfor, formelen er kopiert ned til rad 6, og du kan se formelen i celle C6.
Kombiner VLOOKUP med MATCH
I stedet for å skrive kolonnenummeret i en VLOOKUP-formel, bruk MATCH-funksjonen for å finne riktig kolonne i oppslagstabellen. Dette har et par fordeler:
- Gjør formelen fleksibel, så det er lettere å kopiere formelen over et regneark.
- Kan forhindre problemer hvis nye kolonner legges til i oppslagstabellen, eller hvis oppslagskolonnene er omorganisert.
Denne videoen viser trinnene, og det er skrevet instruksjoner for et annet eksempel under videoen.
Nettleseren din kan ikke vise denne rammen. Her er en lenke til siden
VLOOKUP With MATCH for Order Details
I dette eksemplet vil en VLOOKUP-formel returnere bestillingsdetaljene fra en oppslagstabell, basert på ordre-ID-nummeret . Her er oppslagstabellen, kalt tblOrders.
MERK: Dette eksemplet er i eksempel på arbeidsbok nr. 1, på arket som heter OrdersMATCH.
Her er regnearket med VLOOKUP-formlene. Vi ønsker regionen, bestillingsdato og bestillingsbeløp for hver bestilling, så det kreves 3 VLOOKUP-formler.
Hvis kolonnetallene er skrevet inn i formelen, er det nødvendig med en annen formel i hver kolonne:
- Region: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
MATCH-funksjonen
I stedet for å skrive kolonnetallet i VLOOKUP-formelen, kan vi bruke MATCH-funksjonen. MATCH-funksjonen finner posisjonen til et element i en liste, og returnerer posisjonsnummeret.
I skjermbildet nedenfor returnerer MATCH-formelen 2 som posisjon for «Region», i overskriftscellene (A1: D1) for oppslagstabellen.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
MERK: For at denne teknikken skal fungere riktig, er overskriftene på VLOOKUP-arket må samsvare nøyaktig med oppslagstabelloverskriftene. For å sikre nøyaktig samsvar, er VLOOKUP-overskriftscellene knyttet til oppslagstabellens overskriftsceller.
Legg til MATCH i VLOOKUP
For å legge til MATCH-funksjonen til VLOOKUP-formelen, er det bare å erstatte skrevet kolonnenummer
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
med MATCH-formelen:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Kopier VLOOKUP-formelen over
Nå, i stedet for å trenge en annen formel i hver kolonne, kan du kopiere formelen og bruke den samme formelen i alle kolonnene. I hver kolonne vil den referere til overskriftscellen i den kolonnen, og finne sin posisjon i oppslagstabellen.
MERKNAD: Hvis du fyller formelen på tvers av kolonner med annen formatering, følger du disse trinnene:
- Velg cellen med formelen du vil kopiere
- Pek på fyllhåndtaket på den valgte cellen (liten firkant nederst i høyre hjørne)
- Trykk på HØYRE museknapp, og dra over til den siste cellen som trenger formelen
- Slipp museknappen, og klikk på Fyll uten formatering
Kombiner IF og VLOOKUP
Du kan bruke en IF-formel med en VLookup-formel, for å returnere eksakte verdier hvis de blir funnet, og en tom streng hvis ikke funnet.
For å se trinnene for å sette opp IF- og VLOOKUP-formelen, kan du se denne korte videoen. De skriftlige instruksjonene er under videoen.
For å skjule feil ved å kombinere IF med VLOOKUP, følg disse trinnene:
- På fakturaarket, i celle A8, skriv inn produktet kode A28. Hvis VLookup-formelen i celle B8 har FALSE som det fjerde argumentet, er resultatet # N / A, fordi det ikke er noe nøyaktig samsvar med produktkoden i oppslagstabellen.
- Pakk VLookup-formelen med en IF-formel (i dette eksemplet har produktlisten fått navnet), ved hjelp av ISNA-funksjonen for å se etter en # N / A-feil:
- Trykk på Enter-tasten, og cellen vises tom. Fordi det ikke ble funnet noe nøyaktig samsvar, returnerte VLookup-formelen et # N / A, så resultatet av ISNA-funksjonen er SANT. IF-formelen konverterte dette til en tom streng.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE)) ), «», VLOOKUP (A8, ProductList, 2, FALSE))
Kombiner IFERROR og VLOOKUP
Takk til Chip Pearson for å foreslå denne formelen.
I Excel 2007 ble en ny funksjon, IFERROR, introdusert . Du kan bruke en IFERROR-formel med VLookup for å sjekke flere tabeller for en verdi, og returnere relatert informasjon når den blir funnet. I dette eksemplet har tre regioner, vest, øst og sentral, ordreark. På hvert ark er et navngitt område – OrdersW, OrdersE og OrdersC.
På et ark som heter Orders, kan du skrive inn en ordre-ID og deretter bruke en VLOOKUP med IFERROR for å sjekke hvert navngitte område , og se informasjonen om den valgte bestillingen.
- På bestillingsarket, i celle B6, skriver du inn en 4 som OrderID. Denne ordren ble plassert i den sentrale regionen.
- For å bare sjekke østregionens tabell, ville VLOOKUP-formelen i celle C6 være:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Trykk på Enter-tasten, og VLOOKUP-formelen returnerer et # N / A, fordi ordre-ID 4 ikke er i ordretabellen for østregioner.
- Fordi en ordre kan ha blitt plassert i en av de tre regionene, trenger du en formel som vil sjekke hver tabell.
Hvis ordre-ID ikke finnes i den første tabellen, skal formelen sjekke andre tabell. Hvis ordre-ID ikke er i den andre tabellen tabellen, bør den sjekke den tredje tabellen. Hvis bestillings-ID-en ikke er «t i den tredje tabellen, bør meldingen» Ikke funnet «vises i cellen. - IFERROR-formelen lar deg sjekke en verdi, spesifiser deretter hva du skal gjøre hvis det blir funnet en feil. Hvis du bruker IFERROR med den eksisterende formelen, kan du vise «Ikke funnet», i stedet for # N / A-feilen:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), «Not Found») - For å sjekke alle de tre tabellene, kan du neste IFERROR- og VLOOKUP-formler:
Dette sjekker OrdersE-tabellen, og hvis en feil blir funnet, sjekker OrdersW-tabellen og deretter OrdersC. Hvis OrderID ikke finnes i noen av de tre tabellene, vises meldingen Ikke funnet i cellen.
Du kan også sjekke flere tabeller i eldre versjoner av Excel, der IFERROR ikke er tilgjengelig, ved hjelp av en lengre formel:
VLOOKUP for Combined Values
I noen tabeller er det kanskje ikke unike verdier hvilken som helst kolonne i oppslagstabellen. For eksempel, i tabellen vist nedenfor, er Jacket oppført to ganger i kolonne A. Det er imidlertid bare en post for hver jakke og størrelseskombinasjon – Jacket Medium i rad 4 og Jacket Large i rad 5.
Hvis du trenger å finne prisen for en stor jakke, vil en VLOOKUP bare basert på kolonne A returnere prisen for den første jakken som er oppført (Medium ). Du vil være for billig på jakken – selge den til 60,00, i stedet for 65,00.
For å skape unike oppslagsverdier, må du kan sette inn en ny kolonne på venstre side av tabellen, og bruke en formel for å kombinere produktet og størrelsen. I celle A2 kombinerer formelen verdien i B2 og rørtegnet og verdien i C2.
= B2 & «|» & C2
Kopier formelen ned til siste rad med data, slik at hver rad har en unik verdi i kolonne A.
Merk : I stedet for rørtegnet kan du bruke et annet tegn som ikke er inkludert i dataene dine.
Deretter, i en VLOOKUP-formel, kombiner produkt og størrelse som Lookup_value. I celle H1 kombinerer formelen verdien i F1 og rørtegnet og verdien i G1.
= VLOOKUP (F1 &» | «& G1, $ A $ 2: $ D $ 5,4, FALSE)
Merk: Prisen er nå i kolonne 4, i stedet for kolonne 3. ▲ TOPP
Feilsøk VLOOKUP-formelen
Din VLOOKUP-formel kan returnere et # N / A, selv om verdien du leter etter ser ut til å være i oppslagstabellen. Vanlige årsaker til dette er:
Tekst vs. antall
Mellomromstegn
Andre tegn
Tekst kontra antall
En vanlig årsak til denne feilen er at en av verdiene er et tall, og den andre er tekst. Oppslagstabellen kan for eksempel inneholde «123 (tekst), og verdien du skal slå opp er 123 (et tall).
- Hvis det er mulig, konverterer du teksten til tall ved å bruke en av metoder vist her: Konverter tekst til tall
- Hvis du ikke kan konvertere dataene, kan du konvertere oppslagsverdien i VLOOKUP-formelen ved å bruke en av løsningene nedenfor:
- Oppslagsverdier er tekst, tabell har tall
- Oppslagsverdier er tall, tabell har tekst
- For å finne ut hvilke verdier som er tekst, og hvilke som er tall, se det detaljerte feilsøkingstrinnet for antall eller tekst
1. Oppslagsverdier er tekst, og tabellen inneholder tall
Hvis oppslagstabellen inneholder tall, og verdien du skal slå opp er tekst, bruker du en formel som ligner på følgende:
= VLOOKUP (–A7, produkter!$ A $ 2: $ C $ 5,3, FALSE)
Dobbelt unary (-) konverterer tekst til et tall, og vil fungere riktig selv om oppslagsverdiene er tall.
2. Oppslagsverdier er tall, og tabellen inneholder tekst
Hvis oppslagstabellen inneholder tekst, og verdien du skal slå opp er numerisk, bruker du en formel som ligner på følgende:
= VLOOKUP (A7 & «»), Produkter! $ A $ 2: $ C $ 5,3, FALSE)
ELLER
= VLOOKUP (TEKST (A7, «00000») , Produkter! $ A $ 2: $ C $ 5,3, FALSE)
TEKST-funksjonen konverterer et tall til tekst, og vil fungere riktig selv om oppslagsverdiene er tekst. I det første eksemplet oppretter operatøren & en tekststreng fra et uformatert nummer. I det andre eksemplet vil et tall formatert med ledende nuller (f.eks. 00123) matche et «tall» med ledende nuller.
Hvis du vil se trinnene for å fikse VLOOKUP-problemet når oppslagstabellen har tekstverdier, se denne korte videoopplæringen.
Plasser i en verdi, og ikke den andre
En annen potensiell årsak til at det ikke blir funnet noen samsvarende verdi, er en forskjell i mellomrom. En av verdiene kan inneholde ledende mellomrom (eller etterfølgende eller innebygde mellomrom), og den andre ikke. For å teste verdiene kan du bruke LEN-funksjonen til å sjekke lengden på hver verdi.
For eksempel: = LENN (A7) returnerer antall tegn i celle A7. Det skal være lik antall tegn i den matchende cellen i oppslagstabellen.
Fjern om mulig det unødvendige mellomrom, og VLOOKUP-formelen skal fungere riktig. Hvis du ikke kan fjerne mellomromene, bruk TRIM-funksjonen i VLOOKUP for å fjerne ledende, etterfølgende eller dupliserte mellomrom. For eksempel:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Andre tegn
Hvis TRIM-funksjonen alene ikke løser problemet , kan du prøve et av følgende forslag:
- SUBSTITUTE-funksjon
- RENGJØRING-funksjon
- Makro for å fjerne netttegn
SUBSTITUTE-funksjon
Bruk SUBSTITUTE-funksjonen til å fjerne uønskede tegn. Det er et eksempel på Contextures-bloggen: Rengjør Excel-data med TRIM og SUBSTITUTE
CLEAN-funksjon
En annen måte å løse VLOOKUP-problemer på er med CLEAN-funksjonen, som kan fjerne noen uønskede tegn fra teksten. Det er mer informasjon om CLEAN-funksjonen i dette Contextures-blogginnlegget: 30 Excel-funksjoner på 30 dager: 29 – CLEAN
Nettkarakterer i den ene verdien, og ikke den andre
Hvis du kopierte data fra en webside, kan det inneholde HTML-mellomrom (& nbsp) tegn. David McRitchie har skrevet en makro for å fjerne dem, sammen med o der mellomromstegn
Problemer ved sortering av VLOOKUP-formel
En VLOOKUP-formel kan gi de riktige resultatene til å begynne med, men viser deretter feil resultat hvis listen over elementer er sortert. Dette kan oppstå hvis referansen til oppslagsverdien inkluderer et arknavn. For eksempel:
= VLOOKUP («Bestillingsskjema»! B5, Produkter! $ B $ 2: $ C $ 6,2, FALSE)
MERK: Dette problemet kan oppstå med andre funksjoner også, for eksempel en INDEX / MATCH-oppslagsformel.
Se denne videoen for å se trinnene for å løse problemet, og last ned VLOOKUP Sorteringsproblemfilen for å følge med. De skriftlige instruksjonene er under videoen.
Arknavn i referanse
Denne typen referanse opprettes hvis du klikker på et annet ark mens du bygger formelen. Så snart du gjør det, legger Excel arknavnet til eventuelle påfølgende referanser i formelen.
I skjermbildet over er Dress i celle B9, og celle C9 viser den riktige prisen på $ 30.
Imidlertid, etter å ha sortert produktene AZ, går Dressen opp til celle B5, men formelen i celle C5 fortsetter å referere til celle B9. På grunn av arknavnene i referansene, beholder Excel de originale referansene, i stedet for å beholde en referanse til den gjeldende raden. Cell C5 viser prisen for en genser, i stedet for en kjole. ▲ TOPP
Løs problemet
For å løse problemet, fjern eventuelle unødvendige arknavn fra VLOOKUP cellereferanser. Her er den reviderte formelen for celle C5:
= VLOOKUP (B5, Products! $ B $ 2: $ C $ 6,2, FALSE)
Etter at unødvendige arknavn er fjernet, listen kan sorteres trygt, og de riktige resultatene vises for hvert element.
Last ned prøvefilene
1. Skaff deg VLOOKUP-eksempelarbeidsboken (Excel 2007 og nyere). Den zip-filen er i xlsx-format, og inneholder ingen makroer.
2. For Excel 2003, få denne versjonen av VLOOKUP-eksempelfilen. Den zip-filen er i xls-format og inneholder ingen makroer.
3. For å se problemet som kan oppstå når du sorterer med VLOOKUP, kan du hente denne eksemplarfilen til VLOOKUP Sorting. Den zip-filen er i xlsx-format, og inneholder ingen makroer.
4. Få arbeidsboken for produktprisoppslag, brukt i videoopplæringen.Den zip-filen er i xlsx-format, og inneholder ingen makroer. ▲ TOPP
Flere opplæringsprogrammer
VLOOKUP-nummer / feilsøking av tekst
VLOOKUP fra en annen arbeidsbok
HLOOKUP
VELG Funksjon
Oppslag – 2 kriterier
SØK
INDEKS / MATCH
Sammenlign oppslagsfunksjoner
Funksjonsliste