Zašto su INDEX i MATCH bolji od VLOOKUP-a u Excelu

Prethodno smo objasnili početnicima kako da koriste osnovne funkcije VLOOKUP-a (engleski VLOOKUP, skraćenica znači “vertical lookup function”). A iskusnim korisnicima je prikazano nekoliko složenijih formula.

I u ovom članku ćemo pokušati dati informacije o još jednoj metodi rada s vertikalnim pretraživanjem.

Možda se pitate: “Zašto je ovo potrebno?”. A to je neophodno kako bi se prikazale sve moguće metode pretraživanja. Osim toga, brojna ograničenja VLOOKUP-a često sprječavaju postizanje željenog rezultata. U tom smislu, INDEX() MATCH() je mnogo funkcionalniji i raznovrsniji, a imaju i manje ograničenja.

Osnove INDEX MATCH

Pošto je svrha ovog vodiča da pokaže koliko je ova funkcija dobra, mi Pogledajmo osnovne informacije o principima njegovog rada. I pokazat ćemo primjere, a također razmotriti zašto je to bolje od VLOOKUP ().

Sintaksa i upotreba funkcije INDEX

Ova funkcija pomaže u pronalaženju željene vrijednosti među navedenim područjima pretraživanja na osnovu broja kolone ili reda. Sintaksa:

=INDEX(niz, broj reda, broj kolone):

• niz – područje u kojem će se izvršiti pretraga;
• broj reda – broj reda koji se traži u navedenom nizu. Ako je broj reda nepoznat, mora se navesti broj kolone;
• broj kolone – broj kolone koja se nalazi u navedenom nizu. Ako je vrijednost nepoznata, potreban je broj reda.

Primjer jednostavne formule:

=INDEX(A1:S10,2,3)

Funkcija će tražiti u rasponu od A1 do C10. Brojevi pokazuju iz kojeg reda (2) i kolone (3) treba prikazati željenu vrijednost. Rezultat će biti ćelija C2.

Prilično jednostavno, zar ne? Ali kada radite sa stvarnim dokumentima, malo je vjerovatno da ćete imati informacije o brojevima kolona ili ćelijama. Tome služi funkcija MATCH().

Sintaksa i upotreba funkcije MATCH

Funkcija MATCH() traži željenu vrijednost i prikazuje njen približni broj u navedenom području pretraživanja.

Searchpos() sintaksa izgleda ovako:

=MATCH(vrijednost za traženje, niz za traženje, tip podudaranja)

• vrijednost pretrage – broj ili tekst koji treba pronaći;
• traženi niz – područje u kojem će se izvršiti pretraga;
• tip podudaranja – određuje hoće li se tražiti tačna vrijednost ili vrijednosti koje su joj najbliže:
  • 1 (ili nije navedena vrijednost) – vraća najveću vrijednost koja je jednaka ili manja od vrijednosti koja je specificirana;
  • 0 – prikazuje tačno podudaranje sa traženom vrijednošću. U kombinaciji INDEX() MATCH() će vam skoro uvijek trebati tačno podudaranje, tako da pišemo 0;
  • -1 – Prikazuje najmanju vrijednost koja je veća ili jednaka vrijednosti navedenoj u formuli. Sortiranje se vrši u opadajućem redosledu.

Na primjer, u rasponu B1:B3 registrirani su New York, Pariz, London. Formula ispod će pokazati broj 3 jer je London treći na listi:

=EXPOSE(London,B1:B3,0)

Kako raditi sa funkcijom INDEX MATCH 

Vjerovatno ste već počeli shvaćati princip po kojem se gradi zajednički rad ovih funkcija. Ukratko, onda INDEX() traži željenu vrijednost među navedenim redovima i stupcima. I MATCH() pokazuje brojeve ovih vrijednosti:

=INDEX(kolona iz koje se vraća vrijednost, MATCH(vrijednost za pretraživanje, kolona za pretraživanje, 0))

Još uvijek vam je teško razumjeti kako to funkcionira? Možda će neki primjer bolje objasniti. Pretpostavimo da imate listu svjetskih metropola i njihove populacije:

Da bismo saznali veličinu stanovništva određenog glavnog grada, na primjer, glavnog grada Japana, koristimo sljedeću formulu:

