Det er let at finde værdi med en unik nøgle i en tabel. Vi kan simpelthen bruge VLOOKUP -funktionen. Men når du ikke har den unikke kolonne i dine data og skal slå op i flere kolonner for at matche en værdi, hjælper VLOOKUP ikke.
Så for at slå en værdi op i en tabel med flere kriterier vil vi bruge INDEX-MATCH-INDEX formel.
Generisk formel til opslag med flere kriterier
= INDEX (opslagsområde, MATCH (1, INDEX ((kriterium1 = område1)*(kriterium2 = område2)*(kriterierN = områdeN), 0,1), 0))
opslag_range: Det er det område, hvorfra du vil hente værdi.
Kriterier1, Kriterier2, Kriterier N: Dette er de kriterier, du vil matche i område 1, område 2 og område N. Du kan have op til 270 kriterier - områdepar.
Område1, område2, områdeN: Dette er de områder, hvor du vil matche dine respektive kriterier.
Hvordan vil det fungere? Lad os se…
INDEX og MATCH med flere kriterier
Her har jeg en datatabel. Jeg vil trække kundens navn ved hjælp af bookingdato, bygherre og område. Så her har jeg tre kriterier og et opslagsområde.
Skriv denne formel i celle I4, tryk på enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Hvordan det virker:
Vi ved allerede, hvordan INDEX og MATCH -funktion fungerer i EXCEL, så jeg vil ikke forklare det her. Vi vil tale om det trick, vi brugte her.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hoveddelen er dette. Hver del af denne sætning returnerer en række sand falsk.
Når boolske værdier multipliceres, returnerer de array på 0 og 1. Multiplikation fungerer som AND -operator. Hense når alle værdier kun er sande, returnerer den 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Dette vil helt tilbage
{FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK;* SAND} FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK}
Hvilket vil oversætte til
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): INDEX -funktionen returnerer det samme array ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}) til MATCH -funktion som opslagsarray.
MATCH(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funktionen leder efter 1 i array {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}. Og vil returnere indeksnummer for de første 1 fundet i array. Hvilket er 8 her.
INDEKS(E2: E16,MATCH(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Endelig returnerer INDEX værdi fra et givet område (E2: E16) ved fundet indeks (8).
Enkel????. Beklager, kunne ikke gøre det mere enkelt.
Array løsning
Hvis du derfor kan trykke CTRL + SKIFT + ENTER, kan du fjerne den indre INDEX -funktion. Bare skriv denne formel og tryk på CTRL + SKIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Generisk matrixformel til opslag med flere kriterier
= INDEX (opslagsområde, MATCH (1, (kriterium1 = område1)*(kriterium2 = område2)*(kriterierN = områdeN), 0))
Formlen fungerer på samme måde som ovenstående forklaring.
Jeg prøvede mit bedste for at forklare det så enkelt som muligt. Men hvis jeg ikke var klar nok, så lad mig vide det i kommentarfeltet herunder. I øvrigt behøver du ikke vide, hvordan motoren fungerer for at køre en bil. Du skal bare vide, hvordan du kører den. Og du kender det udmærket.
Sådan søges Top 5-værdier med dublerede værdier ved hjælp af INDEX-MATCH i Excel
Sådan VLOOKUP flere værdier i Excel
Sådan VLOOKUP med Dynamic Col Index i Excel
Sådan bruges VLOOKUP fra to eller flere opslagstabeller i Excel
Populære artikler:
50 Excel -genveje til at øge din produktivitet
Sådan bruges VLOOKUP -funktionen i Excel
Sådan bruges funktionen COUNTIF i Excel
Sådan bruges SUMIF -funktionen i Excel