Titta på video – Hur man använder VLOOKUP-funktionen med flera kriterier

Excel VLOOKUP-funktionen, i sin grundform, kan leta efter ett uppslagsvärde och returnera motsvarande värde från den angivna raden.

Men det finns ofta ett behov av att använda Excel VLOOKUP med flera kriterier.

Så här använder du VLOOKUP med flera kriterier

Antag att du har data med elevernas namn, tentamen och matematikpoängen (som visas nedan):

Att använda VLOOKUP-funktionen för att få matematikpoängen för varje elev för respektive examen kan vara en utmaning.

Man kan argumentera att ett bättre alternativ skulle vara att omstrukturera datamängden eller använda en pivottabell. Om det fungerar för dig, inget liknande. Men i många fall är du fast med den data du har och pivottabellen kanske inte är ett alternativ.

I sådana fall är den här självstudien för dig.

Nu finns det två sätt du kan få uppslagsvärdet med VLOOKUP med flera kriterier.

• Med hjälp av en hjälpkolumn.
• Med funktionen VÄLJ.

VLOOKUP med flera kriterier – med hjälp av en hjälpkolumn

Jag är ett fan av hjälpkolumner i Excel.

Jag hittar två betydande fördelar med att använda hjälpkolumner över arrayformler:

• Det gör det enkelt att förstå vad som händer i kalkylbladet.
• Det gör det snabbare jämfört med arrayfunktionerna (märks i stora datamängder).

Missförstå mig inte nu. Jag är inte emot matrisformler. Jag älskar de fantastiska saker som kan göras med matrisformler. Det är bara att jag sparar dem för speciella tillfällen när alla andra alternativ inte är till hjälp.

När vi kommer tillbaka till frågan i fråga behövs hjälparpelaren för att skapa en unik kvalificering. Denna unika kvalificering kan sedan användas för att slå upp rätt värde. Det finns till exempel tre Matt i data, men det finns bara en kombination av Matt och Unit Test eller Matt och Mid-Term.

Här är stegen:

Hur fungerar det?

Vi skapar unika kvalificeringar för varje instans av ett namn och tentamen. I VLOOKUP-funktionen som används här ändrades uppslagsvärdet till $ F3 & ”|” & G $ 2 så att båda uppslagskriterierna kombineras och används som ett enda uppslagsvärde. Till exempel är uppslagsvärdet för VLOOKUP-funktionen i G2 Matt | Enhetstest. Nu används detta sökvärde för att få poängen från C2: D19.

Förtydliganden:

Det finns ett par frågor som troligtvis kommer att tänka på dig, så jag trodde att jag försöker svara på det här:

• Varför har jag använt | symbol när du går med i de två kriterierna? – Under vissa exceptionellt sällsynta (men möjliga) förhållanden kan det hända att du har två kriterier som är olika men som slutar ge samma resultat när de kombineras. Här är ett mycket enkelt exempel (förlåt mig för min brist på kreativitet här):

Observera att medan A2 och A3 är olika och B2 och B3 är olika, kombinationerna blir desamma. Men om du använder en separator, skulle även kombinationen vara annorlunda (D2 och D3).

• Varför satte jag in hjälpkolumnen mellan kolumn B och C och inte längst till vänster? – Det finns ingen skada att sätta in hjälpkolonnen längst till vänster. Faktum är att om du inte vill temperera med originaldata, bör det vara vägen att gå. Jag gjorde det eftersom det får mig att använda mindre antal celler i VLOOKUP-funktionen. Istället för att ha fyra kolumner i tabellmatrisen kunde jag hantera med bara två kolumner. Men det är bara jag.

Nu finns det ingen storlek som passar alla. Vissa människor kanske föredrar att inte använda någon hjälpkolumn när de använder VLOOKUP med flera kriterier.

Så här är den icke-hjälparkolonnmetoden för dig.

Ladda ner exempelfilen

VLOOKUP med flera kriterier – Använda CHOOSE-funktionen

Använda arrayformler istället för hjälpare kolumner sparar kalkylbladets fastigheter, och prestandan kan vara lika bra om den används mindre gånger i en arbetsbok.

Med tanke på samma datamängd som ovan, här är formeln som ger dig resultatet :

= VLOOKUP ($ E3 & ”|” & F $ 2, VÄLJ ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Eftersom detta är en matrisformel, använd den med Control + Shift + Enter istället för bara Enter.

Hur fungerar det?

Formeln använder också begreppet en hjälpkolumn. Skillnaden är att i stället för att placera hjälpkolumnen i kalkylbladet, betrakta den som virtuell hjälpdata som är en del av formeln.

Låt mig visa dig vad jag menar med virtuella hjälpardata.

I ovanstående illustration, när jag väljer VÄLJ delen av formeln och trycker på F9, visar det resultatet att VÄL formel skulle ge.

Resultatet är {”Matt | Enhetstest”, 91; ”Bob | Enhetstest”, 52; ……}

Det är en matris där ett komma representerar nästa cell i samma rad och semikolon representerar att följande data finns i nästa kolumn. Därför skapar denna formel två kolumner med data – En kolumn har den unika identifieraren och en har poängen.

Nu , när du använder VLOOKUP-funktionen, letar den helt enkelt efter värdet i den första kolumnen (av denna virtuella 2-kolumndata) och returnerar motsvarande poäng.

Ladda ner Exempelfil

Du kan också använda andra formler för att söka efter flera kriterier (som INDEX / MATCH eller SUMPRODUCT).