Introduktion till VLOOKUP -funktion
VLOOKUP -funktionen i Excel är utan tvekan den mest använda och pratade funktionen. I alla anställningsintervjuer som kräver Excel -färdigheter är detta den vanligaste frågan om du vet hur du använder VLOOKUP -funktionen.
VLOOKUP -funktionen hör till uppslagskategorin excel -funktioner. V i VLOOKUP står för vertikal. Funktionen används för att leta upp data som är strukturerade vertikalt. För horisontell sökning finns en funktion som kallas HLOOKUP.
Excel VLOOKUP -funktion letar efter den första matchningen av ett visst värde som kallas uppslagsvärde, i en datatabell och returnerar värdet från det angivna kolumnindexet. Matchen kan vara ungefärlig eller exakt.
Även om VLOOKUP är lätt att använda, har det sina egna begränsningar och bakgrunder. Vi kommer att diskutera allt inklusive styrkor och svagheter i funktionen.
Syntax:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Vad är Lookup Value
Lookup_value: Det är värdet som du vill leta efter i en tabellmatris.
Till exempel om du letar efter rulle nr. 110 i studentens bord sedan dess 110 är uppslagsvärde.
Tabell_array: Tabellen från vilken du vill leta efter uppslagsvärde.
Till exempel, om du letar efter 110 i tabellmatrisen B5: E17. Sedan B5: E17 är din tabellmatris.
col_index_number: Kolumnnumret i tabellmatris som du vill hämta resultat från.
Till exempel i tabellmatris B5: E17 du vill få värde från kolumn D då blir ditt kolumnindex 3 (räknas från B till D (B, C, D)).
[range_lookup]: FALSKT om du vill söka efter exakt värde, SANT om du vill ha en ungefärlig matchning.
Hur man använder VLOOKUP -funktionen
När du har en strukturerad data och du vill leta efter data i den informationen är VLOOKUP -funktionen lösningen.
VLOOKUP -matchande lägen
VLOOKUP har två matchningslägen, Ungefärlig matchning och exakt matchning. Som standard utför VLOOKUP en ungefärlig matchning. Men om du vill tvinga VLOOKUP -funktionen att göra en exakt matchning kan du också göra det. Oftast tror jag och jag starkt på att andra också försöker göra en exakt matchning med hjälp av VLOOKUP -funktionen. Jag förstår inte varför excel -utvecklare tror att användare mest skulle vilja använda den ungefärliga matchningen.
1. VLOOKUP Ungefärlig matchning
= VLOOKUP (lookup_value, table_array, col_index_number, 1)
I VLOOKUP ungefärlig matchning, om ett värde inte hittas då sista värdet som är mindre än uppslagsvärdet matchas och värdet från ett givet kolumnindex returneras.
VLOOKUP är som standard ungefärlig matchning, om vi utelämnar variabel för intervalluppslag. En ungefärlig matchning är användbar när du vill göra en ungefärlig matchning och när du har din tabellmatris sorterad i stigande ordning.
Vlookup letar efter värdet och om det inte hittar värdet i tabellmatris matchar det värdet som är mindre än det värdet i tabellmatrisen. Låt oss förstå det med ett exempel.
Exempel 1
Vi har en studentlista här. Varje elev har fått några betyg i ett test. Nu vill jag ge betyg enligt deras betyg.
Alla elever som fick mindre än 40 bör märkas F, elever som fick mellan 40 och 60 ska vara märkta C, och så vidare enligt bilden nedan.
Vi skulle skriva denna VLOOKUP -formel i E2 och dra ner den.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, TRUE)
Hur det fungerar?
I exemplet ovan finns våra uppslagsvärden i kolumn D, med början från D2. Tabellen Array är H2: I6 (notera att den är sorterad i stigande ordning och absolut). Kolumnen vi får data från är 2. Och matchningstyp som vi har definierat ungefärligt genom att passera TRUE (ingenting och 1 kommer att betyda samma).
Så låt oss undersöka frist -uppslagsvärdet D2 som innehåller 40.
- VLOOKUP söker efter 40 i första kolumn (H) i tabellmatris H2: I6.
- Den hittar 40 vid andra positionen i cell H3.
- Nu går den till andra kolumnen från H3 till I3 och returnerar D.
I det här fallet hittade vlookup den exakta matchningen. Får se nästa fall.
Det andra uppslagsvärdet D3 som innehåller 36.
- VLOOKUP söker efter 36 i första kolumnen i tabellmatris H2: I6.
- VLOOKUP kunde inte hitta 36 någonstans i första kolumnen.
- Eftersom VLOOKUP inte kunde hitta 36, det matchar det med det senast hittade värdet som är mindre än uppslagsvärdet.
- Det första värdet som är mindre än 36 är 0, därför är det retur F som är relaterat till 0.
Detta händer för varje fall här. För 92 är sista värdet som är mindre än 92 90. Därför returneras A för ungefärlig matchning.
2. VLOOKUP Exakt matchning
Denna mest använda form av VLOOKUP och kan också vara mest användbar. När du vill leta efter exakt värde i första kolumnen i tabellmatrisen använder du exakt matchning. Till exempel om du söker efter ett ID än du helst skulle vilja ha en exakt matchning istället för något värde som är mindre än detta värde.
För att tvinga VLOOKUP till en exakt matchning i excel skickar vi 0 eller FALSE som parameter_lookup.
= VLOOKUP (lookup_value, table_array, col_index_number, 0)
Om värdet inte hittas i den första kolumnen i tabellmatrisen kommer formeln att returnera #N/A -fel.
Låt oss se ett exempel.
Exempel 2
I det här exemplet vill jag bara skriva antal elever i A2 och i B2 vill jag hämta elevens namn och i C2 hans Kvalitet. Enkel. Tabellmatris är B5: D17. Varför?
Så för att göra detta, skriv dessa två formler i cellen i cell B2 respektive C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Oavsett vilket ID du skriver i cell A2, letar VLOOKUP -funktionen efter exakt matchning i B -kolumnen och returnerar värdet från en given col_index.
Hur fungerar Exact Match VLOOKUP?
Exact Match VLOOKUP i excel är enkelt. Det letar efter uppslagsvärdet i den första kolumnen i ett givet tabellarray och returnerar värdet från värdet från ett givet kolumnindexnummer. Om värdet inte hittas returnerar VLOOKUP #N/A -fel.
Bästa användningen av VLOOKUP -funktionen
Vlookup har många användningsområden. Jag kommer att diskutera några användbara användningsfall.
3. Använd Excel VLOOKUP för betyg istället för komplex kapslad IF -funktion
Använd VLOOKUP ungefärlig matchning för att ersätta kapslad IF -funktion. I exempel 1 såg vi hur vi kan använda vlookup för att betygsätta elever enligt deras betyg.
Den ungefärliga matchningen VLOOKUP är snabbare än kapslad om eftersom den använder binär sökning internt.
4. Använd VLOOKUP för att hämta data
Denna vanligaste uppgift som enkelt kan utföras med VLOOKUP -funktionen i Excel. I exempel 2 gjorde vi detta för att hämta elevers data med hjälp av deras roll nr.
5. Kontrollera om ett värde finns i en post med Vlookup.
VLOOKUP kan användas för att kontrollera om ett värde finns i någon lista eller inte.
I exempel 2 när vi skriver 152 returnerar det #N/A -fel. Om VLOOKUP returnerar #N/A betyder det att det inte hittade det angivna värdet någonstans i given data.
Jag har förberett en kort artikel om detta Du kan läsa den här.
Använd en VLOOKUP -formel för att kontrollera om ett värde finns
Få kolumnindex automatiskt
Jag tillhör den gruppen människor som inte vill upprepa samma uppgift om och om igen. Så att jag ändrar col_index -nummer igen och igen irriterar mig och jag undviker det alltid. Egentligen undviker jag någon form av redigering i mina formler när jag har skrivit det, såvida inte och tills det inte finns något annat alternativ.
Kolumnindex kan beräknas automatiskt i Excel. Det finns flera sätt att göra det. Låt oss se några av dem här.
6. Använd VLOOKUP -funktion med MATCH -funktion
Så som vi vet att MATCH -funktionen returnerar det hittade indexet för matchat värde.
Så om vi har exakt samma rubriker vid uppslagningsplats som källdata, än vi kan använda för att få col_index. Hur? Vi får se…
I nedanstående data vill vi bara skriva rullnummer i G2 och vill hämta studentnamn, betyg och betyg i H2, vilken rubrik vi än skriver där.
Skriv denna formel i cell H2
= VISNING (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Nu när du ändrar rubrik i H1 kommer värdet i H2 att visas från den kolumnen.
Du kan använda en rullgardinsmeny med datavalidering som har alla rubriker från tabellen för att göra den mer användarvänlig.
Här gör VLOOKUP exakt matchning. Nu gör VLOOKUP sina vanliga saker med att dra data. Magin görs med MATCH -funktionen vid col_index -position.
Här ser VLOOKUP efter valfritt värde i G2 i första kolumnen i tabellmatris B2: E14. Nu på platsen för col_index har vi MATCH (H1, B1: E1,0).
MATCH -funktionen letar efter valfritt värde i H1 inom intervallet B1: E1 och returnerar index för matchningsvärde.
Till exempel när vi skriver studerande i H1 letar den efter det inom räckvidd B1: E1. Det finns i C2. därav återkommer den 2. Om vi skriver Kvalitet det kommer att returnera studenten.
Observera att när vi skriver Region returnerar den #N/A. Eftersom det är utanför tabellens omfattning. Vi kommer att prata om det senare i artikeln.
Detta är det bästa sättet att göra VLOOKUP automatiskt. Det finns andra metoder men de är inte så flexibla än så här.
7. Använd VLOOKUP -funktionen med COLUMN -funktionen
Kolumnfunktionen returnerar kolumnnumret för den angivna referensen. Och vad behöver vi för att hämta data med VLOOKUP? Col_index. Så med hjälp av COLUMN -funktionen kan vi naturligtvis få data från alla datamängder.
Låt oss ta exemplet ovan. Men nu måste vi hämta hela data. Inte bara en kolumn.
Skriv denna formel i H2 och kopiera i I2 och J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COLUMN (B1), 0)
Detta ger dig ett dynamiskt resultat. Hur? Låt oss se.
VLOOKUP fungerar som vanligt. På column_index har vi skrivit KOLONN (B1), vilket kommer att översättas till 2, eftersom vi har elevnamn i 2 -kolumn från B -kolumn, returnerar det studentens namn.
Viktig: Detta fungerar eftersom vi har vår tabell från B -kolumn. Om ditt bord i mitten av arket måste du subtrahera eller lägga till några siffror så att det matchar dina krav.
Till exempel, om tabellen ovan startade från C1 i samma struktur, fungerade alla nedanstående formler bra.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP för att slå ihop data
I de flesta databaser finns det en fråga som sammanfogas till tabeller, oftast kallad en sammanfogningsfråga men MS Excel i ingen databas, därför har den ingen kopplingsfråga. Men det betyder inte att vi inte kan gå med i två tabeller om vi har några vanliga kolumner i två tabeller.
Så när du får lite data från två avdelningar i samma företag skulle du vilja slå ihop dem för att analysera dessa data.
Till exempel, när du får betyg på elever från lärare och sedan närvarorekord från administrativa avdelningar, skulle du vilja se vilken elev som fick vilket betyg och vad som var hans närvaroprocent, på en gång.
Vi måste slå ihop dem i tabellen nedan.
Märken, vi måste komma från Marks Table och närvaro från närvarodata.
Skriv nedan formler i cell D19 för att få märken och dra ner.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Skriv nedan formeln i cell E19
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
Och vi har slagit ihop två bord till ett bord. Du kan naturligtvis lägga till mer än två tabeller. Du behöver bara hämta data från alla tabeller du vill ha på ett ställe med hjälp av VLOOKUP.
Bästa praxis för VLOOKUP
Så alla ovanstående exempel använde vi bara vlookup med rådata. I alla ovanstående exempel var vi försiktiga med data vi använde. Vi behövde vara försiktiga medan vi gav referenser till tabelluppställningar. Men det finns vissa sätt som kan minska denna ansträngning kraftigt.
9. Använd Absoluta referenser med VLOOKUP
Du kanske har märkt att jag i vissa artiklar har använt absoluta intervall (intervall $ tecken). Tja, absoluta raseri används när vi inte vill att intervallet ska ändras när vi kopierar formelceller till andra celler.
Om jag till exempel har = VLOOKUP (G2,B2: E14, 2,0), 0) i cell H2, och om jag kopierar eller drar ner det i cell H3 kommer formeln att ändras till = VLOOKUP (G3,B3: E152,0). Alla referenser ändras relativt. Här kanske vi vill ändra referens för uppslagsvärde men inte tabellmatrisen. Eftersom B2 kommer ut eller sträcker sig och vi kanske alltid vill ha det i våra tabellarrayer.
Så, för att förhindra att vi använder absoluta referenser. Vi gör dessa intervall absoluta som vi inte vill ändra, som table_array i H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). När du kopierar H2 i H3 är formeln = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Observera att uppslagsvärdet ändras men inte table_array.
10. Använd namngivna områden med VLOOKUP
I exemplet ovan använde vi absolut referens för att fixa table_array. Det ser ganska luddigt ut och svårt att läsa. Vad sägs om du bara kan skriva 'data' i stället för $ B $ 2: $ E $ 14 och det skulle referera till $ B $ 2: $ E $ 14 alltid. Det kommer att göra det ännu enklare att läsa och skriva formeln.
Så välj bara intervallet och namnge det "data".
Om du vill namnge ett område väljer du intervallet. Högerklicka, klicka på Definiera namn och namnge det område du vill ha. Nu bara = VLOOKUP (G3,data,2,0), 0) formeln fungerar exakt som tidigare.
Du kan läsa mer om namngivna intervall i Excel. Jag har förklarat varje aspekt av namngivna intervall här.
11. Jokertecken för delvis matchning med VLOOKUP
VLOOKUP tillåter delvis matchning. Ja, medan du gör exakt matchning av VLOOKUP kan du använda jokerteckenoperatörer för att göra delvis matchning. Om du till exempel vill leta upp ett värde som börjar med "Gil", kan du använda jokerteknik * med "Gil" som "Gil *". Låt oss se ett exempel.
Här vill jag få betyg på student vars namn börjar med Gil. För att få det skriver jag nedanstående formel.
= VLOOKUP ("*Gill", C2: D14,2,0)
Det finns två jokertecken som är tillgängliga i excel för användning med VLOOKUP -funktionen.
Asterisk (*) jokertecken. Detta används när användaren inte vet hur många tecken det finns och bara känner till några matchande tecken. Till exempel om användaren känner till ett namn som börjar med "Sm", skriver han "Sm*". Om användaren vet att ett uppslagsvärde som slutar med "123" skriver han "*123". (jokertecken fungerar bara med texter). Och om han vet att "se" kommer in i en text så skriver han "*se*".
Frågetecken ("?"). Detta används när användaren känner till antalet tecken som saknas. Till exempel, om jag vill leta upp ett värde som har 4 tecken i det men jag inte vet vad det är kommer jag helt enkelt att skriva "????" på platsen för uppslagsvärdet.
Vlookup returnerar respektive värde för det först hittade värdet som har exakt fyra tecken i det.
Viktig: Wild card fungerar bara med textvärden. Konvertera siffror till text om du vill använda jokertecken med dem.
Svagheterna i VLOOKUP -funktionen
VLOOKUP är verkligen en kraftfull och enkel funktion för att hämta värden, men det finns många områden där VLOOKUP inte är så bra. Om du arbetar med excel måste du känna till dem också och hur du gör uppgifter som VLOOKUP inte kan utföra.
12. Kan inte hämta värde från vänster om Table_Array
Den största bristen i VLOOKUP -funktionen är att den inte kan hämta värde från vänster om tabellmatrisen. VLOOKUP letar bara upp till sökvärdet.
Detta beror på att VLOOKUP söker efter given sökvärde i den givna första kolumnen tabellmatris. Det kommer aldrig att returnera ett värde från utsidan av bordet. Och om du försöker behålla den vänstra kolumnen i tabellmatrisen blir den den första kolumnen i tabellmatrisen, därför kommer sökvärdet att sökas i det intervallet. Vilket förmodligen kommer att generera fel.
För att leta upp värden från vänster om ett uppslagsvärde använder vi INDEX-MATCH istället. INDEX-MATCH-funktionen kan leta upp värden från vilken kolumn som helst på arket. Egentligen är uppslagsintervallet och intervallet från vilket du vill leta upp värden helt oberoende, vilket gör indexmatchning mycket anpassningsbar.
VLOOKUP returnerar #N/A även när det finns uppslagningsvärde.
#N/A -fel returneras av VLOOKUP -funktionen när sökningsvärdet inte hittas. Du kan tycka att det är irriterande när ett värde finns inom intervallet men VLOOKUP returnerar #N/A -fel.
14. När siffror formas som text
Detta händer oftast när siffror formas som text. När du hittar kommandot CTRL+F kommer excel att hitta det, men när du försöker använda VLOOKUP returnerar det #N/A. Denna uppgift ges i de flesta intervjuer som trickfrågor.
I bilden ovan kan du se att 101 ligger precis där, men formeln returnerar ett fel. Hur hanterar du det? Det finns två metoder.
1.Konvertera text till nummer i data
Du kan se den gröna taggen i vänstra övre hörnet i rollno -kolumnen. De indikerar att något är ovanligt i cellens värde. När du markerar cellen visar det att numret är formaterat som text.
När du klickar på utropstecknet (!) Visar det dig alternativet Konvertera till nummer. Markera alla celler och klicka på det här alternativet. Alla värden kommer att konverteras till text.
2. Konvertera ditt söknummer till text i formeln
I exemplet ovan muterade vi originaldata. Du kanske inte vill ändra något i originaldata. Så du skulle vilja göra detta i formeln istället. För att ändra nummer till text i VLOOKUP -formel, skriv detta.
= VISNING (TEXT (G2, "0"), B2: D14,2,0)
Här ändrar formeln dynamiskt tal till text och matchar det sedan med ett givet intervall.
15. Text med ledande och efterföljande utrymmen
Vissa data som tillhandahålls av datainmatningsoperatörer och data från internet är inte rena. De kan ha efterföljande mellanslag eller några oskrivbara tecken. När du söker med dessa värden kan du se ett #N/A -fel. För att undvika detta, rengör dina data först. Använd TRIM -funktionen för att ta bort ledande mellanslag. TRIM tar bort valfritt antal ledande eller bakre mellanslag från text.
Så jag föreslår att du lägger till en ny kolumn och får din rensade data där. Värdeklistra sedan in det. Om du vill kan du bli av med den ursprungliga kolumnen.
= TRIM (RENGÖR (text))
Hanteringsfel i VLOOKUP
Som du vet misslyckas VLOOKUP med att hitta ett värde, det returnerar ett #N/A -fel. Det är okej att få #N/A -fel, kanske du ibland tänker få det, till exempel när du vill kontrollera om a -värdet redan finns i listan eller inte. Det #N/A betyder att värdet inte finns där.
Men #N/A ser ful ut. Vad sägs om att få en användarvänlig utmatning, till exempel "Id hittades inte" eller "Kund hittades inte". Vi kan använda IFERROR med VLOOKUP -funktion för att undvika #N/A.
Använd nedanstående formel för att fånga fel i VLOOKUP.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP med MULTIPLE Criterias
VLOOKUP kan inte fungera med flera kriterier.Ja, du kan bara ha ett kriterium för att leta upp värden med hjälp av VLOOKUP -formeln.
Till exempel om du har för- och efternamn i två separata kolumner.
Och nu om du vill leta upp värde vars förnamn är John och Efternamn Dave, då kan du inte göra det normalt.
Men det finns en lösning för detta. Du kan skapa en separat kolumn genom att sammanfoga förnamn och efternamn och sedan slå upp den för- och efternamnkolumnen.
Där är en INDEX-MATCH-alternativ som kan leta upp flera kriterier utan någon hjälpkolumn.
17. VLOOKUP Hämtar endast först hittat värde
Tänk dig att du har lite data i den centrala regionen. Nu vill du få de första 5 dataposterna i centralregionen. VLOOKUP returnerar bara det första centrala värdet i alla fem poster. Detta är en viktig bakgrund.
Men det betyder inte att vi inte kan uppnå detta. Vi kan använda en komplex matrisformel enligt nedan.
= INDEX ($ C $ 2: $ C $ 14, LITEN (OM (G2 = $ A $ 2: $ A $ 14, RAD ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), RAD (1: 1)))
Skriv denna formel och tryck CTRL+SKIFT+ENTER.
Och det är klart.
Jag har förklarat det i artikeln hur man LOOKUP flera värden i Excel.
Så ja grabbar, i den här artikeln har jag täckt alla möjliga aspekter av VLOOKUP -funktion enligt min kunskap. Om du tror att jag har missat något än vänligen meddela mig i kommentarfältet nedan.
Vlookup Topp 5 värden med dubblettvärden med INDEX-MATCH i Excel
VLOOKUP Flera värden
VLOOKUP med Dynamic Col Index
Delvis matchning med VLOOKUP -funktion
Använd VLOOKUP från två eller fler uppslagstabeller
Vlookup efter datum i Excel
Använd en VLOOKUP -formel för att kontrollera om ett värde finns
Hur man VLOOKUP från olika Excel -blad
VLOOKUP med siffror och text
IF, ISNA och VLOOKUP -funktion
ISNA- och VLOOKUP -funktion
IFERROR och VLOOKUP -funktion