VLOOKUP på två eller flera kriteriekolumner – Excel University
Om du någonsin har försökt använda en VLOOKUP-funktion med två eller flera kriteriekolumner, har du snabbt upptäckt att det byggdes bara inte för det ändamålet. Lyckligtvis finns det en annan funktion som kan fungera som ett alternativ till VLOOKUP beroende på vad du vill returnera.
Flerkolonnslagsmål
Låt oss först bekräfta vårt mål genom att titta på ett exempel på en arbetsbok. Vi har exporterat lite information från vårt redovisningssystem, och det sammanfattar i princip transaktionssummorna för månaden efter klass och per konto. Ett exempel på exporten visas nedan:
Från denna exporterade data vill vi hämta utvalda mängder baserat på klass- och kontokolumner. Vi vill hämta beloppen och placera dem i vår lilla rapport, bilden nedan:
Om du är bekant med VLOOKUP funktion känns det naturligt att försöka bygga rapporten med den här funktionen eftersom det trots allt är en uppslagningsuppgift. Och uppslagsuppgifter löses bäst med traditionella uppslagsfunktioner … eller hur? Det beror på. Det beror på vad du försöker hämta.
Villkorlig summering för uppslag
Om du försöker hämta ett numeriskt värde, till exempel ett belopp, kanske en traditionell uppslagsfunktion inte var din bästa insats. Här är varför. Från och med Excel 2007 inkluderade Microsoft den villkorliga summeringsfunktionen SUMIFS. Denna summeringsfunktion för flera villkor är utformad för att lägga till en kolumn med siffror och inkluderar endast rader som uppfyller ett eller flera villkor. Börjar prickarna att anslutas ännu?
Om vi tillämpar denna idé på vår uppgift, skulle vi snabbt inse att vi skulle kunna använda denna villkorliga summeringsfunktion för att hämta våra rapportvärden.
Det första argumentet för SUMIFS-funktionen är summan, det vill säga kolumnen med siffror som ska läggas till. I vårt fall kolumnen som har det värde vi vill returnera. De återstående argumenten kommer i par: kriterieintervallet och kriterievärdet.
Det är bra att tänka på funktionen i dessa termer: lägg till den här kolumnen (argument 1), bara inkludera de rader där den här kolumnen (argument 2) är lika med detta värde (argument 3), och där denna kolumn (argument 4) är lika med detta värde (argument 5), och där … och så vidare, upp till 127 par.
För att fylla i vår rapport hämtar vi således beloppsvärdena från exporten och matchar klass- och kontokolumnerna, som visas nedan.
Om det råkar finnas flera rader med samma klass och konton, skulle SUMIFS-funktionen returnera summan av alla matchande objekt.
Som du kan se, om värdet du försöker returnera är ett tal, då gör SUMIFS-funktionen det enkelt att utföra flerkolonnsökningar. Men tänk om värdet du försöker returnera inte är ett nummer? Tja, då måste du använda en traditionell uppslagsfunktion som diskuteras nedan.
Använda VLOOKUP med SUMIFS-metod
En metod är att använda VLOOKUP och SUMIFS i en enda formel. I huvudsak använder du SUMIFS som det första argumentet för VLOOKUP. Denna metod utforskas fullständigt i detta Excel University-inlägg:
Använda VLOOKUP med CONCATENATE Method
Om du försöker returnera en textsträng snarare än ett nummer, eller använder en version av Excel som inte har SUMIFS, då är du förmodligen fast med att använda en traditionell uppslagsfunktion som VLOOKUP tillsammans med CONCATENATE-funktionen för att generera en enda unik uppslagningskolumn. Det här tillvägagångssättet är ganska väldokumenterat, men grundidén går så här: skapa en enda uppslagningskolumn först och använd sedan VLOOKUP.
Vårt exempel kommer att vara en anställdslista, som illustreras nedan:
Vi måste hämta staten från medarbetarlistan för vår lilla rapport som visas nedan:
Eftersom värdet vi försöker returnera, staten, är en textsträng och inte ett tal, är vi uteslutna från att använda SUMIFS-funktionen. Därför måste vi gå i skolan med VLOOKUP och CONCATENATE.
Vi börjar med att bygga en hjälpkolumn som i grunden skapar de kombinerade uppslagsvärdena. Detta kan enkelt åstadkommas med CONCATENATE-funktionen eller med sammanfogningsoperatören (&). Denna nya uppslagskolumn illustreras i kolumn B nedan:
Nu har vi en enda uppslagningskolumn som kan användas med en traditionell uppslagsfunktion som VLOOKUP. Rapporten kan fyllas genom att slå upp de kombinerade namnen inom det nya sökningsområdet, som visas nedan:
Samma metod kan användas när två, tre eller fler uppslagningskolumner måste övervägas.
Slutsats
Förutom att kunna utföra flerkolonnsökningar när returvärdet är numeriskt har SUMIFS-funktionen ytterligare fördelar jämfört med traditionella sökfunktioner. Till exempel returnerar den noll när inget matchningsvärde hittas, den returnerar summan av alla matchningar, den stöder jämförelseoperatörer och den bryts inte när en ny kolumn infogas mellan uppslagningskolumnerna.
Så när du håller på att ta bort VLOOKUP-funktionen för att göra en sökningsuppgift, överväg att använda SUMIFS istället. Tro det eller inte, SUMIFS-funktionen är en underbar uppslagsfunktion.
Om du har några andra föredragna metoder för flerkolonnuppslag, skulle vi gärna höra mer … skriv en kommentar nedan.
Exempelfil
Om du vill spela med arbetsboken som används för att skapa skärmdumparna ovan, är du välkommen att ladda ner exempelfilen:
MultColumnLookup