Dessa exempel lär dig hur man Vlookup flera kriterier, returnerar en specifik instans eller alla matchningar, gör dynamisk Vlookup i flera ark och mer.
Det är den andra delen av serien som hjälper dig att utnyttja kraften i Excel VLOOKUP. Exemplen antyder att du vet hur den här funktionen fungerar. Om inte, är det självklart att börja med de grundläggande användningarna av VLOOKUP i Excel.
Innan jag går vidare, låt mig kort påminna dig om syntaxen:
Nu när alla är på samma sida, låt oss titta närmare på de avancerade VLOOKUP-formelexemplen:
- Hur man Vlookup flera kriterier i Excel
- Vlookup och return nth match
- Vlookup flera värden
- Vlookup baserat på rad- och kolumnvärden
- Nestad Vlookup-formel
- VLOOKUP och INDIRECT för att hämta data från flera ark
Hur Vlookup flera kriterier
Excel VLOOKUP-funktionen är verkligen till hjälp när det gäller att söka i en databas för ett visst värde. Det saknar dock en viktig funktion – dess syntax möjliggör bara ett sökvärde. Men vad händer om du vill slå upp med flera villkor? Det finns några olika lösningar som du kan välja mellan.
Formel 1. VLOOKUP baserat på tw o värden
Antag att du har en lista med beställningar och vill hitta kvantiteten baserat på två kriterier, kundnamn och produkt. En komplicerande faktor är att varje kund beställde flera produkter, som visas i tabellen nedan:
En vanlig VLOOKUP-formel kommer inte att fungera i detta situation eftersom den returnerar den första hittade matchningen baserat på ett enskilt uppslagsvärde som du anger.
För att övervinna detta kan du lägga till en hjälpkolumn och sammanfoga värdena från två uppslagskolumner (kund och produkt) där. Det är viktigt att hjälpkolumnen ska vara kolumnen längst till vänster i tabellmatrisen eftersom det är där Excel VLOOKUP alltid söker efter uppslagsvärdet.
Så lägg till en kolumn till vänster om din tabell och kopiera nedanstående formel över den kolumnen. Detta fyller hjälpkolumnen med värdena från kolumnerna B och C (mellanslagstecken sammanfogas emellan för bättre läsbarhet):
=B2&" "&C2
Och använd sedan en vanlig VLOOKUP-formel och placera båda kriterierna i argumentet lookup_value, separerat med ett mellanslag:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Eller mata in kriterierna i separata celler (G1 och G2 i vårt fall) och sammanfoga dessa celler:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
När vi vill returnera ett värde från kolumn D, som är fjärde i tabellmatrisen, använder vi 4 för col_index_num. Argumentet range_lookup är inställt på FALSE till Vlookup en exakt matchning. Skärmdumpen nedan visar resultatet:
Om din uppslagstabell finns i ett annat ark, inkludera bladets namn i din VLOOKUP-formel. Till exempel:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativt kan du skapa ett namngivet intervall för uppslagstabellen (säg order) för att göra formeln enklare- att läsa:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Mer information finns i Vlookup från ett annat ark i Excel.
Formel 2. Excel VLOOKUP med flera villkor
I teorin kan du använda ovanstående metod för Vlookup mer än två kriterier. H owever, det finns ett par försiktigheter. För det första är ett sökningsvärde begränsat till 255 tecken, och för det andra kanske det inte går att lägga till en hjälpkolumn i kalkylbladets design.
Som tur är erbjuder Microsoft Excel ofta mer än ett sätt att göra samma sak. För att Vlookup flera kriterier kan du använda antingen en INDEX MATCH-kombination eller XLOOKUP-funktionen som nyligen introducerades i Office 365.
Till exempel för att slå upp baserat på tre olika värden (Datum, Kundnamn och Produkt), använd en av följande 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)
Var:
För detaljerad förklaring av formlerna, se:
- XLOOKUP med flera kriterier
- INDEX MATCH-formel med flera kriterier
Hur man använder VLOOKUP för att få 2: a, 3: e eller n: e matchning
Som du redan vet kan Excel VLOOKUP bara hämta ett matchande värde, mer exakt, det returnerar den första hittade matchningen. Men vad händer om det finns flera matchningar i din uppslagsmatris och du vill få 2: a eller 3: e instansen? Uppgiften låter ganska invecklad, men lösningen finns!
Formel 1. Vlookup Nth-instans
Antag att du har kundnamn i en kolumn, de produkter de köpte i en annan och du letar efter den 2: a eller 3: e produkten som köpts av en viss kund.
Det enklaste sättet är att lägga till en hjälpkolumn till vänster om tabellen som vi gjorde i det första exemplet. Men den här gången kommer vi att fylla i det med kundnamn och förekomstnummer som ”John Doe1”, ”John Doe2” etc.
För att få förekomsten använder du COUNTIF-funktionen med en referens för blandat intervall ( den första referensen är absolut och den andra är relativ som $ B $ 2: B2). Eftersom den relativa referensen ändras baserat på en position i cellen där formeln kopieras, blir den i rad 3 $ B $ 2: B3, i rad 4 – $ B $ 2: B4 och så vidare.
Sammankopplat med kundnamnet (B2) har formeln följande form:
=B2&COUNTIF($B$2:B2, B2)
Ovanstående formel går till A2 och sedan kopierar du ner det till så många celler som behövs.
Därefter anger du målnamnet och förekomstnumret i separata celler (F1 och F2) och använder formeln nedan för att Vlookup en specifik förekomst:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Formel 2. Vlookup 2: a förekomst
Om du letar efter den andra instansen av uppslagsvärdet kan du klara dig utan hjälpkolumnen. Skapa istället tabellmatrisen dynamiskt genom att använda INDIRECT-funktionen tillsammans med MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Var:
- E1 är uppslagsvärdet
- A2: A11 är uppslagsområdet
- B11 är den sista (nedre högra) cellen i uppslagstabellen
Observera att ovanstående formel är skriven för ett specifikt fall där dataceller i uppslagstabellen börjar i rad 2. Om din tabell är någonstans i mitt på arket, använd denna universella formel, där A1 är den övre vänstra cellen i uppslagstabellen som innehåller en kolumnrubrik:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Så här fungerar denna formel
Här är den viktigaste delen av formeln som skapar ett dynamiskt uppslagsområde:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
MATCH-funktionen som är konfigurerad för exakt matchning (0 i det sista argumentet) jämför målnamnet (E1) mot namnlistan (A2: A11) och returnerar positionen för den först hittade matchningen, vilket är 3 i o ditt fall. Detta nummer kommer att användas som startradkoordinat för vlookup-intervallet, så vi lägger till 2 till det (+1 för att utesluta den första instansen och +1 för att utesluta rad 1 med kolumnrubrikerna). Alternativt kan du använda 1 + RAD (A1) för att beräkna nödvändig justering automatiskt baserat på positionen för rubrikraden (A1 i vårt fall).
Som ett resultat får vi följande textsträng, vilken INDIRECT konverterar till en intervallreferens:
INDIRECT("A"&5&":B11") -> A5:B11
Detta intervall går till argumentet table_array för VLOOKUP och tvingar det att börja söka i rad 5, utesluter den första instansen av sökningsvärdet:
VLOOKUP(E1, A5:B11, 2, FALSE)
Hur man Vlookup och returnerar flera värden i Excel
Excel VLOOKUP-funktionen är utformad för att returnera bara en matchning. Finns det ett sätt att Vlookup flera instanser? Ja, det finns det, men inte lätt. Detta kräver en kombinerad användning av flera funktioner som INDEX, SMALL och ROW är en matrisformel.
Till exempel kan nedan hitta alla förekomster av uppslagsvärdet F2 i uppslagsområdet B2: B16 och return flera matchningar från kolumn C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Det finns två sätt att ange formeln i kalkylbladet:
- Skriv formeln i den första cellen, tryck på Ctrl + Skift + Enter och dra den sedan ner till några fler celler.
- Välj flera intilliggande celler i en enda kolumn (F1: F11 i skärmdumpen nedan), skriv formeln och tryck Ctrl + Skift + Enter för att slutföra den.
Hur som helst, antalet celler där du anger formeln ska vara lika med eller större än maximalt antal möjliga matchningar.
För en detaljerad förklaring av formellogiken och fler exempel, se Hur VLOOKUP flera värden i Excel .
Hur man Vlookup i rader och kolumner (tvåvägssökning)
Tvåvägssökning (aka matrisuppslag eller 2-dimensionell uppslagning) är ett snyggt ord för att leta upp ett värde vid skärningspunkten mellan en viss rad och kolumn.Det finns några olika sätt att göra tvådimensionell uppslagning i Excel, men eftersom fokus i denna handledning är på VLOOKUP-funktionen kommer vi naturligtvis att använda den.
För det här exemplet tar vi nedan tabell med månadsförsäljning och utarbeta en VLOOKUP-formel för att hämta försäljningssiffran för en viss artikel under en viss månad.
Med artikelnamn i A2: A9, månadsnamn i B1: F1, målvaran i I1 och målmånaden i I2 går formeln enligt följande:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Så här fungerar denna formel
Kärnan i formeln är VLOOKUP-standardfunktionen som söker en exakt matchning med uppslagsvärdet i I1. Men eftersom vi inte vet i vilken kolumn exakt försäljningen för en viss månad är, kan vi inte ange kolumnnumret direkt till argumentet col_index_num. För att hitta den kolumnen använder vi följande MATCH-funktion:
MATCH(I2, A1:F1, 0)
Översatt till engelska säger formeln: slå upp I2-värdet i A1: F1 och returnerar dess relativa position i matrisen. Genom att ange 0 till det tredje argumentet instruerar du MATCH att hitta värdet exakt lika med uppslagsvärdet (det är som att använda FALSE för argumentet range_lookup för VLOOKUP).
Eftersom Mar är i den fjärde kolumnen i uppslagsmatrisen returnerar MATCH-funktionen 4, som går direkt till argumentet col_index_num för VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Var uppmärksam att även om månadsnamnen börjar i kolumn B använder vi A1: I1 för uppslagsmatrisen. Detta görs för att antalet som returneras av MATCH ska motsvara kolumnens position i tabellarray för VLOOKUP.
För att lära dig fler sätt att utföra matrisuppslag i Excel, se INDEX MATCH MATCH och andra formler för tvådimensionell uppslagning.
Hur man gör flera Vlookup i Excel (kapslad Vlookup)
Ibland kan det hända att huvudtabellen och uppslagstabellen inte har en enda kolumn gemensamt, vilket hindrar dig från att göra en Vlookup mellan två tabeller. Det finns dock en annan tabell som inte innehåller den information du letar efter men har en gemensam kolumn med huvudtabellen och en annan vanlig kolumn med uppslagstabellen.
I bilden nedan illustreras situationen:
Målet är att kopiera priser till huvudtabellen baserat på artikel-ID. Problemet är att tabellen som innehåller priser inte har artikel-ID: n, vilket betyder att vi måste göra två Vlookups i en formel.
För enkelhets skull, låt oss skapa ett par namngivna områden först :
- Uppslagstabell 1 heter Produkter (D3: E3).
- Uppslagstabell 2 heter Priser (G3: H3).
Tabellerna kan vara i samma eller olika kalkylblad.
Och nu kommer vi att utföra den så kallade dubbla Vlookup, aka nestad Vlookup.
Först gör du en VLOOKUP formel för att hitta produktnamnet i uppslagstabellen 1 (namngivna produkter) baserat på artikel-id (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Lägg sedan ovanstående formel i argumentet lookup_value för en annan VLOOKUP-funktion för att dra priser från uppslagstabell 2 (med namnet Priser) baserat på produktnamnet som returneras av den kapslade VLOOKUP:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Skärmdumpen nedan visar vår kapslade Vlookup-formel i aktion:
Hur man Vlookup flera ark dynamiskt
Ibland kan du ha data i samma format fördelat på flera kalkylblad. Och ditt mål är att hämta data från ett visst ark beroende på nyckelvärdet i en viss cell.
Detta kan vara lättare att förstå från ett exempel. Låt oss säga, du har några regionala försäljningsrapporter i samma format och du vill få försäljningssiffrorna för en viss produkt i vissa regioner:
Som i föregående exempel börjar vi med att definiera några namn:
- Område A2: B5 i CA-ark heter CA_Sales.
- Område A2 : B5 i FL-ark heter FL_Sales.
- Område A2: B5 i KS-ark heter KS_Sales.
Som du kan se har alla nämnda intervall gemensamma del (Försäljning) och unika delar (CA, FL, KS). Var noga med att namnge dina intervall på ett liknande sätt eftersom det är viktigt för formeln vi ska bygga.
Formel 1. INDIRECT VLOOKUP för att dynamiskt hämta data från olika ark
Om din uppgift är att hämta data från flera ark är en VLOOKUP INDIRECT formel den bästa lösningen – kompakt och lätt att förstå.
För det här exemplet organiserar vi sammanfattningstabellen på detta sätt:
- Mata in produkter av intresse i A2 och A3. Det här är våra sökvärden.
- Ange de unika delarna av de angivna intervallen i B1, C1 och D1.
Och nu sammanfogar vi cellen som innehåller den unika delen (B1) med den gemensamma delen (”_Sales”) och matar den resulterande strängen till INDIRECT:
INDIRECT(B$1&"_Sales")
Funktionen INDIRECT omvandlar strängen till ett namn som Excel kan förstå, och du lägger den i argumentet table_array för VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Formeln ovan går till B2 och sedan kopierar du den ner och till höger.
Var uppmärksam på att i uppslagsvärdet ($ A2) har vi låst kolumnkoordinaten med absolut cellreferens så att kolumnen förblir fast när formeln kopieras till höger. I B $ 1-referensen låste vi raden eftersom vi vill ha kolumnen samordna för att ändra och leverera en lämplig namndel till INDIRECT beroende på kolumnen till vilken formeln kopieras:
Om din huvudtabell är organiserad på annat sätt, sökvärdena i rad och unika delar av intervallnamnen i en kolumn mn, då ska du låsa radkoordinaten i uppslagsvärdet (B $ 1) och kolumnkoordinaten i namndelarna ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Formel 2. SÖKNING och kapslade IF: er för att slå upp flera ark
I en situation när du just har två eller tre uppslagningsark kan du använda en ganska enkel VLOOKUP-formel med kapslade IF-funktioner för att välja rätt ark baserat på nyckelvärdet i en viss cell:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Där $ A2 är uppslagsvärdet (artikelnamn) och B $ 1 är nyckelvärdet (tillstånd):
I det här fallet behöver du inte nödvändigtvis definiera namn och kan använda externa referenser för att hänvisa till ett annat ark eller en arbetsbok.
För fler formelexempel, se Hur du SÖKAR över flera ark i Excel .
Så här använder du VLOOKUP i Excel. Jag tackar dig för att du läste och hoppas att vi ses på vår blogg nästa vecka!
Övningsarbetsbok för nedladdning
Avancerade VLOOKUP-formelexempel (.xlsx-fil)
- Excel VLOOKUP-handledning för nybörjare
- Hur Vlookup flera matchningar i Excel
- Hur man använder VLOOKUP & SUM- eller SUMIF-funktioner i Excel
- INDEX & MATCH-funktioner i Excel – ett bättre alternativ till VLOOKUP
- 4 sätt att göra en skiftlägeskänslig Vlookup i Excel
- Excel VLOOKUP fungerar inte – N / A, NAME och VALUE-fel