Det är lätt att leta efter värde med en unik nyckel i en tabell. Vi kan helt enkelt använda VLOOKUP -funktionen. Men när du inte har den unika kolumnen i din data och behöver leta i flera kolumner för att matcha ett värde, hjälper VLOOKUP inte.
Så för att leta upp ett värde i en tabell med flera kriterier använder vi INDEX-MATCH-INDEX-formeln.
Generisk formel för flera kriterier
= INDEX (lookup_range, MATCH (1, INDEX ((kriterium1 = område1)*(kriterier2 = intervall2)*(kriterierN = intervallN), 0,1), 0))
lookup_range: Det är intervallet från vilket du vill hämta värdet.
Kriterier1, Kriterier2, Kriterier N: Det här är de kriterier du vill matcha i intervall1, intervall2 och intervall N. Du kan ha upp till 270 kriterier - intervallpar.
Område1, intervall2, intervallN: Detta är de områden där du kommer att matcha dina respektive kriterier.
Hur kommer det att fungera? Låt oss se…
INDEX och MATCH med exempel på flera kriterier
Här har jag en datatabell. Jag vill dra kundens namn med bokningsdatum, byggare och område. Så här har jag tre kriterier och ett uppslagsområde.
Skriv denna formel i cell I4 tryck enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Hur det fungerar:
Vi vet redan hur INDEX och MATCH fungerar i EXCEL, så jag kommer inte att förklara det här. Vi kommer att prata om tricket vi använde här.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Huvuddelen är detta. Varje del av detta uttalande returnerar en rad sanna falska.
När booleska värden multipliceras returnerar de matrisen 0 och 1. Multiplikation fungerar som OCH -operatör. Hense när allt värde är sant först då returnerar det 1 annat 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Detta kommer helt tillbaka
{FALSKT; FALSKT; FALSKT / FALSKT / FALSKT / FALSKT; FALSKT; FALSKT; FALSKT; FALSKT
Som kommer att översättas till
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEX -funktionen returnerar samma array ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) till MATCH -funktionen som uppslagningsarray.
MATCH(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktionen letar efter 1 i array {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Och kommer att returnera indexnummer för de första 1 som finns i arrayen. Vilket är 8 här.
INDEX(E2: E16,MATCH(1,INDEX((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Slutligen returnerar INDEX värde från ett givet intervall (E2: E16) vid hittat index (8).
Enkel????. Förlåt, kunde inte göra det enklare.
Array -lösning
Om du kan slå CTRL + SKIFT + ENTER följaktligen kan du eliminera den inre INDEX -funktionen. Skriv bara den här formeln och tryck på CTRL + SKIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Generisk matrisformel för flera kriterier
= INDEX (lookup_range, MATCH (1, (kriterium1 = område1)*(kriterier2 = intervall2)*(kriterierN = intervallN), 0))
Formeln fungerar på samma sätt som ovanstående förklaring.
Jag gjorde mitt bästa för att förklara det så enkelt som möjligt. Men om jag inte var tillräckligt tydlig, låt mig veta det i kommentarsfältet nedan. Förresten, du behöver inte veta hur motorn fungerar för att köra en bil. Du behöver bara veta hur du kör den. Och du vet det mycket väl.
Hur man letar upp de fem bästa värdena med dubblettvärden med hjälp av INDEX-MATCH i Excel
Hur man VLOOKUP flera värden i Excel
Hur man VLOOKUP med Dynamic Col Index i Excel
Hur man använder VLOOKUP från två eller fler uppslagstabeller i Excel
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet
Så här använder du VLOOKUP -funktionen i Excel
Så här använder du funktionen COUNTIF i Excel
Hur man använder SUMIF -funktionen i Excel