=INDEX(C2:C10, PODRŽI (Japan, A2:A10,0))

Objašnjenje:

• Funkcija MATCH() traži vrijednost – “Japan” u nizu A2:A10 i vraća broj 3, jer je Japan treća vrijednost na listi. 
• Ova cifra ide naredni broj” u formuli INDEX() i govori funkciji da ispiše vrijednost iz ovog reda.

Dakle, gornja formula postaje standardna formula INDEKS(C2:C10,3). Formula pretražuje od ćelija C2 do C10 i vraća podatke iz treće ćelije u ovom rasponu, odnosno C4, jer odbrojavanje počinje od drugog reda.

Ne želite da propišete ime grada u formuli? Zatim ga upišite u bilo koju ćeliju, recimo F1, i koristite ga kao referencu u formuli MATCH(). I na kraju ćete dobiti formulu za dinamičko pretraživanje:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Važno! Broj linija u array INDEX() mora biti isti kao i broj redova u razmatran niz u MATCH(), inače ćete dobiti pogrešan rezultat.

Čekajte malo, zašto jednostavno ne koristite formulu VLOOKUP()?

=VLOOKUP(F1, A2:C10, 3, False)

 Koja je svrha gubiti vrijeme pokušavajući shvatiti sve ove složenosti INDEX MATCH-a?

U ovom slučaju, nije važno koju funkciju koristiti. Ovo je samo primjer za razumijevanje kako funkcije INDEX() i MATCH() rade zajedno. Drugi primjeri će pokazati za šta su ove funkcije sposobne u situacijama kada je VLOOKUP nemoćan. 

INDEX MATCH ili VLOOKUP

Prilikom odlučivanja koju će formulu pretraživanja koristiti, mnogi se slažu da su INDEX() i MATCH() znatno superiorniji od VLOOKUP-a. Međutim, mnogi ljudi još uvijek koriste VLOOKUP(). Prvo, VLOOKUP() je jednostavniji, a drugo, korisnici ne razumiju u potpunosti sve prednosti rada sa INDEX() i MATCH(). Bez ovog znanja, niko neće pristati da troši svoje vreme proučavajući složen sistem.

Evo ključnih prednosti INDEX() i MATCH() u odnosu na VLOOKUP():

• Traži s desna na lijevo. VLOOKUP() ne može pretraživati ​​s desna na lijevo, tako da vrijednosti koje tražite uvijek moraju biti u krajnjim lijevom stupcu tabele. Ali INDEX() i MATCH() mogu ovo riješiti bez problema. Ovaj članak će vam reći kako to izgleda u praksi: kako pronaći željenu vrijednost na lijevoj strani.

1. Sigurno dodavanje ili uklanjanje kolona. Formula VLOOKUP() pokazuje netačne rezultate prilikom uklanjanja ili dodavanja kolona jer VLOOKUP() treba tačan broj kolone da bi bio uspješan. Naravno, kada se kolone dodaju ili uklanjaju, mijenjaju se i njihovi brojevi. 

A u formulama INDEX() i MATCH() naveden je raspon kolona, ​​a ne pojedinačni stupci. Kao rezultat, možete bezbedno dodavati i uklanjati kolone bez potrebe da svaki put ažurirate formulu.

1. Nema ograničenja u količini pretraživanja. Kada koristite VLOOKUP(), ukupan broj kriterija pretraživanja ne smije biti veći od 255 znakova ili ćete dobiti #VRIJEDNOST! Dakle, ako vaši podaci sadrže veliki broj znakova, INDEX() i MATCH() su najbolja opcija.
2. Velika brzina obrade. Ako su vaši stolovi relativno mali, malo je vjerovatno da ćete primijetiti bilo kakvu razliku. Ali, ako tabela sadrži stotine ili hiljade redova, i, shodno tome, postoje stotine i hiljade formula, INDEX () i MATCH () će se nositi mnogo brže od VLOOKUP (). Činjenica je da će Excel obraditi samo kolone navedene u formuli, umjesto da obrađuje cijelu tablicu. 

Uticaj VLOOKUP() na performanse će biti posebno primetan ako vaš radni list sadrži veliki broj formula kao što su VLOOKUP() i SUM(). Odvojene provjere funkcija VLOOKUP() su potrebne za raščlanjivanje svake vrijednosti u nizu. Dakle, Excel mora obraditi ogromnu količinu informacija, a to značajno usporava rad.

