Hur man använder VLOOKUP med flera kriterier i Excel

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

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

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

Hur man använder VLOOKUP med flera kriterier

Anta att du har en data med elevernas namn, tentamenstyp och matematikpoängen (som visas nedan):

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

Man kan argumentera för 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 har du fastnat för den data du har och pivottabellen kanske inte är ett alternativ.

I sådana fall är denna handledning för dig.

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

• Använda en hjälpkolumn.
• Använd funktionen VÄLJ.

VLOOKUP med flera kriterier - Använda en hjälparkolumn

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

Jag hittar två betydande fördelar med att använda hjälpkolumner framför 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).

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

För att komma tillbaka till frågan i punkt behövs hjälpkolumnen för att skapa ett unikt kval. Denna unika kvalificering kan sedan användas för att leta upp rätt värde. Till exempel finns det tre Matt i data, men det finns bara en kombination av Matt och Unit Test eller Matt och Mid-Term.

Här är stegen:

• Infoga en hjälparkolumn mellan kolumn B och C.
• Använd följande formel i hjälpkolumnen: = A2 & ”|” & B2
  • Detta skulle skapa unika kval för varje instans som visas nedan.
• Använd följande formel i G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
• Kopiera för alla celler.

Hur fungerar detta?

Vi skapar unika kval för varje instans av ett namn och provet. 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. Exempelvis är uppslagsvärdet för VLOOKUP -funktionen i G2 Matt | Unit Test. Nu används detta uppslagsvärde för att få poängen från C2: D19.

Förtydliganden:

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

• Varför har jag använt | symbol när du förenar de två kriterierna? - I vissa exceptionellt sällsynta (men möjliga) tillstånd kan du ha två kriterier som är olika men 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, så blir kombinationerna desamma. Men om du använder en separator, så skulle även kombinationen vara annorlunda (D2 och D3).

• Varför satte jag in hjälparkolonnen mellan kolumn B och C och inte i extremvänstern? - Det skadar inte att sätta in hjälpkolonnen längst till vänster. Faktum är att om du inte vill dämpa originalinformationen borde det vara vägen att gå. Jag gjorde det eftersom det får mig att använda färre antal celler i VLOOKUP -funktionen. Istället för att ha 4 kolumner i tabellmatrisen kunde jag klara mig med bara 2 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älparkolumn när de använder VLOOKUP med flera kriterier.

Så här är kolumnmetoden utan hjälp för dig.

Ladda ner exempelfilen

VLOOKUP med flera kriterier - Använda VÄLJ -funktionen

Genom att använda matrisformler i stället för hjälpkolumner sparar du arbetsbladets fastigheter och prestandan kan vara lika bra om den används mindre antal gånger i en arbetsbok.

Med tanke på samma datauppsättning som används 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 kan du använda den med Ctrl + Skift + Retur, istället för bara Enter.

Hur fungerar detta?

Formeln använder också begreppet en hjälparkolonn. Skillnaden är att istället för att placera hjälpkolumnen i kalkylbladet, betrakta det som virtuella hjälpdata som är en del av formeln.

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

I ovanstående illustration, när jag väljer VÄLJ delen av formeln och trycker på F9, visar det resultatet som VÄLJ formeln 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 2 kolumner med data - En kolumn har den unika identifieraren och en har poängen.

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

Ladda ner exempelfilen

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

Finns det något annat sätt du vet att göra detta? Om ja, dela med mig i kommentarsfältet.

Du kanske också gillar följande LOOKUP -handledning:

• VLOOKUP Vs. INDEX/MATCH
• Få flera uppslagsvärden utan upprepning i en enda cell.
• Hur man gör VLOOKUP skiftlägeskänslig.
• Använd IFERROR med VLOOKUP för att bli av med #N/A -fel.