Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel VLOOKUP -funktion i Excel för att leta upp ett värde.
VLOOKUP Funktionsöversikt
VLOOKUP -funktionen Vlookup står för vertikal uppslagning. Det söker efter ett värde i kolumnen längst till vänster i en tabell. Returnerar sedan ett värde ett angivet antal kolumner till höger från det hittade värdet. Det är samma sak som en uppkoppling, förutom att det letar upp värden vertikalt istället för horisontellt.
(Lägg märke till hur formelinmatningarna ser ut)
VLOOKUP -funktion Syntax och argument:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
uppslagningsvärde - Värdet du vill söka efter.
tabell_array - Dataområdet som innehåller både det värde du vill söka efter och det värde du vill att vlookup ska returnera. Kolumnen som innehåller sökvärdena måste vara kolumnen längst till vänster.
col_index_num - Kolumnnumret i dataområdet, från vilket du vill returnera ett värde från.
range_lookup - Sant eller falskt. FALSE söker efter en exakt matchning. TRUE söker efter närmaste matchning som är mindre än eller lika med lookup_value. Om TRUE väljs måste kolumnen längst till vänster (uppslagskolumnen) sorteras stigande (lägst till högst).
Vad är VLOOKUP -funktionen?
Som en av de äldre funktionerna i kalkylbladets värld används VLOOKUP -funktionen för att göra Vertiska Sökningar. Den har några begränsningar som ofta övervinns med andra funktioner, till exempel INDEX/MATCH. Det har dock fördelen att det är en enda funktion som kan göra en exakt matchning i sig själv. Det tenderar också att vara en av de första funktionerna människor lär sig om.
Grundläggande exempel
Låt oss titta på ett exempel på data från en betygsbok. Vi tar upp flera exempel för att extrahera information för specifika studenter.
Om vi vill hitta vilken klass Bob är i, skulle vi skriva formeln:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Viktiga saker att komma ihåg är att objektet vi söker efter (Bob) måste finnas i den första kolumnen i vårt sökområde (A2: C5). Vi har berättat för funktionen att vi vill returnera ett värde från 2: annd kolumn, som i detta fall är kolumn B. Slutligen angav vi att vi vill göra en exakt matchning genom att placera False som det sista argumentet. Här blir svaret "Läsning".
Sidotips: Du kan också använda talet 0 istället för False som det sista argumentet, eftersom de har samma värde. Vissa människor föredrar detta eftersom det är snabbare att skriva. Vet bara att båda är acceptabla.
Skiftad data
För att lägga till lite förtydligande i vårt första exempel behöver sökobjektet inte finnas i kolumn A i kalkylarket, bara den första kolumnen i ditt sökintervall. Låt oss använda samma datamängd:
Låt oss nu hitta betyget för klassen Science. Vår formel skulle vara
| 1 | = VLOOKUP ("Science", B2: C5, 2, FALSE) |
Detta är fortfarande en giltig formel, eftersom den första kolumnen i vårt sökintervall är kolumn B, det är där vår sökterm "Science" finns. Vi returnerar ett värde från 2: annd kolumn i sökområdet, som i detta fall är kolumn C. Svaret är då ”A-“.
Användning av jokertecken
VLOOKUP -funktionen stöder användningen av jokertecken “*” och “?” när man gör sökningar. Låt oss till exempel säga att vi hade glömt hur man stavar Franks namn och bara ville söka efter ett namn som börjar med "F". Vi skulle kunna skriva formeln
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Detta skulle kunna hitta namnet Frank i rad 5 och sedan returnera värdet från 2nd relativ kolumn. I det här fallet blir svaret ”Vetenskap”.
Ej exakt matchning
För det mesta vill du se till att det sista argumentet i VLOOKUP är falskt (eller 0) så att du får en exakt matchning. Det finns dock några gånger när du kan leta efter en icke-exakt matchning. Om du har en lista med sorterade data kan du också använda VLOOKUP för att returnera resultatet för objektet som är antingen detsamma eller det näst minsta. Detta används ofta när man hanterar ökande antal intervall, till exempel i en skattetabell eller provisionsbonus.
Låt oss säga att du vill hitta skattesatsen för en inkomst som anges i cell D2. Formeln i D4 kan vara:
| 1 | = VISNING (D2, A2: B6, 2, SANT) |
Skillnaden i denna formel är att vårt sista argument är "True". I vårt specifika exempel kan vi se att när våra individer lägger in en inkomst på 45 000 dollar kommer de att ha en skattesats på 15%.
Notera: Även om vi vanligtvis vill ha en exakt matchning med False som argument, glömmer du att ange 4: anth -argumentet i en VLOOKUP är standarden True. Detta kan göra att du får oväntade resultat, särskilt när du hanterar textvärden.
Dynamisk kolumn
VLOOKUP kräver att du anger ett argument som säger vilken kolumn du vill returnera ett värde från, men det kan uppstå när du inte vet var kolumnen kommer att vara, eller om du vill tillåta din användare att ändra vilken kolumn han ska återvända från. I dessa fall kan det vara bra att använda MATCH -funktionen för att bestämma kolumnnumret.
Låt oss överväga vårt betygsboksexempel igen, med några ingångar i E2 och E4. För att få kolumnnumret kan vi skriva en formel för
| 1 | = MATCH (E2, A1: C1, 0) |
Detta kommer att försöka hitta den exakta positionen för "Grade" inom intervallet A1: C1. Svaret blir 3. Genom att veta detta kan vi ansluta det till en VLOOKUP -funktion och skriva en formel i E6 så här:
| 1 | = VISNING (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Så, MATCH -funktionen kommer att utvärderas till 3, och det säger VLOOKUP att returnera ett resultat från 3rd kolumn i A2: C5 -intervallet. Sammantaget får vi sedan vårt önskade resultat av “C”. Vår formel är dynamisk nu genom att vi kan ändra antingen kolumnen att titta på eller namnet att söka efter.
Begränsningar för VLOOKUP
Som nämnts i början av artikeln är det största fallet med VLOOKUP att det kräver att sökordet finns i den vänstra kolumnen i sökområdet. Även om det finns några fina knep du kan göra för att övervinna detta, är det vanliga alternativet att använda INDEX och MATCH. Den kombinationen ger dig mer flexibilitet, och det kan ibland till och med vara en snabbare beräkning.
VLOOKUP i Google Kalkylark
VLOOKUP -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
Ytterligare anmärkningar
Använd VLOOKUP -funktionen för att utföra en VERTIKAL sökning. VLOOKUP söker efter en exakt matchning (range_lookup = FALSE) eller den närmaste matchningen som är lika med eller mindre än lookup_value (range_lookup = SANT, endast numeriska värden) på den första raden i tabellen_array. Det returnerar sedan ett motsvarande värde, n antal rader under matchningen.
När du använder en VLOOKUP för att hitta en exakt matchning definierar du först ett identifieringsvärde som du vill söka efter som uppslagningsvärde. Detta identifieringsvärde kan vara ett SSN, medarbetar -ID, namn eller någon annan unik identifierare.
Därefter definierar du intervallet (kallas tabell_array) som innehåller identifierarna i den översta raden och de värden som du slutligen vill söka efter i raderna under den. VIKTIGT: De unika identifierarna måste finnas på den översta raden. Om de inte är det måste du antingen flytta raden till toppen eller använda MATCH / INDEX istället för VLOOKUP.
Tredje, definiera radnummer (row_index) av tabell_array som du vill återvända. Tänk på att den första raden, som innehåller de unika identifierarna, är rad 1. Den andra raden är rad 2, etc.
Sist måste du ange om du vill söka efter en exakt matchning (FALSE) eller närmaste match (SANN) i range_lookup. Om alternativet för exakt matchning är valt och en exakt matchning inte hittas, returneras ett fel (#N/A). För att få formeln att returnera tom eller "inte hittad", eller något annat värde i stället för felvärdet (#N/A), använd IFERROR -funktionen med VLOOKUP.
För att använda VLOOKUP -funktionen för att returnera en ungefärlig matchningsuppsättning: range_lookup = SANT. Det här alternativet är endast tillgängligt för numeriska värden. Värdena måste sorteras i stigande ordning.
För att utföra en horisontell sökning använder du HLOOKUP -funktionen istället.
VLOOKUP Exempel i VBA
Du kan också använda VLOOKUP -funktionen i VBA. Typ:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
För funktionsargumenten (lookup_value, etc.) kan du antingen ange dem direkt i funktionen eller definiera variabler som ska användas istället.
Återgå till listan över alla funktioner i Excel