Primjeri formule 

Korisnost ovih funkcija već smo shvatili, pa možemo prijeći na najzanimljiviji dio: primjenu znanja u praksi.

Formula za pretraživanje s desna na lijevo

Kao što je već spomenuto, VLOOKUP ne može izvršiti ovaj oblik pretraživanja. Dakle, ako se željene vrijednosti ne nalaze u krajnjem lijevom stupcu, VLOOKUP() neće dati rezultat. Funkcije INDEX() i MATCH() su svestranije, a lokacija vrijednosti ne igra veliku ulogu za njihov rad.

Na primjer, na lijevu stranu naše tabele ćemo dodati stupac ranga i pokušati odgonetnuti koji rang po broju stanovnika zauzima glavni grad Naše zemlje.

U ćeliju G1 upisujemo vrijednost koju treba pronaći, a zatim koristimo sljedeću formulu za pretraživanje u rasponu C1:C10 i vraćamo odgovarajuću vrijednost iz A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Ako planirate koristiti ovu formulu za više ćelija, pobrinite se da popravite raspone koristeći apsolutno adresiranje (na primjer, $A$2: $A$10 i $C$2: 4C$10).

INDEX IZLOŽENIJI IZLOŽENIJI  za pretraživanje u kolonama i redovima

U gornjim primjerima koristili smo ove funkcije kao zamjenu za VLOOKUP() za vraćanje vrijednosti iz unaprijed definiranog raspona redova. Ali šta ako treba da uradite matrično ili dvostrano pretraživanje?

Zvuči komplikovano, ali formula za takve proračune je slična standardnoj formuli INDEX() MATCH(), sa samo jednom razlikom: formula MATCH() mora se koristiti dva puta. Prvi put da dobijete broj reda, a drugi put da dobijete broj kolone:

=INDEX(niz, MATCH(vrijednost okomitog pretraživanja, kolona za pretraživanje, 0), MATCH(vrijednost horizontalne pretrage, red za pretraživanje, 0))

Pogledajmo donju tabelu i pokušajmo napraviti formulu INDEX() EXPRESS() EXPRESS() kako bi se prikazali demografski podaci u određenoj zemlji za odabranu godinu.

Ciljna zemlja je u ćeliji G1 (vertikalna pretraga), a ciljna godina je u ćeliji G2 (horizontalno traženje). Formula će izgledati ovako:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Kako ova formula funkcionira

Kao i sa svim drugim složenim formulama, lakše ih je razumjeti razlaganjem u pojedinačne jednadžbe. I tada možete razumjeti šta svaka pojedinačna funkcija radi:

• PODRŽI (G1,A2:A11,0) – traži vrijednost (G1) u rasponu A2:A11 i prikazuje broj ove vrijednosti, u našem slučaju je 2;
• TRAŽI(G2,B1:D1,0) – traži vrijednost (G2) u rasponu B1:D1. U ovom slučaju rezultat je bio 3.

Pronađeni brojevi redova i stupaca šalju se na odgovarajuću vrijednost u formuli INDEX():

=INDEX(B2:D11,2,3)

Kao rezultat, imamo vrijednost koja se nalazi u ćeliji na sjecištu 2 reda i 3 stupca u rasponu B2:D11. I formula pokazuje željenu vrijednost, koja se nalazi u ćeliji D3.

Pretražujte po više uslova sa INDEX i MATCH

Ako ste pročitali naš vodič za VLOOKUP(), vjerovatno ste isprobali više formula za pretraživanje. Ali ova metoda pretraživanja ima jedno značajno ograničenje – potrebu za dodavanjem pomoćne kolone.

Ali dobra vijest je ta Sa INDEX() i MATCH() možete tražiti više uslova bez potrebe za uređivanjem ili promjenom radnog lista.

Evo opće formule za pretragu sa više uslova za INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Napomena: ova formula se mora koristiti zajedno sa prečicom na tastaturi CTRL+SHIFT+ENTER.

Pretpostavimo da trebate pronaći vrijednost koju tražite na osnovu 2 uslova: kupac и Proizvod.

Za to je potrebna sljedeća formula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

