Disse eksempler lærer dig, hvordan du Vlookup flere kriterier, returnerer en bestemt forekomst eller alle matches, gør dynamisk Vlookup i flere ark og mere.
Det er anden del af serien, der hjælper dig med at udnytte kraften i Excel VLOOKUP. Eksemplerne antyder, at du ved, hvordan denne funktion fungerer. Hvis ikke, er det grund til at starte med de grundlæggende anvendelser af VLOOKUP i Excel.
Før jeg går videre, skal jeg kort minde dig om syntaksen:
Nu hvor alle er på samme side, lad os se nærmere på de avancerede eksempler på VLOOKUP-formlen:
- Sådan Vlookup flere kriterier i Excel
- Vlookup og returnere nte match
- Vlookup flere værdier
- Vlookup baseret på række- og kolonneværdier
- Indlejret Vlookup-formel
- VLOOKUP og INDIRECT for at hente data fra flere ark
Sådan Vlookup flere kriterier
Excel VLOOKUP-funktionen er virkelig nyttig, når det kommer til søgning på tværs af en database for en bestemt værdi. Den mangler dog en vigtig funktion – dens syntaks tillader kun en opslagsværdi. Men hvad hvis du vil slå op med flere betingelser? Der er et par forskellige løsninger, du kan vælge imellem.
Formel 1. VLOOKUP baseret på tw o værdier
Antag at du har en ordreliste og vil finde mængden baseret på 2 kriterier, kundenavn og produkt. En komplicerende faktor er, at hver kunde bestilte flere produkter, som vist i nedenstående tabel:
En almindelig VLOOKUP-formel vil ikke arbejde i dette situation, fordi den returnerer det første fundne match baseret på en enkelt opslagsværdi, som du angiver.
For at overvinde dette kan du tilføje en hjælpekolonne og sammenkæde værdierne fra to opslagskolonner (kunde og produkt) der. Det er vigtigt, at hjælpekolonnen er kolonnen længst til venstre i tabelarrayet, fordi det er, hvor Excel VLOOKUP altid søger efter opslagsværdien.
Så tilføj en kolonne til venstre for din tabel og kopier nedenstående formel på tværs af den kolonne. Dette udfylder hjælpekolonnen med værdierne fra kolonne B og C (mellemrumstegnet sammenkædes imellem for bedre læsbarhed):
=B2&" "&C2
Og brug derefter en standard VLOOKUP-formel, og placer begge kriterier i argumentet lookup_value, adskilt med et mellemrum:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Eller indtast kriterierne i separate celler (G1 og G2 i vores tilfælde) og sammenkæd disse celler:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Som vi ønsker at returnere en værdi fra kolonne D, som er fjerde i tabelarrayet, bruger vi 4 til col_index_num. Argumentet range_lookup er indstillet til FALSE til Vlookup et nøjagtigt match. Skærmbilledet nedenfor viser resultatet:
Hvis din opslagstabel er i et andet ark, skal du inkludere arkets navn i din VLOOKUP-formel. For eksempel:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativt kan du oprette et navngivet område til opslagstabellen (f.eks. Ordrer) for at gøre formlen lettere- at læse:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
For mere information, se Sådan Vlookup fra et andet ark i Excel.
Formel 2. Excel VLOOKUP med flere betingelser
I teorien kan du bruge ovenstående tilgang til Vlookup mere end to kriterier. Der er et par advarsler. For det første er en opslagsværdi begrænset til 255 tegn, og for det andet tillader regnearkets design muligvis ikke at tilføje en hjælpekolonne.
Heldigvis giver Microsoft Excel ofte mere end en måde at gøre det samme på. For at Vlookup flere kriterier kan du bruge enten en INDEX MATCH-kombination eller den XLOOKUP-funktion, der for nylig blev introduceret i Office 365.
For eksempel at slå op baseret på 3 forskellige værdier (dato, kundenavn og produkt), brug en af følgende formler:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Hvor:
For en detaljeret forklaring af formlerne, se:
- XLOOKUP med flere kriterier
- INDEX MATCH formel med flere kriterier
Sådan bruges VLOOKUP til at få 2., 3. eller nth match
Som du allerede ved, kan Excel VLOOKUP kun hente en matchende værdi, mere præcist, den returnerer det første fundne match. Men hvad hvis der er flere matches i dit opslagsarray, og du ønsker at få 2. eller 3. instans? Opgaven lyder ret indviklet, men løsningen findes!
Formel 1. Vlookup Nth-forekomst
Antag at du har kundenavne i en kolonne, de produkter, de købte i en anden, og du søger at finde det 2. eller 3. produkt købt af en given kunde.
Den enkleste måde er at tilføje en hjælpekolonne til venstre for tabellen, som vi gjorde i det første eksempel. Men denne gang vil vi udfylde det med kundenavne og forekomstnumre som “John Doe1”, “John Doe2” osv.
For at få forekomsten skal du bruge COUNTIF-funktionen med en blandet rækkevidde ( den første reference er absolut, og den anden er relativ som $ B $ 2: B2). Da den relative reference ændres baseret på en position i cellen, hvor formlen kopieres, bliver den i række 3 $ B $ 2: B3, i række 4 – $ B $ 2: B4 osv.
Sammenkædet med kundenavnet (B2) har formlen denne form:
=B2&COUNTIF($B$2:B2, B2)
Ovenstående formel går til A2, og så kopierer du det ned til så mange celler som nødvendigt.
Indtast derefter målnavnet og forekomstnummer i separate celler (F1 og F2), og brug nedenstående formel til at Vlookup en bestemt begivenhed:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formel 2. Vlookup 2. forekomst
Hvis du leder efter 2. forekomst af opslagsværdien, kan du undvære hjælpekolonnen. Opret i stedet tabelarrayet dynamisk ved hjælp af INDIRECT-funktionen sammen med MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Hvor:
- E1 er opslagsværdien
- A2: A11 er opslagsområdet
- B11 er den sidste (nederste højre) celle i opslagstabellen
Bemærk, at ovenstående formel er skrevet til et specifikt tilfælde, hvor dataceller i opslagstabellen begynder i række 2. Hvis din tabel er et eller andet sted i i midten af arket, brug denne universelle formel, hvor A1 er den øverste venstre celle i opslagstabellen, der indeholder en kolonneoverskrift:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Sådan fungerer denne formel
Her er den centrale del af formlen, der skaber et dynamisk vlookup-interval:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
MATCH-funktionen konfigureret til nøjagtig matchning (0 i det sidste argument) sammenligner målnavnet (E1) med listen over navne (A2: A11) og returnerer positionen for det først fundne match, som er 3 i o din sag. Dette nummer skal bruges som startrækkoordinat for vlookup-området, så vi tilføjer 2 til det (+1 for at ekskludere den første forekomst og +1 for at ekskludere række 1 med kolonneoverskrifterne). Alternativt kan du bruge 1 + ROW (A1) til automatisk at beregne den nødvendige justering baseret på positionen for headerrækken (A1 i vores tilfælde).
Som et resultat får vi følgende tekststreng, hvilken INDIRECT konverterer til en rækkehenvisning:
INDIRECT("A"&5&":B11") -> A5:B11
Dette interval går til argumentet table_array i VLOOKUP og tvinger det til at begynde at søge i række 5 udelader den første forekomst af opslagsværdien:
VLOOKUP(E1, A5:B11, 2, FALSE)
Sådan Vlookup og returnerer flere værdier i Excel
Excel VLOOKUP-funktionen er designet til kun at returnere et match. Er der en måde at Vlookup flere forekomster på? Ja, der er, men ikke let. Dette kræver en kombineret brug af flere funktioner såsom INDEX, SMALL og ROW er en matrixformel.
For eksempel kan nedenstående finde alle forekomster af opslagsværdien F2 i opslagsområdet B2: B16 og return flere matches fra kolonne C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Der er 2 måder at indtaste formlen på dit regneark:
- Indtast formlen i den første celle, tryk på Ctrl + Shift + Enter, og træk den derefter ned til et par flere celler.
- Vælg flere tilstødende celler i en enkelt kolonne (F1: F11 i skærmbilledet nedenfor), skriv formlen, og tryk på Ctrl + Shift + Enter for at fuldføre den.
Uanset hvad skal antallet af celler, hvor du indtaster formlen, være lig med eller større end det maksimale antal mulige matches.
For en detaljeret forklaring af formellogikken og flere eksempler, se Sådan VLOOKUP flere værdier i Excel .
Sådan Vlookup i rækker og kolonner (tovejs opslag)
Tovejs opslag (aka matrix opslag eller 2-dimensionel opslag) er et smukt ord for at slå en værdi op i skæringspunktet mellem en bestemt række og kolonne.Der er et par forskellige måder at foretage todimensionalt opslag i Excel, men da fokus i denne vejledning er på VLOOKUP-funktionen, vil vi naturligvis bruge det.
Til dette eksempel tager vi nedenstående tabel med månedligt salg og udarbejd en VLOOKUP-formel for at hente salgstallet for en bestemt vare i en given måned.
Med varenavne i A2: A9, månedsnavne i B1: F1, målgenstanden i I1 og målmåneden i I2 går formlen som følger:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Sådan fungerer denne formel
Kernen i formlen er standard VLOOKUP-funktionen, der søger efter en nøjagtig matchning med opslagsværdien i I1. Men da vi ikke ved i hvilken kolonne nøjagtigt salget for en bestemt måned er, kan vi ikke levere kolonnenummeret direkte til argumentet col_index_num. For at finde denne kolonne bruger vi følgende MATCH-funktion:
MATCH(I2, A1:F1, 0)
Oversat til engelsk siger formlen: slå op I2-værdien i A1: F1, og returner sin relative position i arrayet. Ved at levere 0 til det tredje argument instruerer du MATCH at finde værdien nøjagtigt lig med opslagsværdien (det er som at bruge FALSK til argumentet range_lookup for VLOOKUP).
Da Mar er i 4. kolonne i opslagsarray returnerer MATCH-funktionen 4, som går direkte til col_index_num-argumentet for VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Vær opmærksom at selvom månedsnavne starter i kolonne B, bruger vi A1: I1 til opslagsarray. Dette gøres for at antallet, der returneres af MATCH, skal svare til kolonnens position i tabelarray for VLOOKUP.
For at lære flere måder at udføre matrixopslag i Excel, se INDEX MATCH MATCH og andre formler for 2-dimensionel opslag.
Sådan gør du flere Vlookup i Excel (indlejret Vlookup)
Nogle gange kan det ske, at din hovedtabel og opslagstabel ikke har en enkelt kolonne til fælles, hvilket forhindrer dig i at lave en Vlookup mellem to tabeller. Der findes dog en anden tabel, der ikke indeholder de oplysninger, du leder efter, men har en fælles kolonne med hovedtabellen og en anden fælles kolonne med opslagstabellen.
I billedet nedenfor illustreres situationen:
Målet er at kopiere priser til hovedtabellen baseret på vare-ider. Problemet er, at tabellen, der indeholder priser, ikke har vare-iderne, hvilket betyder, at vi bliver nødt til at lave to Vlookups i en formel.
For nemheds skyld skal vi først oprette et par navngivne intervaller :
- Opslagstabel 1 hedder Produkter (D3: E3).
- Opslagstabel 2 hedder Priser (G3: H3).
Tabellerne kan være i samme eller forskellige regneark.
Og nu udfører vi den såkaldte dobbelt Vlookup, også kaldet nestet Vlookup.
Lav først en VLOOKUP formel for at finde produktnavnet i opslagstabel 1 (navngivne produkter) baseret på vare-id (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Derefter skal du placere ovenstående formel i lookup_value-argumentet for en anden VLOOKUP-funktion for at hente priser fra opslagstabel 2 (navngivet priser) baseret på produktnavnet returneret af den indlejrede VLOOKUP:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Nedenstående skærmbillede viser vores indlejrede Vlookup-formel i aktion:
Sådan Vlookup flere ark dynamisk
Nogle gange har du muligvis data i samme format fordelt på flere regneark. Og dit mål er at hente data fra et bestemt ark afhængigt af nøgleværdien i en given celle.
Dette kan være lettere at forstå ud fra et eksempel. Lad os sige, du har et par regionale salgsrapporter i samme format, og du ønsker at få salgstallene for et specifikt produkt i bestemte regioner:
Som i det foregående eksempel begynder vi med at definere et par navne:
- Område A2: B5 i CA-ark får navnet CA_Sales.
- Område A2 : B5 i FL-ark hedder FL_Sales.
- Område A2: B5 i KS-ark hedder KS_Sales.
Som du kan se, har alle de navngivne områder et fælles del (Salg) og unikke dele (CA, FL, KS). Sørg for at navngive dine intervaller på en lignende måde, da det er vigtigt for den formel, vi skal bygge.
Formel 1. INDIRECT VLOOKUP for dynamisk at hente data fra forskellige ark
Hvis din opgave er at hente data fra flere ark, er en VLOOKUP INDIRECT formel den bedste løsning – kompakt og let at forstå.
I dette eksempel organiserer vi oversigtstabellen på denne måde:
- Indtast produkterne af interesse i A2 og A3. Det er vores opslagsværdier.
- Indtast de unikke dele af de navngivne områder i B1, C1 og D1.
Og nu sammenkædes cellen, der indeholder den unikke del (B1), med den fælles del (“_Sales”) og føder den resulterende streng til INDIRECT:
INDIRECT(B$1&"_Sales")
Funktionen INDIRECT omdanner strengen til et navn, som Excel kan forstå, og du lægger den i argumentet table_array for VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Ovenstående formel går til B2, og derefter kopierer du den ned og til højre.
Vær opmærksom på, i opslagsværdien ($ A2) har vi låst kolonnekoordinaten med absolut cellereference, så kolonnen forbliver fast, når formlen kopieres til højre. I B $ 1-referencen låste vi rækken, fordi vi vil have kolonnen koordinere for at ændre og levere en passende navnedel til INDIRECT afhængigt af den kolonne, formlen kopieres til:
Hvis din hovedtabel er organiseret forskelligt er opslagsværdierne i en række og unikke dele af rækkevidden i en colu mn, så skal du låse rækkekoordinaten i opslagsværdien (B $ 1) og kolonnekoordinaten i navnedelene ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formel 2. VLOOKUP og indlejrede IFer for at slå flere ark op
I en situation, hvor du lige har to eller tre opslagark, kan du bruge en ret simpel VLOOKUP-formel med indlejrede IF-funktioner til at vælge det korrekte ark baseret på nøgleværdien i en bestemt celle:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Hvor $ A2 er opslagsværdien (varenavn) og B $ 1 er nøgleværdien (tilstand):
I dette tilfælde behøver du ikke nødvendigvis definere navne og kan bruge eksterne referencer til at henvise til et andet ark eller en projektmappe.
For flere eksempler på formler, se Sådan VLOOKUP på tværs af flere ark i Excel .
Sådan bruges VLOOKUP i Excel. Jeg takker dig for læsningen og håber at se dig på vores blog i næste uge.
Øv arbejdsmappe til download
Avancerede eksempler på VLOOKUP-formel (.xlsx-fil)
- Excel VLOOKUP-tutorial til begyndere
- Sådan Vlookup flere matches i Excel
- Sådan bruges VLOOKUP & SUM- eller SUMIF-funktioner i Excel
- INDEX & MATCH-funktioner i Excel – et bedre alternativ til VLOOKUP
- 4 måder at lave en sagsfølsom Vlookup i Excel
- Excel VLOOKUP fungerer ikke – N / A, NAME og VALUE fejl