Det er lett å finne verdien med en unik nøkkel i en tabell. Vi kan ganske enkelt bruke VLOOKUP -funksjonen. Men når du ikke har den unike kolonnen i dataene dine og trenger å slå opp i flere kolonner for å matche en verdi, hjelper VLOOKUP ikke.
Så, for å slå opp en verdi i en tabell med flere kriterier, vil vi bruke INDEX-MATCH-INDEX-formelen.
Generisk formel for flere kriterier
= INDEX (oppslagsområde, MATCH (1, INDEX ((kriterium1 = område1)*(kriterier2 = område2)*(kriterierN = områdeN), 0,1), 0))
oppslag_område: Det er området du vil hente verdien fra.
Kriterier1, Kriterier2, Kriterier N: Dette er kriteriene du vil matche i område 1, område 2 og område N. Du kan ha opptil 270 kriterier - områdepar.
Område1, område2, områdeN: Dette er områdene der du vil matche dine respektive kriterier.
Hvordan vil det fungere? La oss se…
INDEX og MATCH med flere kriterier
Her har jeg en datatabell. Jeg vil trekke navnet på kunden ved å bruke Date of Booking, Builder og Area. Så her har jeg tre kriterier og ett oppslagsområde.
Skriv denne formelen i celle I4, trykk enter.
= INDEKS (E2: E16, MATCH (1, INDEKS ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Hvordan det fungerer:
Vi vet allerede hvordan INDEX og MATCH -funksjonen fungerer i EXCEL, så jeg skal ikke forklare det her. Vi vil snakke om trikset vi brukte her.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Hoveddelen er dette. Hver del av denne setningen returnerer en rekke sanne usanne.
Når boolske verdier multipliseres returnerer de matrisen 0 og 1. Multiplikasjon fungerer som AND -operator. Hense når alle verdier er sanne bare da returnerer den 1 annen 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Dette vil helt tilbake
{FALSK; FALSK; FALSK; FALSK; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}* {FALSK; FALSK; FALSK; SANN; SANN; SANN; SANN; FALSK; FALSK; FALSK; FALSK; FALSK* SANN}* {FALSK; FALSK; FALSK; SANN; FALSK; FALSK; SANN; FALSK; FALSK; FALSK; FALSK
Som vil oversette 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 -funksjonen returnerer samme matrise ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}) til MATCH -funksjonen som oppslagsarray.
KAMP(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH -funksjonen vil se etter 1 i matrisen {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}. Og vil returnere indeksnummeret for de første 1 funnet i array. Som er 8 her.
INDEKS(E2: E16,KAMP(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Endelig vil INDEX returnere verdi fra gitt område (E2: E16) ved funnet indeks (8).
Enkel????. Beklager, kunne ikke gjøre det enklere.
Array -løsning
Hvis du kan trykke CTRL + SKIFT + ENTER følgelig, kan du eliminere den indre INDEX -funksjonen. Bare skriv denne formelen og trykk CTRL + SKIFT + ENTER.
= INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Generisk matriseformel for flere kriterier
= INDEX (oppslagsområde, MATCH (1, (kriterium1 = område1)*(kriterier2 = område2)*(kriterierN = områdeN), 0))
Formelen fungerer på samme måte som forklaringen ovenfor.
Jeg prøvde så godt jeg kunne å forklare det så enkelt som mulig. Men hvis jeg ikke var tydelig nok, la meg få vite det i kommentarfeltet nedenfor. Forresten, du trenger ikke å vite hvordan motoren fungerer for å kjøre bil. Du trenger bare å vite hvordan du kjører den. Og du vet det veldig godt.
Slik søker du opp de fem verdiene med dupliserte verdier ved å bruke INDEX-MATCH i Excel
Hvordan VLOOKUP flere verdier i Excel
Hvordan VLOOKUP med Dynamic Col Index i Excel
Slik bruker du VLOOKUP fra to eller flere oppslagstabeller i Excel
Populære artikler:
50 Excel -snarveier for å øke produktiviteten
Slik bruker du VLOOKUP -funksjonen i Excel
Slik bruker du COUNTIF -funksjonen i Excel
Slik bruker du SUMIF -funksjonen i Excel
