Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel VLOOKUP -funktion i Excel for at slå en værdi op.
Oversigt over funktion VLOOKUP
VLOOKUP -funktionen Vlookup står for lodret opslag. Den søger efter en værdi i kolonnen længst til venstre i en tabel. Returnerer derefter en værdi et bestemt antal kolonner til højre fra den fundne værdi. Det er det samme som en tilslutning, bortset fra at det slår værdier op lodret i stedet for vandret.
(Bemærk hvordan formelindgangene vises)
VLOOKUP -funktion Syntaks og argumenter:
| 1 | = VLOOKUP (opslagsværdi, tabel_array, col_index_num, interval_lookup) |
opslagsværdi - Den værdi, du vil søge efter.
tabel_array - Dataområdet, der indeholder både den værdi, du vil søge efter, og den værdi, du vil have, at vlookup skal returnere. Kolonnen, der indeholder søgeværdierne, skal være kolonnen længst til venstre.
col_index_num - Kolonnetallet i dataområdet, hvorfra du vil returnere en værdi fra.
rækkevidde_opslag - Sandt eller falsk. FALSK søger efter et nøjagtigt match. SAND søger efter det nærmeste match, der er mindre end eller lig med opslagets værdi. Hvis SAND er valgt, skal kolonnen til venstre (opslagskolonnen) sorteres stigende (lavest til højest).
Hvad er VLOOKUP -funktionen?
Som en af de ældre funktioner i regnearkets verden bruges VLOOKUP -funktionen til at udføre Vertisk Opslag. Det har et par begrænsninger, der ofte overvindes med andre funktioner, såsom INDEX/MATCH. Det har imidlertid fordelen ved at være en enkelt funktion, der kan udføre et eksakt match -opslag i sig selv. Det har også en tendens til at være en af de første funktioner, folk lærer om.
Grundlæggende eksempel
Lad os se på et eksempel på data fra en karakterbog. Vi tager fat på flere eksempler til udtrækning af oplysninger til specifikke elever.
Hvis vi vil finde, hvilken klasse Bob er i, ville vi skrive formlen:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSK) |
Vigtige ting at huske er, at det element, vi leder efter (Bob), skal være i den første kolonne i vores søgeområde (A2: C5). Vi har fortalt funktionen, at vi vil returnere en værdi fra 2nd kolonne, som i dette tilfælde er kolonne B. Endelig angav vi, at vi vil lave en præcis match ved at placere Falsk som det sidste argument. Her vil svaret være "Læsning".
Sidetip: Du kan også bruge tallet 0 i stedet for Falsk som det sidste argument, da de har den samme værdi. Nogle mennesker foretrækker dette, da det er hurtigere at skrive. Ved bare, at begge dele er acceptable.
Skiftede data
For at tilføje en vis præcisering til vores første eksempel behøver opslagsposten ikke at være i kolonne A i dit regneark, kun den første kolonne i dit søgeområde. Lad os bruge det samme datasæt:
Lad os nu finde karakteren for klassen Science. Vores formel ville være
| 1 | = OPLYSNING ("Videnskab", B2: C5, 2, FALSK) |
Dette er stadig en gyldig formel, da den første kolonne i vores søgeområde er kolonne B, hvor vores søgeterm "Science" findes. Vi returnerer en værdi fra 2nd kolonne i søgeområdet, som i dette tilfælde er kolonne C. Svaret er derefter "A-".
Brug af jokertegn
VLOOKUP -funktionen understøtter brugen af jokertegnene "*" og "?" når man laver søgninger. Lad os f.eks. Sige, at vi havde glemt, hvordan vi stavede Franks navn, og bare ville søge efter et navn, der starter med "F". Vi kunne skrive formlen
| 1 | = OPLYSNING ("F*", A2: C5, 2, FALSK) |
Dette ville kunne finde navnet Frank i række 5 og derefter returnere værdien fra 2nd relativ søjle. I dette tilfælde vil svaret være “Videnskab”.
Ikke-eksakt match
For det meste vil du sikre dig, at det sidste argument i VLOOKUP er Falsk (eller 0), så du får et nøjagtigt match. Der er dog et par gange, hvor du muligvis søger efter et ikke-eksakt match. Hvis du har en liste med sorterede data, kan du også bruge VLOOKUP til at returnere resultatet for den vare, der enten er den samme eller den næstmindste. Dette bruges ofte, når det drejer sig om stigende talintervaller, f.eks. I et skattebord eller kommissionsbonusser.
Lad os sige, at du vil finde skattesatsen for en indkomst, der er indtastet i celle D2. Formlen i D4 kan være:
| 1 | = OPLYSNING (D2, A2: B6, 2, SAND) |
Forskellen i denne formel er, at vores sidste argument er "sandt". I vores specifikke eksempel kan vi se, at når vores individuelle indtægter en indkomst på $ 45.000, vil de have en skattesats på 15%.
Bemærk: Selvom vi normalt ønsker en nøjagtig match med False som argument, glemmer du at angive 4th -argument i en VLOOKUP, er standarden True. Dette kan få dig til at få nogle uventede resultater, især når du beskæftiger dig med tekstværdier.
Dynamisk kolonne
VLOOKUP kræver, at du giver et argument om, hvilken kolonne du vil returnere en værdi fra, men anledningen kan opstå, når du ikke ved, hvor kolonnen vil være, eller du vil tillade din bruger at ændre, hvilken kolonne der skal returneres fra. I disse tilfælde kan det være nyttigt at bruge funktionen MATCH til at bestemme kolonnetallet.
Lad os overveje vores karakterbogeksempel igen med nogle input i E2 og E4. For at få kolonnenummeret kunne vi skrive en formel for
| 1 | = MATCH (E2, A1: C1, 0) |
Dette vil forsøge at finde den nøjagtige position for "Grade" inden for området A1: C1. Svaret vil være 3. Ved at vide dette, kan vi tilslutte det til en VLOOKUP -funktion og skrive en formel i E6 sådan:
| 1 | = OPLYSNING (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Så MATCH -funktionen evalueres til 3, og det fortæller VLOOKUP at returnere et resultat fra 3rd kolonne i A2: C5 -området. Samlet set får vi så vores ønskede resultat af “C”. Vores formel er dynamisk nu, idet vi kan ændre enten den kolonne, der skal kigges på, eller navnet, vi skal søge efter.
VLOOKUP begrænsninger
Som nævnt i begyndelsen af artiklen er det største fald i VLOOKUP, at det kræver, at søgeudtrykket findes i venstre kolonne i søgeområdet. Selvom der er nogle smarte tricks, du kan gøre for at overvinde dette, er det almindelige alternativ at bruge INDEX og MATCH. Denne kombination giver dig mere fleksibilitet, og det kan nogle gange endda være en hurtigere beregning.
VLOOKUP i Google Sheets
VLOOKUP -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
Ekstra Noter
Brug VLOOKUP -funktionen til at udføre et VERTISK opslag. VLOOKUP søger efter et nøjagtigt match (rækkevidde_opslag = FALSK) eller det nærmeste match, der er lig med eller mindre end opslagsværdien (rækkevidde_opslag = SAND, kun numeriske værdier) i den første række i table_array. Det returnerer derefter en tilsvarende værdi, n antal rækker under kampen.
Når du bruger en VLOOKUP til at finde et nøjagtigt match, definerer du først en identificerende værdi, som du vil søge efter som opslagsværdi. Denne identificerende værdi kan være et SSN, medarbejder -id, navn eller en anden unik identifikator.
Dernæst definerer du området (kaldet tabel_array), der indeholder identifikatorerne i den øverste række og de værdier, du i sidste ende ønsker at søge efter i rækkerne under den. VIGTIGT: De unikke identifikatorer skal være i den øverste række. Hvis de ikke er det, skal du enten flytte rækken til toppen eller bruge MATCH / INDEX i stedet for VLOOKUP.
For det tredje skal du definere rækkenummer (row_index) af tabel_array som du ønsker at returnere. Husk, at den første række, der indeholder de unikke identifikatorer, er række 1. Den anden række er række 2 osv.
Sidst skal du angive, om du vil søge efter et nøjagtigt match (FALSE) eller nærmeste match (TRUE) i rækkevidde_opslag. Hvis den nøjagtige matchmulighed er valgt, og der ikke findes et eksakt match, returneres en fejl (#N/A). For at få formlen retur tom eller “ikke fundet” eller en anden værdi i stedet for fejlværdien (#N/A) skal du bruge IFERROR -funktionen med VLOOKUP.
Sådan bruges VLOOKUP -funktionen til at returnere et omtrentligt match -sæt: rækkevidde_opslag = SAND. Denne indstilling er kun tilgængelig for numeriske værdier. Værdierne skal sorteres i stigende rækkefølge.
Brug HLOOKUP -funktionen i stedet for at udføre et vandret opslag.
VLOOKUP Eksempler i VBA
Du kan også bruge VLOOKUP -funktionen i VBA. Type:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
For funktionsargumenterne (opslagsværdi osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.
Tilbage til listen over alle funktioner i Excel