U ovoj formuli, C2:C10 je raspon u kojem će se pretraga odvijati, F1 – ovo stanje, A2:A10 — je raspon za upoređivanje stanja, F2 – uslov 2, V2:V10 – raspon za poređenje stanja 2.

Ne zaboravite pritisnuti kombinaciju na kraju rada sa formulom CTRL+SHIFT+ENTER – Excel će automatski zatvoriti formulu sa vitičastim zagradama, kao što je prikazano u primjeru:

Ako ne želite da koristite formulu niza za svoj rad, dodajte još jedan INDEX() formuli i pritisnite ENTER, izgledat će kao u primjeru:

Kako ove formule rade

Ova formula radi na isti način kao standardna formula INDEX() MATCH(). Da biste tražili više uslova, jednostavno kreirate višestruke lažne i tačne uslove koji predstavljaju ispravne i netačne pojedinačne uslove. I onda se ovi uslovi primjenjuju na sve odgovarajuće elemente niza. Formula pretvara argumente False i True u 0 i 1, respektivno, i daje niz gdje je 1 odgovarajuće vrijednosti koje su pronađene u nizu. MATCH() će pronaći prvu vrijednost koja odgovara 1 i proslijediti je formuli INDEX(). A on će zauzvrat vratiti već željenu vrijednost u navedenom redu iz željene kolone.

Formula bez niza zavisi od sposobnosti INDEX() da ih samostalno rukuje. Drugi INDEX() u formuli odgovara Falsy (0), tako da prosljeđuje cijeli niz sa tim vrijednostima u formulu MATCH(). 

Ovo je prilično dugačko objašnjenje logike iza ove formule. Za više informacija pročitajte članak “INDEX MATCH sa više uslova".

PROSJEK, MAX i MIN u INDEKS-u i MATCH

Excel ima svoje posebne funkcije za pronalaženje prosjeka, maksimuma i minimuma. Ali što ako želite dobiti podatke iz ćelije povezane s tim vrijednostima? U ovom slučaju AVERAGE, MAX i MIN moraju se koristiti zajedno sa INDEX i MATCH.

INDEX MATCH i MAX

Da biste pronašli najveću vrijednost u stupcu D i prikazali je u stupcu C, koristite formulu: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX UPOTREBA i MIN

Da biste pronašli najmanju vrijednost u koloni D i prikazali je u stupcu C, koristite sljedeću formulu:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

PRETRAŽI INDEX i SERPENT

Da biste pronašli prosječnu vrijednost u koloni D i prikazali ovu vrijednost u C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Ovisno o tome kako su vaši podaci napisani, treći argument za MATCH() je ili 1, 0 ili -1:

• ako su kolone sortirane uzlaznim redoslijedom, postavite 1 (onda će formula izračunati maksimalnu vrijednost, koja je manja ili jednaka prosječnoj vrijednosti);
• ako je sortiranje opadajuće, onda -1 (formula će dati minimalnu vrijednost koja je veća ili jednaka prosjeku);
• ako niz pretraživanja sadrži vrijednost koja je točno jednaka prosjeku, onda je postavite na 0. 

 U našem primjeru, populacija je sortirana u opadajućem redoslijedu, tako da stavljamo -1. I rezultat je Tokio, pošto je vrijednost stanovništva (13,189) najbliža prosječnoj vrijednosti (000).

VLOOKUP() također može izvesti takve proračune, ali samo kao formula niza: VLOOKUP sa PROSJEKOM, MIN i MAX.

INDEX MATCH i ESND/IFERROR

Vjerovatno ste već primijetili da ako formula ne može pronaći željenu vrijednost, ona ispušta grešku # N / A. Standardnu ​​poruku o grešci možete zamijeniti nečim informativnijim. Na primjer, postavite argument u formulu U XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Sa ovom formulom, ako unesete podatke koji nisu u tabeli, obrazac će vam dati navedenu poruku.

Ako želite uhvatiti sve greške, osim za U XNUMX može biti korišteno IFERROR:

=IFGREŠKA(INDEX(C2:C10,MACH(F1,A2:A10,0)), "Nešto nije u redu!")

Ali zapamtite da maskiranje grešaka na ovaj način nije dobra ideja, jer standardne greške prijavljuju kršenja formule.

Nadamo se da vam je naš vodič za korištenje funkcije INDEX MATCH() bio od pomoći.