Sådan bruges en VLOOKUP-formel til at hente data fra en Excel-liste. Fejlfinding af VLOOKUP, når tingene går galt. Trinvise videoer og Excel-arbejdsbøger med VLOOKUP-eksempler.
VLOOKUP-video
Se trinene til oprettelse af en VLOOKUP-formel i produktprisopslagsvideoen, se nedenfor. De skriftlige instruktioner er under videoen. Få den eksempelfil til produktprisen, der skal følges sammen med videoen (download fil nr. 4).
Vælg en placering til en opslagstabel
Det er en god idé at gemme hvert opslag tabel på et separat regneark i projektmappen. Når du tilføjer og sletter rækker i opslagstabellerne, vil du ikke ved et uheld tilføje eller slette rækker i en hvilken som helst anden tabel.
I dette eksempel er opslagstabellen er på et ark med navnet Produkter.
Opret en opslagstabel
Opslagsformler kan arbejde lodret, søge efter værdier ned i en kolonne, eller de kan arbejde vandret og søge værdier på tværs af en række . I dette eksempel gemmes informationen lodret med værdier nede i en kolonne, og senere bruger vi en VLookup-formel til at foretage en lodret opslag.
- Indtast overskrifterne i første række
- Den første kolonne skal indeholde de unikke nøgleværdier, som du vil basere opslaget på. I dette eksempel kan du finde pris for en bestemt produktkode.
- Hvis du har andre data på regnearket, skal du efterlade mindst en tom række i bunden af tabellen og en tom kolonne til højre for tabellen for at adskille opslagstabel fra de andre data.
Bemærk: For at gøre det lettere at henvise til tabellen kan du navngive området. Der er instruktioner her: Navngivning af et interval ▲ TOP
VLOOKUP-funktionsargumenter
VLOOKUP-funktionen har fire argumenter:
- lookup_value : Hvilken værdi vil du slå op? I dette eksempel er produktkoden i celle A7, og du vil finde dens pr produktnavn.
- table_array: Hvor er opslagstabellen? Hvis du bruger en absolut reference ($ A $ 2: $ C $ 5), i stedet for en relativ reference (A2: C5), vil det være lettere at kopiere til formlen til andre celler. Eller navngiv opslagstabellen, og henvis til den ved navn.
- col_index_num: Hvilken kolonne har den værdi, du vil returnere? I dette eksempel findes produktnavne i den anden kolonne i opslagstabellen.
- : Vil du have et nøjagtigt match? Er en omtrentlig kamp okay?
Hvis du bruger SAND som det sidste argument eller udelader det sidste argument, kan et omtrentligt match returneres. Dette eksempel har FALSE som det sidste argument, så hvis produktkoden ikke findes, bliver resultatet # N / A. (Bemærk: Excel er ret tilgivende og accepterer 0 i stedet for FALSE og 1 i stedet for SAND.) ▲ TOP
Opret en VLookup-formel
Når du har oprettet opslagstabellen, kan du oprette andre formler i projektmappen og hente oplysninger fra produktlisten. For at forberede en faktura kan du f.eks. Indtaste en produktkode, og formler får produktnavnet eller prisen fra produkttabellen.
I dette eksempel oprettes fakturaen på et ark med navnet Faktura. VLOOKUP-formlen skal finde et nøjagtigt match med produktkoden og returnere produktnavnet.
For at oprette VLOOKUP-formlen, der beregner produktprisen, skal du følge disse trin:
- Vælg fakturaark
- Indtast produktkode A23 i celle A7
- I celle B7 skal du starte formlen VLOOKUP:
= VLOOKUP ( - Klik på celle A7 for at indtaste sin reference.
- Skriv et komma for at afslutte det første argument
- Vælg produktarket
- Vælg celler A2: C5 (produktlisten )
- Tryk på F4-tasten for at ændre cellehenvisningerne til absolutte. Formlen skal nu se sådan ud:
= VLOOKUP (A7, produkter! $ A $ 2: $ C $ 5 - Skriv et komma for at afslutte det andet argument.
- Skriv en 2, som er kolonnen i opslagstabellen, der indeholder produktnavnet.
- Skriv et komma for at afslutte tredje argument.
- Skriv FALSE for at angive, at der findes en nøjagtig matchning af produktkoden, og tilføj den afsluttende parentes.
- Formlen skal nu se ud l ligesom dette:
= VLOOKUP (A7, produkter! $ A $ 2: $ C $ 5,2, FALSK) - Tryk på Enter-tasten for at udfylde formlen. Produktnavnet vises.
Bemærk: For at returnere produktprisen skal du oprette en VLOOKUP-formel, der henviser til kolonne 3 i opslagstabellen. Indtast for eksempel følgende formel i celle C7:
= VLOOKUP (A7, produkter! $ A $ 2: $ C $ 5,3, FALSK) ▲ TOP
VLOOKUP-formel til række af værdier
I nogle situationer foretrækkes et omtrentligt match, så flere værdier returnerer det samme resultat. For eksempel skal alle papirer med en karakter på 85 eller derover få en A-karakter, når de klassificerer studerendes papirer.
Hvis du vil se trinene til oprettelse af denne formel, skal du se VLOOKUP-videoen vist nedenfor. De skriftlige instruktioner er under videoen.
I dette eksempel oprettes opslagstabellen på et ark med navnet Grades. For at oprette opslagstabellen skal du indtaste minimumscore for hver karakter i kolonne A. Indtast den matchende karakter i kolonne B. Sorter resultaterne i stigende rækkefølge.
Celler A2: B6 blev navngivet GradeList.
Scorerne indtastes på et ark med navnet Rapportkort, hvor en VLOOKUP-formel beregner karakteren.
I skærmbillede nedenunder, formlen er kopieret ned til række 6, og du kan se formlen i celle C6.
Kombiner VLOOKUP med MATCH
I stedet for at skrive kolonnenummeret i en VLOOKUP-formel, skal du bruge MATCH-funktionen til at finde den rigtige kolonne i opslagstabellen. Dette har et par fordele:
- Gør formlen fleksibel, så det er lettere at kopiere formlen på tværs af et regneark.
- Kan forhindre problemer, hvis nye kolonner tilføjes i opslagstabellen, eller hvis opslagskolonnerne er omarrangeret.
Denne video viser trinene, og der er skrevet instruktioner til et andet eksempel under videoen.
Din browser kan ikke vise denne ramme. Her er et link til siden
VLOOKUP With MATCH for Order Details
I dette eksempel returnerer en VLOOKUP-formel ordredetaljerne fra en opslagstabel, baseret på ordre-ID-nummeret . Her er opslagstabellen med navnet tblOrders.
BEMÆRK: Dette eksempel findes i eksempel på projektmappe 1 på arket med navnet OrdersMATCH.
Her er regnearket med VLOOKUP-formlerne. Vi ønsker regionen, ordredato og ordrebeløb for hver ordre, så der kræves 3 VLOOKUP-formler.
Hvis kolonnenumrene er skrevet i formlen, er der brug for en anden formel i hver kolonne:
- Region: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
MATCH-funktionen
I stedet for at skrive kolonnenummeret i VLOOKUP-formlen kan vi bruge MATCH-funktionen. MATCH-funktionen finder placeringen af et element i en liste og returnerer positionsnummeret.
I nedenstående skærmbillede returnerer MATCH-formlen 2 som positionen “Region” i overskriftscellerne (A1: D1) for opslagstabellen.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
BEMÆRK: For at denne teknik fungerer korrekt, er overskrifter på VLOOKUP-arket skal svare nøjagtigt til opslagstabeloverskrifterne. For at sikre en nøjagtig matchning er VLOOKUP-overskriftscellerne knyttet til opslagstabelens overskriftsceller.
Tilføj MATCH til VLOOKUP
For at tilføje MATCH-funktionen til VLOOKUP-formlen skal du bare erstatte indtastet kolonnenummer
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
med MATCH-formlen:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Kopier VLOOKUP-formlen over
Nu, i stedet for at have brug for en anden formel i hver kolonne, kan du kopiere formlen på tværs og bruge den samme formel i alle kolonnerne. I hver kolonne henviser den til overskriftscellen i den kolonne og finder dens position i opslagstabellen.
BEMÆRK: Hvis du udfylder formlen på tværs af kolonner med forskellig formatering, skal du følge disse trin:
- Vælg cellen med den formel, du vil kopiere
- Peg på udfyldningshåndtaget på den valgte celle (lille firkant i nederste højre hjørne)
- Tryk på HØJRE museknap, og træk hen til den sidste celle, der har brug for formlen
- Slip museknappen, og klik på Udfyld uden formatering
Kombiner IF og VLOOKUP
Du kan bruge en IF-formel med en VLookup-formel for at returnere nøjagtige værdier, hvis de findes, og en tom streng hvis ikke fundet.
For at se trinene til opsætning af IF- og VLOOKUP-formlen kan du se denne korte video. De skriftlige instruktioner er under videoen.
For at skjule fejl ved at kombinere IF med VLOOKUP, følg disse trin:
- På fakturaarket i celle A8 skal du indtaste produktet kode A28. Hvis VLookup-formlen i celle B8 har FALSE som det fjerde argument, er resultatet # N / A, fordi der ikke er nogen nøjagtig matchning af produktkoden i opslagstabellen.
- Pak VLookup-formlen med en IF-formel (i dette eksempel er produktlisten blevet navngivet) ved hjælp af ISNA-funktionen til at kontrollere en # N / A-fejl:
- Tryk på Enter-tasten, og cellen vises tom. Da der ikke blev fundet nøjagtigt match, returnerede VLookup-formlen et # N / A, så ISNA-funktionsresultatet er SAND. IF-formlen konverterede dette til en tom streng.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), “”, VLOOKUP (A8, ProductList, 2, FALSE))
Kombiner IFERROR og VLOOKUP
Tak til Chip Pearson for at foreslå denne formel.
I Excel 2007 blev en ny funktion, IFERROR, introduceret . Du kan bruge en IFERROR-formel med VLookup til at kontrollere flere tabeller for en værdi og returnere de relaterede oplysninger, når de findes. I dette eksempel har tre regioner, Vest, Øst og Central, ordreblade. På hvert ark er et navngivet område – OrdersW, OrdersE og OrdersC.
På et ark med navnet Orders kan du indtaste et ordre-id og derefter bruge en VLOOKUP med IFERROR til at kontrollere hvert navngivet interval , og se oplysningerne om den valgte ordre.
- Indtast en 4 som ordre-id i ordrearket i celle B6. Denne ordre blev placeret i den centrale region.
- For blot at kontrollere østregionens tabel ville VLOOKUP-formlen i celle C6 være:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Tryk på Enter-tasten, og VLOOKUP-formlen returnerer et # N / A, fordi ordre-ID 4 ikke er i ordretabellen for østregioner.
- Fordi en ordre kunne have blevet placeret i en af de tre regioner, har du brug for en formel, der kontrollerer hver tabel.
Hvis ordre-idet ikke findes i den første tabel, skal formlen kontrollere anden tabel. Hvis ordre-idet ikke er i den anden tabel tabellen, skal den kontrollere den tredje tabel. Hvis ordre-idet ikke er “i den tredje tabel, skal en meddelelse” Ikke fundet “vises i cellen. - Formlen IFERROR giver dig mulighed for at kontrollere en værdi, angiv derefter, hvad der skal gøres, hvis der findes en fejl. Hvis du bruger IFERROR med den eksisterende formel, kan du vise “Ikke fundet” i stedet for # N / A-fejlen:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), “Not Found”) - For at kontrollere alle tre tabeller kan du næste formular IFERROR og VLOOKUP:
Dette kontrollerer OrdersE-tabellen, og hvis der findes en fejl, kontrollerer OrdersW-tabellen og derefter OrdersC. Hvis OrderID ikke findes i nogen af de tre tabeller, vises meddelelsen Ikke fundet i cellen.
Du kan også kontrollere flere tabeller i ældre versioner af Excel, hvor IFERROR ikke er tilgængelig ved hjælp af en længere formel:
VLOOKUP for kombinerede værdier
I nogle tabeller er der muligvis ikke unikke værdier enhver kolonne i opslagstabellen. I tabellen vist nedenfor er jakken f.eks. Anført to gange i kolonne A. Der er dog kun en rekord for hver jakke og størrelseskombination – jakke Medium i række 4 og jakke stor i række 5.
Hvis du har brug for at finde prisen på en stor jakke, returnerer en VLOOKUP kun på kolonne A prisen for den første jakke, der er anført (Medium ). Du ville være underpris for jakken – sælge den til 60,00 i stedet for 65,00.
For at skabe unikke opslagsværdier skal du kan indsætte en ny kolonne i venstre side af tabellen og bruge en formel til at kombinere produktet og størrelsen. I celle A2 kombinerer formlen værdien i B2 og rørtegnet og værdien i C2.
= B2 & “|” & C2
Kopier formlen ned til den sidste række data, så hver række har en unik værdi i kolonne A.
Bemærk : I stedet for rørtegnet kunne du bruge et andet tegn, der ikke er inkluderet i dine data.
Derefter i en VLOOKUP-formel, kombiner produktet og størrelsen som Lookup_value. I celle H1 kombinerer formlen værdien i F1 og rørtegnet og værdien i G1.
= VLOOKUP (F1 &” | “& G1, $ A $ 2: $ D $ 5,4, FALSE)
Bemærk: Prisen er nu i kolonne 4 i stedet for kolonne 3. ▲ TOP
Fejlfinding af VLOOKUP-formlen
Din VLOOKUP-formel returnerer muligvis et # N / A, selvom den værdi, du leder efter, ser ud til at være i opslagstabellen. Almindelige årsager til dette er:
Tekst vs. antal
Mellemrumstegn
Andre tegn
Tekst vs. tal
En almindelig årsag til denne fejl er, at en af værdierne er et tal, og den anden er tekst. For eksempel kan opslagstabellen indeholde “123 (tekst), og værdien, der skal slås op, er 123 (et tal).
- Hvis det er muligt, konverteres teksten til tal ved hjælp af et af metoder vist her: Konverter tekst til tal
- Hvis du ikke kan konvertere dataene, kan du konvertere opslagsværdien inden for VLOOKUP-formlen ved hjælp af en af nedenstående løsninger:
- Opslagsværdier er tekst, tabel har tal
- Opslagsværdier er tal, tabel har tekst
- For at finde ud af, hvilke værdier der er tekst, og hvilke der er tal, se det detaljerede antal eller tekstfejlfindingstrin
1. Opslagsværdier er tekst, og tabellen indeholder tal
Hvis opslagstabellen indeholder tal, og værdien, der skal slås op er tekst, skal du bruge en formel, der svarer til følgende:
= VLOOKUP (–A7, produkter!$ A $ 2: $ C $ 5,3, FALSE)
Den dobbelte unary (-) konverterer tekst til et tal og fungerer korrekt, selvom opslagsværdierne er tal.
2. Opslagsværdier er tal, og tabellen indeholder tekst
Hvis opslagstabellen indeholder tekst, og værdien, der skal slås op, er numerisk, skal du bruge en formel, der svarer til følgende:
= VLOOKUP (A7 & “”), Produkter! $ A $ 2: $ C $ 5,3, FALSK)
ELLER
= VLOOKUP (TEKST (A7, “00000”) , Produkter! $ A $ 2: $ C $ 5,3, FALSE)
TEKST-funktionen konverterer et tal til tekst og fungerer korrekt, selvom opslagsværdierne er tekst. I det første eksempel opretter operatøren & en tekststreng fra et uformateret nummer. I det andet eksempel matcher et tal, der er formateret med forreste nuller (f.eks. 00123), et tekst “nummer” med forudgående nuller.
For at se trinene til løsning af VLOOKUP-problemet, når opslagstabellen har tekstværdier, se denne korte videoundervisning.
Pladser i den ene værdi og ikke den anden
En anden potentiel årsag til, at der ikke findes nogen matchende værdi, er forskellen i mellemrum. En af værdierne kan indeholde førende mellemrum (eller bageste eller indlejrede mellemrum), og den anden ikke. For at teste værdierne kan du bruge LEN-funktionen til at kontrollere længden af hver værdi.
For eksempel: = LEN (A7) returnerer antallet af tegn i celle A7. Det skal være lig med antallet af tegn i den matchende celle i opslagstabellen.
Fjern om nødvendigt det unødvendige mellemrum, og VLOOKUP-formlen skal fungere korrekt. Hvis du ikke kan fjerne mellemrumene, skal du bruge TRIM-funktionen i VLOOKUP til at fjerne ledende, efterfølgende eller duplikerede mellemrum. For eksempel:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Andre tegn
Hvis TRIM-funktionen alene ikke løser problemet , kan du prøve et af følgende forslag:
- SUBSTITUTE-funktion
- RENE-funktion
- Makro til fjernelse af webtegn
SUBSTITUTE-funktion
Brug SUBSTITUTE-funktionen til at fjerne uønskede tegn. Der er et eksempel på Contextures-bloggen: Rengør Excel-data med TRIM og SUBSTITUTE
CLEAN-funktion
En anden måde at løse VLOOKUP-problemer på er med CLEAN-funktionen, som kan fjerne nogle uønskede tegn fra teksten. Der er flere oplysninger om CLEAN-funktionen i dette Contextures-blogindlæg: 30 Excel-funktioner på 30 dage: 29 – REN
Webtegn i den ene værdi og ikke den anden
Hvis du kopierede data fra en webside, kan de indeholde HTML-plads, der ikke bryder (& nbsp) tegn. David McRitchie har skrevet en makro for at fjerne dem sammen med o der mellemrumstegn
Problemer ved sortering af VLOOKUP-formel
En VLOOKUP-formel returnerer muligvis de korrekte resultater i starten, men viser derefter forkerte resultater, hvis listen over emner er sorteret. Dette kan forekomme, hvis henvisningen til opslagsværdien inkluderer et arknavn. For eksempel:
= VLOOKUP (“Bestillingsformular”! B5, produkter! $ B $ 2: $ C $ 6,2, FALSK)
BEMÆRK: Dette problem kan opstå med andre funktioner også, såsom en INDEX / MATCH-opslagsformel.
Se denne video for at se trinene til løsning af problemet, og download VLOOKUP Sorteringsproblemeksempelfilen for at følge med. De skriftlige instruktioner er under videoen.
Arknavne i reference
Denne type reference oprettes, hvis du klikker på et andet ark, mens du bygger formlen. Så snart du gør det, tilføjer Excel arknavnet til eventuelle efterfølgende referencer i formlen.
I skærmbilledet ovenfor er Dress i celle B9, og celle C9 viser den korrekte pris på $ 30.
Efter sortering af produkterne AZ bevæger Dressen sig imidlertid op til celle B5, men formlen i celle C5 refererer fortsat til celle B9. På grund af arknavne i referencerne bevarer Excel de originale referencer i stedet for at holde en henvisning til den aktuelle række. Cell C5 viser prisen på en sweater i stedet for en kjole. ▲ TOP
Løs problemet
For at løse problemet skal du fjerne unødvendige arknavne fra VLOOKUP cellereferencer. Her er den reviderede formel for celle C5:
= VLOOKUP (B5, Products! $ B $ 2: $ C $ 6,2, FALSE)
Når de unødvendige arknavne er fjernet, listen kan sorteres sikkert, og de korrekte resultater vises for hvert element.
Download prøvefiler
1. Hent VLOOKUP-eksempelarbejdsmappen (Excel 2007 og nyere). Den zip-fil er i xlsx-format og indeholder ingen makroer.
2. Til Excel 2003 skal du hente denne version af VLOOKUP-eksempelfilen. Den zip-fil er i xls-format og indeholder ingen makroer.
3. For at se det problem, der kan opstå, når du sorterer med VLOOKUP, skal du hente denne prøvefil med VLOOKUP-sorteringsproblemet. Den zip-fil er i xlsx-format og indeholder ingen makroer.
4. Hent produktprisopslag-projektmappen, der bruges i videoundervisningen.Den zip-fil er i xlsx-format og indeholder ingen makroer. ▲ TOP
Flere øvelser
VLOOKUP-nummer / tekstfejlfinding
VLOOKUP fra en anden projektmappe
HLOOKUP
VÆLG Funktion
Opslag – 2 kriterier
OPSLAG
INDEX / MATCH
Sammenlign opslagsfunktioner
Funktionsliste