Lako je potražiti vrijednost s jednim jedinstvenim ključem u tablici. Možemo jednostavno koristiti funkciju VLOOKUP. No, kada nemate taj jedinstveni stupac u svojim podacima i trebate tražiti u više stupaca kako bi odgovarali vrijednosti, VLOOKUP ne pomaže.
Dakle, za traženje vrijednosti u tablici s više kriterija koristit ćemo formulu INDEX-MATCH-INDEX.
Opća formula za traženje više kriterija
= INDEX (opseg_pregledavanja, MATCH (1, INDEX ((kriteriji1 = raspon1)**kriterij2 = raspon2)*(kriterijN = rasponN), 0,1), 0))
opseg_potraživanja: Njegov raspon iz kojeg želite dohvatiti vrijednost.
Kriterij1, kriterij2, kriterij N: Ovo su kriteriji kojima želite odgovarati u rasponu1, rasponu2 i rasponu N. Možete imati do 270 kriterija - parova raspona.
Raspon1, raspon2, rasponN: Ovo su rasponi u kojima ćete odgovarati svojim kriterijima.
Kako će to funkcionirati? Da vidimo…
INDEKS i MATCH s primjerom više kriterija
Ovdje imam tablicu podataka. Želim povući ime korisnika koristeći Datum rezervacije, Graditelja i Površinu. Dakle, ovdje imam tri kriterija i jedan raspon pretraživanja.
Upišite ovu formulu u ćeliju I4 pritisnite enter.
= INDEKS (E2: E16, MATCH (1, INDEX ((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0))
Kako radi:
Već znamo kako funkcije INDEX i MATCH rade u EXCEL -u, pa to neću ovdje objašnjavati. Govorit ćemo o triku koji smo ovdje upotrijebili.
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16): Glavni dio je ovaj. Svaki dio ove izjave vraća niz istina lažno.
Kad se logičke vrijednosti pomnože, vraćaju niz od 0 i 1. Množenje radi kao operator AND. Dakle, kad su sve vrijednosti istinite, tada vraća 1 else 0
(I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16) Ovo će se u potpunosti vratiti
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}* {FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; ; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}* {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Što će se prevesti u
{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): Funkcija INDEX vratit će isti niz ({0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}) na MATCH funkciju kao niz za pretraživanje.
UTAKMICA(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1): MATCH funkcija će tražiti 1 u nizu {0; 0; 0; 0; 0; 0 ; 0; 1; 0; 0; 0; 0; 0; 0; 0}. I vratit će indeksni broj prvih 1 pronađenih u nizu. Što je ovdje 8.
INDEKS(E2: E16,UTAKMICA(1,INDEKS((I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0,1), 0)): Konačno, INDEX će vratiti vrijednost iz zadanog raspona (E2: E16) kada se pronađe indeks (8).
Jednostavan????. Nažalost, ne mogu učiniti jednostavnijim.
Rješenje za niz
Ako posljedično možete pritisnuti CTRL + SHIFT + ENTER, tada možete ukloniti unutarnju funkciju INDEX. Samo napišite ovu formulu i pritisnite CTRL + SHIFT + ENTER.
= INDEKS (E2: E16, MATCH (1, (I1 = A2: A16)*(I2 = B2: B16)*(I3 = C2: C16), 0))
Opća formula niza za traženje više kriterija
= INDEX (opseg_pregledavanja, MATCH (1, (kriterij1 = raspon1)*(kriterij2 = raspon2)*(kriterijN = rasponN), 0))
Formula radi isto kao gore objašnjenje.
Dao sam sve od sebe da to objasnim što jednostavnije. Ali ako nisam bio dovoljno jasan, javite mi to u odjeljku komentara ispod. Usput, ne morate znati kako motor radi da biste vozili automobil. Samo trebate znati voziti. I ti to jako dobro znaš.
Kako pronaći top 5 vrijednosti s dvostrukim vrijednostima pomoću INDEX-MATCH u Excelu
Kako VLOOKUP više vrijednosti u Excelu
Kako VLOOKUP s Dynamic Col Index u Excelu
Kako koristiti VLOOKUP iz dvije ili više tablica za pretraživanje u Excelu
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti
Kako koristiti funkciju VLOOKUP u Excelu
Kako koristiti funkciju COUNTIF u Excelu
Kako koristiti funkciju SUMIF u Excelu