VLOOKUP -funktion - Introduktion
VLOOKUP -funktion är riktmärket.
Du kan något i Excel om du vet hur du använder VLOOKUP -funktionen.
Om du inte gör det bör du inte lista Excel som ett av dina starka områden i ditt CV.
Jag har varit en del av panelintervjuerna där så snart kandidaten nämnde Excel som sitt expertområde var det första du frågade - du fick det - VLOOKUP -funktionen.
Nu när vi vet hur viktig denna Excel -funktion är, är det vettigt att se det helt för att stolt kunna säga - "Jag kan en sak eller två i Excel".
Detta kommer att bli en massiv VLOOKUP -handledning (enligt mina standarder).
Jag kommer att täcka allt som finns att veta om det och sedan visa dig användbara och praktiska VLOOKUP -exempel.
Så spänn dig.
Det är dags för start.
När ska man använda VLOOKUP -funktionen i Excel?
VLOOKUP -funktionen är bäst lämpad för situationer när du letar efter en matchande datapunkt i en kolumn, och när matchande datapunkt hittas går du till höger på den raden och hämtar ett värde från en cell som är ett visst antal kolumner till höger.
Låt oss ta ett enkelt exempel här för att förstå när du ska använda Vlookup i Excel.
Kom ihåg när tentamenslistan var ute och klistrades på anslagstavlan och alla brukade bli galen med att hitta sina namn och deras poäng (åtminstone det var vad som brukade hända när jag var i skolan).
Så här fungerade det:
- Du går upp till anslagstavlan och börjar leta efter ditt namn eller registreringsnummer (kör fingret uppifrån och ned i listan).
- Så snart du upptäcker ditt namn flyttar du ögonen till höger om namnet/registreringsnumret för att se dina poäng.
Och det är precis vad Excel VLOOKUP -funktionen gör för dig (använd gärna detta exempel i din nästa intervju).
VLOOKUP -funktionen letar efter ett angivet värde i en kolumn (i exemplet ovan var det ditt namn) och när den hittar den angivna matchningen returnerar den ett värde på samma rad (märkena du fick).
Syntax
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Inmatningsargument
- lookup_value - detta är det sökningsvärde du försöker hitta i kolumnen längst till vänster i en tabell. Det kan vara ett värde, en cellreferens eller en textsträng. I exemplet med poängbladet skulle detta vara ditt namn.
- tabell_array - detta är tabellmatrisen där du letar efter värdet. Detta kan vara en referens till ett cellområde eller ett namngivet område. I exempelbladet är detta hela tabellen som innehåller poäng för alla för varje ämne
- col_index - detta är kolumnindexnumret från vilket du vill hämta matchningsvärdet. Om du vill ha poängen för matematik (som är den första kolumnen i en tabell som innehåller poängen) i exempelbladet, ser du i kolumn 1. Om du vill ha poängen för fysik ser du i kolumnen 2.
- [range_lookup] - här anger du om du vill ha en exakt matchning eller en ungefärlig matchning. Om den utelämnas är den som standard SANN - ungefärlig matchning (se ytterligare anmärkningar nedan).
Ytterligare anteckningar (tråkigt, men viktigt att veta)
- Matchen kan vara exakt (FALSK eller 0 i intervallet_uppslag) eller ungefärlig (SANT eller 1).
- I ungefärlig sökning, se till att listan är sorterad i stigande ordning (topp till botten), annars kan resultatet bli felaktigt.
- När range_lookup är TRUE (ungefärlig sökning) och data sorteras i stigande ordning:
- Om VLOOKUP -funktionen inte kan hitta värdet returnerar det det största värdet, vilket är mindre än lookup_value.
- Det returnerar ett #N/A -fel om lookup_value är mindre än det minsta värdet.
- Om lookup_value är text kan jokertecken användas (se exemplet nedan).
Nu, i hopp om att du har en grundläggande förståelse för vad VLOOKUP -funktionen kan göra, låt oss skala den här löken och se några praktiska exempel på VLOOKUP -funktionen.
10 Excel VLOOKUP -exempel (grundläggande och avancerade)
Här är 10 användbara exempel på hur du använder Excel Vlookup som visar dig hur du använder det i ditt dagliga arbete.
Exempel 1 - Hitta Brad's Math Score
I VLOOKUP-exemplet nedan har jag en lista med elevnamn i kolumnen längst till vänster och markeringar i olika ämnen i kolumnerna B till E.
Låt oss nu börja arbeta och använda VLOOKUP -funktionen för vad den gör bäst. Från ovanstående data måste jag veta hur mycket Brad gjorde i matematik.
Från ovanstående data måste jag veta hur mycket Brad gjorde i matematik.
Här är VLOOKUP -formeln som returnerar Brads matematikpoäng:
= VLOOKUP ("Brad", $ A $ 3: $ E $ 10,2,0)
Ovanstående formel har fyra argument:
- "Brad: - det här är sökvärdet.
- $ A $ 3: $ E $ 10 - det här är cellintervallet som vi letar efter. Kom ihåg att Excel letar efter uppslagsvärdet i kolumnen längst till vänster. I det här exemplet skulle det leta efter namnet Brad i A3: A10 (vilket är kolumnen längst till vänster i den angivna matrisen).
- 2 - När funktionen upptäcker Brads namn går den till den andra kolumnen i matrisen och returnerar värdet i samma rad som Brad. Värdet 2 här indikerar att vi letar efter poängen från den andra kolumnen i den angivna matrisen.
- 0 - detta berättar VLOOKUP -funktionen att bara leta efter exakta matchningar.
Så här fungerar VLOOKUP -formeln i exemplet ovan.
Först letar det efter värdet Brad i kolumnen längst till vänster. Den går uppifrån och ner och hittar värdet i cell A6.
Så snart det hittar värdet går det till höger i den andra kolumnen och hämtar värdet i det.
Du kan använda samma formelkonstruktion för att få någons betyg i något av ämnena.
Till exempel, för att hitta Marias betyg i kemi, använd följande VLOOKUP -formel:
= VLOOKUP ("Maria", $ A $ 3: $ E $ 10,4,0)
I exemplet ovan anges uppslagsvärdet (elevens namn) i dubbla citattecken. Du kan också använda en cellreferens som innehåller uppslagsvärdet.
Fördelen med att använda en cellreferens är att den gör formeln dynamisk.
Om du till exempel har en cell med en elevs namn och du hämtar poängen för matematik uppdateras resultatet automatiskt när du ändrar elevens namn (som visas nedan):
Om du anger ett uppslagsvärde som inte finns i kolumnen längst till vänster returnerar det ett #N/A-fel.
Exempel 2 - Tvåvägsuppslag
I exempel 1 ovan hårdkodade vi kolumnvärdet. Därför skulle formeln alltid returnera poängen för matematik eftersom vi har använt 2 som kolumnindexnummer.
Men vad händer om du vill göra både VLOOKUP -värdet och kolumnindexnumret dynamiskt. Till exempel, som visas nedan, kan du ändra antingen elevnamnet eller ämnesnamnet, och VLOOKUP -formeln hämtar rätt poäng. Detta är ett exempel på en tvåvägs VLOOKUP-formel.
Detta är ett exempel på en tvåvägs VLOOKUP-funktion.
För att göra denna tvåvägssökningsformel måste du också göra kolumnen dynamisk. Så när en användare byter ämne väljer formeln automatiskt rätt kolumn (2 i fråga om matematik, 3 när det gäller fysik, osv.).
För att göra detta måste du använda funktionen MATCH som kolumnargument.
Här är VLOOKUP -formeln som gör detta:
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Ovanstående formel använder MATCH (H3, $ A $ 2: $ E $ 2,0) som kolumnnummer. MATCH -funktionen tar ämnesnamnet som uppslagsvärde (i H3) och returnerar sin position i A2: E2. Därför, om du använder matematik, skulle det returnera 2 eftersom matematik finns i B2 (som är den andra cellen i det angivna matrisområdet).
Exempel 3 - Använda rullgardinslistor som uppslagsvärden
I exemplet ovan måste vi ange data manuellt. Det kan vara tidskrävande och felbenäget, särskilt om du har en enorm lista med uppslagsvärden.
En bra idé i sådana fall är att skapa en rullgardinslista med uppslagsvärden (i det här fallet kan det vara elevnamn och ämnen) och sedan helt enkelt välja från listan.
Baserat på valet skulle formeln uppdatera resultatet automatiskt.
Något som visas nedan:
Detta är en bra instrumentpanelskomponent eftersom du kan ha en enorm datauppsättning med hundratals elever i bakänden, men slutanvändaren (låt oss säga en lärare) kan snabbt få betyg på en elev i ett ämne genom att helt enkelt göra val från rullgardinsmenyn.
Så här gör du:
VLOOKUP -formeln som används i detta fall är samma som i exempel 2.
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Sökvärdena har konverterats till listrutor.
Här är stegen för att skapa rullgardinsmenyn:
- Välj den cell där du vill ha listrutan. I det här exemplet, i G4, vill vi ha studentnamnen.
- Gå till Data -> Dataverktyg -> Datavalidering.
- I rutan Datavalidering, på fliken Inställningar, välj Lista på rullgardinsmenyn Tillåt.
- Välj $ A $ 3: $ A $ 10 i källan
- Klicka på OK.
Nu har du rullgardinsmenyn i cell G4. På samma sätt kan du skapa en i H3 för ämnena.
Exempel 4 - Trevägsuppslag
Vad är en trevägsuppslagning?
I exempel 2 har vi använt en uppslagstabell med poäng för elever i olika ämnen. Detta är ett exempel på en tvåvägsuppslagning eftersom vi använder två variabler för att hämta poängen (elevens namn och ämnets namn).
Antag nu att om ett år har en student tre olika nivåer av tentor, enhetstest, halvtids- och slutprov (det var vad jag hade när jag var student).
En trevägsuppslagning skulle vara möjligheten att få en elevs betyg för ett visst ämne från den angivna tentamen.
Något som visas nedan:
I exemplet ovan kan funktionen VLOOKUP leta upp i tre olika tabeller (enhetstest, halvtid och slutprov) och returnerar poängen för den angivna eleven i det angivna ämnet.
Här är formeln som används i cell H4:
= VLOOKUP (G4, VÄLJ (IF (H2 = "Unit Test", 1, IF (H2 = "Midterm", 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Denna formel använder funktionen VÄLJ för att se till att rätt tabell refereras till. Låt oss analysera VÄLJ delen av formeln:
VÄLJ (IF (H2 = ”Unit Test”, 1, IF (H2 = ”Midterm”, 2,3)), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )
Det första argumentet med formeln är IF (H2 = ”Unit Test”, 1, IF (H2 = ”Midterm”, 2,3)), som kontrollerar cellen H2 och ser vilken examenivå som refereras till. Om det är enhetstest returnerar det $ A $ 3: $ E $ 7, som har poängen för enhetstest. Om det är Midterm, returnerar det $ A $ 11: $ E $ 15, annars returnerar det $ A $ 19: $ E $ 23.
Om du gör detta gör VLOOKUP-tabellmatrisen dynamisk och gör det därför till en trevägsuppslagning.
Exempel 5 - Få det sista värdet från en lista
Du kan skapa en VLOOKUP -formel för att få det sista numeriska värdet från en lista.
Det största positiva talet som du kan använda i Excel är 9.99999999999999E+307. Detta innebär också att det största uppslagningsnumret i VLOOKUP -numret också är detsamma.
Jag tror inte att du någonsin skulle behöva någon beräkning med ett så stort antal. Och det är precis vad vi kan använda för att få det sista numret i en lista.
Anta att du har en dataset (i A1: A14) som visas nedan och du vill få det sista numret i listan.
Här är formeln du kan använda:
= VLOOKUP (9.99999999999999E+307, $ A $ 1: $ A $ 14,SANN)
Observera att formeln ovan använder en ungefärlig matchning VLOOKUP (märk SANT i slutet av formeln, istället för FALSKT eller 0). Observera också att listan inte behöver sorteras för att denna VLOOKUP -formel ska fungera.
Så här fungerar den ungefärliga VLOOKUP -funktionen. Den skannar den vänstra kolumnen uppifrån och ner.
- Om den hittar en exakt matchning returnerar det värdet.
- Om den hittar ett värde som är högre än uppslagsvärdet returnerar det värdet i cellen ovanför det.
- Om uppslagsvärdet är större än alla värden i listan returnerar det det sista värdet.
I exemplet ovan är det tredje scenariot på jobbet.
Eftersom 9.99999999999999E+307 är det största antalet som kan användas i Excel, när detta används som uppslagsvärde returnerar det det sista numret från listan.
På samma sätt kan du också använda det för att returnera det sista textobjektet från listan. Här är formeln som kan göra det:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,SANN)
Samma logik följer. Excel tittar igenom alla namn, och eftersom zzz anses vara större än något namn/text som börjar med alfabet före zzz, skulle det returnera det sista objektet från listan.
Exempel 6 - Delvis sökning med jokertecken och VLOOKUP
Excel -jokertecken kan vara till stor hjälp i många situationer.
Det är den magiska potionen som ger dina formler superkrafter.
Delvis sökning behövs när du måste leta efter ett värde i en lista och det inte finns någon exakt matchning.
Anta till exempel att du har en datamängd som visas nedan, och du vill leta efter företaget ABC i en lista, men listan har ABC Ltd istället för ABC.
Du kan inte använda ABC som uppslagsvärde eftersom det inte finns någon exakt matchning i kolumn A. Ungefärlig matchning leder också till felaktiga resultat och det kräver att listan sorteras i stigande ordning.
Du kan dock använda ett jokertecken inom VLOOKUP -funktionen för att få matchningen.
Ange följande formel i cell D2 och dra den till de andra cellerna:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Hur fungerar denna formel?
I formeln ovan, istället för att använda uppslagsvärdet som det är, flankeras det på båda sidor med jokertecknet asterisk (*) - “*” & C2 & ”*”
En asterisk är ett jokertecken i Excel och kan representera valfritt antal tecken.
Att använda asterisken på båda sidor av uppslagsvärdet talar om för Excel att den måste leta efter text som innehåller ordet i C2. Det kan ha valfritt antal tecken före eller efter texten i C2.
Till exempel har cell C2 ABC, så VLOOKUP -funktionen letar igenom namnen i A2: A8 och söker efter ABC. Den hittar en matchning i cell A2, eftersom den innehåller ABC i ABC Ltd. Det spelar ingen roll om det finns några tecken till vänster eller höger om ABC. Tills det finns ABC i en textsträng, kommer det att betraktas som en matchning.
Obs: VLOOKUP -funktionen returnerar alltid det första matchningsvärdet och slutar leta vidare. Så om du har ABC Ltd., och ABC Corporation i en lista, kommer den att returnera den första och ignorera resten.
Exempel 7 - VLOOKUP Returnera ett fel trots en matchning i uppslagsvärde
Det kan göra dig galen när du ser att det finns ett matchande uppslagsvärde och VLOOKUP -funktionen returnerar ett fel.
I fallet nedan finns det till exempel en matchning (Matt), men VLOOKUP -funktionen returnerar fortfarande ett fel.
Medan vi kan se att det finns en match, är det vi inte kan se med blotta ögat att det kan finnas ledande eller bakre utrymmen. Om du har dessa extra mellanslag före, efter eller mellan uppslagsvärdena är det INTE en exakt matchning.
Detta är ofta fallet när du importerar data från en databas eller får den från någon annan. Dessa ledande/bakre utrymmen har en tendens att smyga in.
Lösningen här är TRIM -funktionen. Det tar bort alla ledande eller bakre mellanslag eller extra mellanslag mellan ord.
Här är formeln som ger dig rätt resultat.
= VLOOKUP ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Eftersom detta är en matrisformel använder du Ctrl + Skift + Retur istället för bara Enter.
Ett annat sätt kan vara att först behandla din uppslagsmatris med TRIM -funktionen för att se till att alla extra mellanslag är borta och sedan använda VLOOKUP -funktionen som vanligt.
Exempel 8 - Gör en fallkänslig sökning
Som standard är uppslagsvärdet i VLOOKUP -funktionen inte skiftlägeskänsligt. Till exempel, om ditt uppslagsvärde är MATT, matt eller Matt, är det samma sak för VLOOKUP -funktionen. Det returnerar det första matchningsvärdet oavsett fall.
Men om du vill göra en skiftlägeskänslig sökning måste du använda EXAKT-funktionen tillsammans med VLOOKUP-funktionen.
Här är ett exempel:
Som du kan se finns det tre celler med samma namn (i A2, A4 och A5) men med ett annat alfabetfall. Till höger har vi de tre namnen (Matt, MATT och matt) tillsammans med sina poäng i matematik.
Nu är VLOOKUP-funktionen inte utrustad för att hantera skiftlägeskänsliga uppslagsvärden. I det ovanstående exemplet skulle det alltid returnera 38, vilket är poängen för Matt i A2.
För att göra det skiftlägeskänsligt måste vi använda en hjälparkolumn (som visas nedan):
För att få värdena i hjälpkolumnen, använd funktionen = RAD (). Det får helt enkelt radnumret i cellen.
När du väl har hjälpkolumnen är här formeln som ger det skiftlägeskänsliga sökresultatet.
= VLOOKUP (MAX (EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
Låt oss nu bryta ner och förstå vad detta gör:
- EXAKT (E2, $ A $ 2: $ A $ 9) - Denna del skulle jämföra uppslagsvärdet i E2 med alla värden i A2: A9. Det returnerar en matris med SANT/FALS där SANT returneras där det finns en exakt matchning. I det här fallet skulle det returnera följande array: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9) - Denna del multiplicerar matrisen SANT/FALS med radnumret. Överallt där det finns ett SANT, ger det radnumret , annars ger det 0. I det här fallet skulle det returnera {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXAKT (E2, $ A $ 2: $ A $ 9)*(RAD ($ A $ 2: $ A $ 9)))) - Denna del returnerar det maximala värdet från siffran. I det här fallet skulle det returnera 2 (vilket är radnumret där det finns en exakt matchning).
- Nu använder vi helt enkelt det här numret som uppslagsvärde och använder uppslagsmatrisen som B2: C9
Obs! Eftersom detta är en matrisformel använder du Ctrl + Skift + Retur istället för att bara ange.
Exempel 9 - Använda VLOOKUP med flera kriterier
Excel VLOOKUP -funktion, i sin grundform, kan leta efter ett uppslagsvärde och returnera motsvarande värde från den angivna raden.
Men ofta finns det ett behov av att använda VLOOKUP i Excel med flera kriterier.
Anta att du har en data med elevernas namn, tentamenstyp och matematikpoängen (som visas nedan):
Att använda VLOOKUP -funktionen för att få matematikpoängen för varje elev för respektive tentamenivåer kan vara en utmaning.
Om du till exempel försöker använda VLOOKUP med Matt som uppslagsvärde returnerar det alltid 91, vilket är poängen för den första förekomsten av Matt i listan. För att få poängen för Matt för varje provtyp (Enhetstest, Mellanperiod och Final) måste du skapa ett unikt uppslagsvärde.
Detta kan göras med hjälpkolumnen. Det första steget är att infoga en hjälparkolumn till vänster om poängen.
För att skapa ett unikt kvalificerande för varje instans av namnet använder du följande formel i C2: = A2 & ”|” & B2
Kopiera denna formel till alla celler i hjälpkolumnen. Detta skapar unika uppslagsvärden för varje förekomst av ett namn (som visas nedan):
Nu, medan det fanns upprepningar av namnen, finns det ingen upprepning när namnet kombineras med undersökningsnivån.
Detta gör det enkelt eftersom du nu kan använda hjälpkolumnvärdena som uppslagsvärden.
Här är formeln som ger dig resultatet i G3: I8.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Här har vi kombinerat studentnamnet och undersökningsnivån för att få uppslagsvärdet, och vi använder detta uppslagsvärde och kontrollerar det i hjälpkolumnen för att få matchande post.
Obs! I exemplet ovan har vi använt | som avgränsare när du förenar text i hjälpkolumnen. I vissa exceptionellt sällsynta (men möjliga) tillstånd kan du ha två kriterier som är olika men slutar ge samma resultat när de kombineras. Här är ett exempel:
Observera att medan A2 och A3 är olika och B2 och B3 är olika, så blir kombinationerna desamma. Men om du använder en separator, så skulle även kombinationen vara annorlunda (D2 och D3).
Här är en handledning om hur du använder VLOOKUP med flera kriterier utan att använda hjälpkolumner. Du kan också titta på min videohandledning här.
Exempel 10 - Hanteringsfel när du använder VLOOKUP -funktionen
Excel VLOOKUP -funktion returnerar ett fel när det inte kan hitta det angivna uppslagsvärdet. Du kanske inte vill att det fula felvärdet stör din datas estetik om VLOOKUP inte kan hitta ett värde.
Du kan enkelt ta bort felvärdena med full mening med texten "Inte tillgänglig" eller "Hittades inte".
Till exempel, i exemplet nedan, när du försöker hitta Brad -poängen i listan, returnerar det ett fel eftersom Brads namn inte finns där i listan.
För att ta bort det här felet och ersätta det med något meningsfullt, linda din VLOOKUP -funktion inom IFERROR -funktionen.
Här är formeln:
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "Hittades inte")
IFERROR -funktionen kontrollerar om värdet som returneras av det första argumentet (som är VLOOKUP -funktionen i det här fallet) är ett fel eller inte. Om det inte är ett fel returnerar det värdet med VLOOKUP -funktionen, annars returnerar det Inte hittat.
IFERROR -funktionen är tillgänglig från Excel 2007 och framåt. Om du använder versioner före det, använd följande funktion:
= IF (FEL (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Hittades inte", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Se även: Så här hanterar du VLOOKUP -fel i Excel.
Det är det i denna VLOOKUP -handledning.
Jag har försökt att täcka stora exempel på att använda Vlookup -funktionen i Excel. Om du vill se fler exempel läggas till i den här listan, meddela mig i kommentarfältet.
Obs: Jag har försökt mitt bästa för att korrekturläsa denna handledning, men om du hittar några fel eller stavfel, vänligen meddela mig 🙂
Använder sig av VLOOKUP -funktion i Excel - Video
- Excel HLOOKUP -funktion.
- Excel XLOOKUP -funktion
- Excel INDEX -funktion.
- Excel INDIRECT -funktion.
- Excel MATCH -funktion.
- Excel OFFSET -funktion.
