Hur man använder en VLOOKUP-formel för att hämta data från en Excel-lista. Felsök VLOOKUP när saker går fel. Steg-för-steg-videor och Excel-arbetsböcker med VLOOKUP-exempel.
VLOOKUP-video
Se stegen för att skapa en VLOOKUP-formel i produktprissökningsvideon, se nedan. De skriftliga instruktionerna finns under videon. Få en exempelfil för produktprissökning som ska följas med videon (ladda ner fil 4).
Välj en plats för en uppslagstabell
Det är en bra idé att lagra varje uppslag när du lägger till och tar bort rader i uppslagstabellerna kommer du inte av misstag att lägga till eller ta bort rader i någon annan tabell.
I det här exemplet, uppslagstabellen finns på ett ark med namnet Produkter.
Skapa en uppslagstabell
Uppslagsformler kan fungera vertikalt, leta efter värden i en kolumn eller de kan arbeta horisontellt och leta efter värden över en rad . I detta exempel kommer informationen att lagras vertikalt, med värden nere i en kolumn, och senare kommer vi att använda en VLookup-formel för att göra en vertikal uppslagning.
- Ange rubrikerna i första raden
- Den första kolumnen ska innehålla de unika nyckelvärdena som du kommer att basera uppslaget på. I det här exemplet hittar du pris för en specifik produktkod.
- Om du har andra uppgifter i kalkylbladet, lämna minst en tom rad längst ner i tabellen och en tom kolumn till höger om tabellen för att separera uppslagstabell från andra data.
Obs: För att göra det lättare att hänvisa till tabellen kan du namnge intervallet. Det finns instruktioner här: Namnge ett intervall ▲ TOP
VLOOKUP-funktionsargument
VLOOKUP-funktionen har fyra argument:
- lookup_value : Vilket värde vill du slå upp? I det här exemplet finns produktkoden i cell A7 och du vill hitta dess pr produktnamn.
- table_array: Var är uppslagstabellen? Om du använder en absolut referens ($ A $ 2: $ C $ 5), istället för en relativ referens (A2: C5), blir det lättare att kopiera till formel till andra celler. Eller namnge uppslagstabellen och hänvisa till den med namn.
- col_index_num: Vilken kolumn har det värde du vill returnera? I det här exemplet finns produktnamnen i den andra kolumnen i uppslagstabellen.
- : Vill du ha en exakt matchning? Är en ungefärlig match okej?
Om du använder SANT som det sista argumentet eller utelämnar det sista argumentet kan en ungefärlig matchning returneras. Detta exempel har FALSE som det sista argumentet, så om produktkoden inte hittas blir resultatet # Ej tillämpligt. (Obs: Excel är ganska förlåtande och accepterar 0 istället för FALSKT och 1 istället för SANT.) ▲ TOPP
Skapa en VLookup-formel
När du har skapat uppslagstabellen kan du skapa andra formler i arbetsboken och hämta information från produktlistan. Till exempel, för att förbereda en faktura kan du ange en produktkod och formler får produktnamnet eller priset från produkttabellen.
I det här exemplet skapas fakturan på ett ark som heter Faktura. VLOOKUP-formeln ska hitta en exakt matchning för produktkoden och returnera produktnamnet.
För att skapa VLOOKUP-formeln som beräknar produktpriset, följ dessa steg:
- Välj fakturablad
- Ange produktkod A23 i cell A7
- Starta VLOOKUP-formeln i cell B7:
= VLOOKUP ( - Klicka på cell A7 för att ange sin referens.
- Skriv ett kommatecken för att avsluta det första argumentet
- Välj produktbladet
- Markera cellerna A2: C5 (produktlistan )
- Tryck på F4-tangenten för att ändra cellreferenser till absoluta. Formeln ska nu se ut så här:
= VLOOKUP (A7, produkter! $ A $ 2: $ C $ 5 - Skriv ett komma för att avsluta det andra argumentet.
- Skriv ett 2, som är kolumnen i uppslagstabellen som innehåller produktnamnet.
- Skriv ett komma för att avsluta tredje argumentet.
- Skriv FALSE för att ange att en exakt matchning för produktkoden hittas och lägg till stängningsfästet.
- Formeln ska nu se ut l som detta:
= VLOOKUP (A7, produkter! $ A $ 2: $ C $ 5,2, FALSKT) - Tryck på Enter-tangenten för att slutföra formeln. Produktnamnet visas.
Obs! Om du vill returnera produktpriset skapar du en VLOOKUP-formel som hänvisar till kolumn 3 i uppslagstabellen. Ange till exempel följande formel i cell C7:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOP
VLOOKUP Formula for Range of Values
I vissa situationer föredras en ungefärlig matchning, så flera värden kommer att ge samma resultat. Till exempel, när man betygsätter studentpapper, ska alla papper med betyget 85 eller högre få A-betyg.
För att se stegen för att skapa denna formel, se VLOOKUP-videon som visas nedan. De skriftliga instruktionerna finns under videon.
I det här exemplet skapas uppslagstabellen på ett ark som heter Betyg. För att skapa uppslagstabellen anger du lägsta poäng för varje betyg i kolumn A. Ange matchande betyg i kolumn B. Sortera poängen i stigande ordning.
Cellerna A2: B6 fick namnet GradeList.
Poängen anges på ett ark som heter Report Card, där en VLOOKUP-formel beräknar betyget.
I skärmdump nedan har formeln kopierats ner till rad 6 och du kan se formeln i cell C6.
Kombinera VLOOKUP med MATCH
I stället för att skriva kolumnnumret i en VLOOKUP-formel, använd MATCH-funktionen för att hitta rätt kolumn i uppslagstabellen. Detta har ett par fördelar:
- Gör formeln flexibel, så det är lättare att kopiera formeln över ett kalkylblad.
- Kan förhindra problem om nya kolumner läggs till i uppslagstabellen, eller om uppslagskolumnerna är ordnade.
Den här videon visar stegen, och det finns skriftliga instruktioner för ett annat exempel, nedanför videon.
Din webbläsare kan inte visa den här ramen. Här är en länk till sidan
VLOOKUP With MATCH for Order Details
I detta exempel returnerar en VLOOKUP-formel orderdetaljer från en uppslagstabell, baserat på order-ID-numret . Här är uppslagstabellen med namnet tblOrders.
OBS: Det här exemplet finns i exempelarbetsbok 1 på arket som heter OrdersMATCH.
Här är kalkylbladet med VLOOKUP-formlerna. Vi vill ha region, orderdatum och orderbelopp för varje beställning, så 3 VLOOKUP-formler behövs.
Om kolumnnumren skrivs i formeln krävs en annan formel i varje kolumn:
- Region: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
MATCH-funktionen
I stället för att skriva kolumnnumret i VLOOKUP-formeln kan vi använda MATCH-funktionen. MATCH-funktionen hittar positionen för ett objekt i en lista och returnerar positionsnumret.
I skärmbilden nedan returnerar MATCH-formeln 2 som position för ”Region”, i rubrikcellerna (A1: D1) för uppslagstabellen.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
OBS: För att denna teknik ska fungera korrekt, rubriker på VLOOKUP-arket måste matcha rubrikerna för uppslagstabellen exakt. För att säkerställa en exakt matchning är VLOOKUP-rubrikcellerna länkade till rubrikcellerna för uppslagstabellen.
Lägg till MATCH till VLOOKUP
För att lägga till MATCH-funktionen till VLOOKUP-formeln, ersätt bara skrivit kolumnnummer
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
med MATCH-formeln:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Kopiera VLOOKUP-formeln över
Nu, istället för att behöva en annan formel i varje kolumn, kan du kopiera formeln över och använda samma formel i alla kolumner. I varje kolumn kommer den att hänvisa till rubrikcellen i den kolumnen och hitta sin position i uppslagstabellen.
OBS: Om du fyller formeln över kolumner med olika formatering, följ dessa steg:
- Markera cellen med formeln som du vill kopiera
- Peka på fyllningshandtaget på den valda cellen (liten kvadrat längst ned till höger)
- Tryck på HÖGER musknappen och dra över till den sista cellen som behöver formeln
- Släpp musknappen och klicka på Fyll utan formatering
Kombinera IF och VLOOKUP
Du kan använda en IF-formel med en VLookup-formel för att returnera exakta värden om de hittas och en tom sträng om det inte hittas.
Om du vill se stegen för att konfigurera IF- och VLOOKUP-formeln kan du titta på den här korta videon. De skriftliga instruktionerna finns under videon.
För att dölja fel genom att kombinera IF med VLOOKUP, följ dessa steg:
- På fakturabladet, i cell A8, ange produkten kod A28. Om VLookup-formeln i cell B8 har FALSE som det fjärde argumentet är resultatet # N / A, eftersom det inte finns någon exakt matchning för produktkoden i uppslagstabellen.
- Vik in VLookup-formeln med en IF-formel (i det här exemplet har produktlistan fått namnet), med hjälp av ISNA-funktionen för att söka efter ett # N / A-fel:
- Tryck på Enter-tangenten och cellen visas tom. Eftersom ingen exakt matchning hittades returnerade VLookup-formeln ett # N / A, så resultatet av ISNA-funktionen är SANT. IF-formeln konverterade detta till en tom sträng.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), ””, VLOOKUP (A8, ProductList, 2, FALSE))
Kombinera IFERROR och VLOOKUP
Tack till Chip Pearson för att föreslå den här formeln.
I Excel 2007 introducerades en ny funktion, IFERROR . Du kan använda en IFERROR-formel med VLookup för att kontrollera flera tabeller för ett värde och returnera relaterad information när den hittas. I det här exemplet har tre regioner, västra, östra och centrala, orderblad. På varje ark finns ett namngivet intervall – OrdersW, OrdersE och OrdersC.
På ett ark med namnet Orders kan du ange ett order-ID och sedan använda en VLOOKUP med IFERROR för att kontrollera varje namngivet intervall och visa informationen om den valda ordern.
- I orderbladet, i cell B6, ange en 4 som OrderID. Ordern placerades i den centrala regionen.
- För att helt enkelt kontrollera östregionens tabell skulle VLOOKUP-formeln i cell C6 vara:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Tryck på Enter-tangenten och VLOOKUP-formeln returnerar ett # N / A, eftersom order-ID 4 inte finns i ordningstabellen för östra regioner.
- Eftersom en order kan ha har placerats i någon av de tre regionerna behöver du en formel som kontrollerar varje tabell.
Om order-ID inte finns i den första tabellen ska formeln kontrollera andra tabellen. Om order-ID inte finns i den andra tabellen borde den kontrollera den tredje tabellen. Om order-ID inte är ”i den tredje tabellen, ska ett meddelande” Inte hittat ”visas i cellen. - IFERROR-formeln låter dig kontrollera ett värde, ange sedan vad du ska göra om ett fel hittas. Om du använder IFERROR med den befintliga formeln kan du visa ”Not Found” istället för # N / A-felet:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), ”Not Found”) - För att kontrollera alla tre tabellerna kan du nästa formel IFERROR och VLOOKUP:
Detta kontrollerar OrdersE-tabellen och om ett fel hittas kontrollerar du OrdersW-tabellen och sedan OrdersC. Om OrderID inte finns i någon av de tre tabellerna visas meddelandet Not Found i cellen.
Du kan också kontrollera flera tabeller i äldre versioner av Excel, där IFERROR inte är tillgängligt med en längre formel:
VLOOKUP för kombinerade värden
I vissa tabeller kanske det inte finns unika värden valfri kolumn i uppslagstabellen. Till exempel, i tabellen nedan, är jackan listad två gånger i kolumn A. Det finns dock bara en post för varje jacka och storlekskombination – Jacka Medium i rad 4 och Jacka Large i rad 5.
Om du behöver hitta priset för en stor jacka, skulle en VLOOKUP baserad endast på kolumn A returnera priset för den första jackan som anges (Medium ). Du skulle sänka priset på jackan – sälja den till 60,00 istället för 65,00.
För att skapa unika uppslagsvärden, du kan infoga en ny kolumn till vänster i tabellen och använda en formel för att kombinera produkten och storleken. I cell A2 kombinerar formeln värdet i B2 och rörtecken och värdet i C2.
= B2 & ”|” & C2
Kopiera formeln till den sista raden med data så att varje rad har ett unikt värde i kolumn A.
Obs : Istället för rörtecken kan du använda ett annat tecken som inte ingår i dina data.
Sedan, i en VLOOKUP-formel, kombinera produkten och storleken som Lookup_value. I cell H1 kombinerar formeln värdet i F1 och rörtecknet och värdet i G1.
= VLOOKUP (F1 &” | ”& G1, $ A $ 2: $ D $ 5,4, FALSE)
Obs: Priset är nu i kolumn 4 i stället för kolumn 3. ▲ TOP
Felsök VLOOKUP-formeln
Din VLOOKUP-formel kan returnera ett # N / A, även om värdet du letar efter verkar ligga i uppslagstabellen. Vanliga orsaker till detta är:
Text kontra nummer
Mellanslagstecken
Andra tecken
Text kontra nummer
En vanlig orsak till detta fel är att ett av värdena är ett tal och det andra är text. Till exempel kan uppslagstabellen innehålla ”123 (text) och värdet att slå upp är 123 (ett nummer).
- Om möjligt, konvertera texten till siffror med en av metoder som visas här: Konvertera text till siffror
- Om du inte kan konvertera data kan du konvertera uppslagsvärdet i VLOOKUP-formeln med hjälp av en av lösningarna nedan:
- Uppslagsvärden är text, tabell har siffror
- Sökvärden är siffror, tabell har text
- För att ta reda på vilka värden som är text och vilka som är siffror, se det detaljerade felsökningssteget för antal eller text
1. Uppslagsvärden är text och tabellen innehåller siffror
Om uppslagstabellen innehåller siffror och värdet att slå upp är text, använd en formel som liknar följande:
= VLOOKUP (–A7, produkter!$ A $ 2: $ C $ 5,3, FALSE)
Den dubbla unaryen (-) omvandlar text till ett tal och fungerar korrekt även om uppslagsvärdena är siffror.
2. Uppslagsvärden är siffror och tabellen innehåller text
Om uppslagstabellen innehåller text och värdet att slå upp är numeriskt, använd en formel som liknar följande:
= VLOOKUP (A7 & ””), Produkter! $ A $ 2: $ C $ 5,3, FALSE)
ELLER
= VLOOKUP (TEXT (A7, ”00000”) , Produkter! $ A $ 2: $ C $ 5,3, FALSE)
Funktionen TEXT konverterar ett tal till text och fungerar korrekt även om uppslagsvärdena är text. I det första exemplet skapar operatorn & en textsträng från ett opormerat nummer. I det andra exemplet skulle ett tal formaterat med ledande nollor (t.ex. 00123) matcha ett text ”nummer” med ledande nollor.
För att se stegen för att åtgärda VLOOKUP-problemet när uppslagstabellen har textvärden, titta på den här korta videohandledningen.
Mellanslag i ett värde och inte i det andra
En annan potentiell orsak till att inget matchande värde hittas är en skillnad i mellanslag. Ett av värdena kan innehålla ledande mellanslag (eller efterföljande eller inbäddade mellanslag), och det andra inte. För att testa värdena kan du använda LEN-funktionen för att kontrollera längden på varje värde.
Till exempel: = LEN (A7) returnerar antalet tecken i cell A7. Det ska vara lika med antalet tecken i matchande cell i uppslagstabellen.
Om möjligt, ta bort det onödiga mellanslag och VLOOKUP-formeln ska fungera korrekt. Om du inte kan ta bort mellanslag använder du TRIM-funktionen i VLOOKUP för att ta bort ledande, efterföljande eller duplicerade mellanslag. Till exempel:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Andra tecken
Om TRIM-funktionen ensam inte löser problemet kan du prova något av följande förslag:
- SUBSTITUTE-funktion
- CLEAN-funktion
- Makro för att ta bort webbkaraktärer
SUBSTITUTE-funktion
Använd SUBSTITUTE-funktionen för att ta bort oönskade tecken. Det finns ett exempel på Contextures-bloggen: Rengör Excel-data med TRIM och SUBSTITUT
CLEAN-funktion
Ett annat sätt att åtgärda VLOOKUP-problem är med CLEAN-funktionen, som kan ta bort oönskade tecken från texten. Det finns mer information om CLEAN-funktionen i detta Contextures-blogginlägg: 30 Excel-funktioner på 30 dagar: 29 – CLEAN
Webbtecken i ett värde och inte det andra
Om du har kopierat data från en webbsida kan det innehålla HTML-utrymme utan brott (& nbsp) tecken. David McRitchie har skrivit ett makro för att ta bort dem, tillsammans med o det finns mellanslagstecken
Problem vid sortering av VLOOKUP-formel
En VLOOKUP-formel kanske ger rätt resultat först, men visar sedan felaktiga resultat om listan med objekt sorteras. Detta kan inträffa om referensen till uppslagsvärdet innehåller ett arknamn. Till exempel:
= VLOOKUP (”Beställningsformulär”! B5, Produkter! $ B $ 2: $ C $ 6,2, FALSKT)
OBS: Detta problem kan uppstå med andra funktioner också, till exempel en INDEX / MATCH-uppslagsformel.
Titta på den här videon för att se stegen för att åtgärda problemet och ladda ner VLOOKUP Sorteringsprovfilen för att följa med. De skriftliga instruktionerna finns under videon.
Bladnamn i referens
Denna typ av referens skapas om du klickar på ett annat ark medan du bygger formeln. Så fort du gör det lägger Excel till arkenamnet till alla efterföljande referenser i formeln.
I skärmdumpen ovan är klädsel i cell B9 och cell C9 visar det rätta priset på $ 30.
Men efter sortering av produkterna AZ flyttar klänningen upp till cell B5, men formeln i cell C5 fortsätter att referera till cell B9. På grund av arknamnen i referenserna behåller Excel originalreferenserna istället för att behålla en referens till den aktuella raden. Cell C5 visar priset för en tröja istället för en klänning. ▲ TOPP
Åtgärda problemet
Lös problemet genom att ta bort onödiga arknamn från VLOOKUP-cellreferenser. Här är den reviderade formeln för cell C5:
= VLOOKUP (B5, Products! $ B $ 2: $ C $ 6,2, FALSE)
När de onödiga arknamnen har tagits bort, listan kan sorteras säkert och de korrekta resultaten visas för varje objekt.
Ladda ner provfilerna
1. Skaffa VLOOKUP-exempelarbetsboken (Excel 2007 och senare). Den zippade filen är i xlsx-format och innehåller inga makron.
2. För Excel 2003, hämta den här versionen av exempelfilen VLOOKUP. Den zippade filen är i xls-format och innehåller inga makron.
3. För att se det problem som kan uppstå när du sorterar med VLOOKUP, hämta den här VLOOKUP-sorteringsprofilfilen. Den zippade filen är i xlsx-format och innehåller inga makron.
4. Skaffa arbetsbok för produktprissökning, som används i videohandledningen.Den zippade filen är i xlsx-format och innehåller inga makron. ▲ TOPP
Fler självstudier
VLOOKUP Antal / textfelsökning
VLOOKUP från en annan arbetsbok
HLOOKUP
VÄLJ Funktion
Sökning – 2 kriterier
SÖKNING
INDEX / MATCH
Jämför uppslagningsfunktioner
Funktionslista