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

Prethodno smo početnicima objasnili kako koristiti osnovne funkcije VLOOKUP-a (engleski VLOOKUP, skraćenica za “vertical lookup function”). A iskusnim korisnicima prikazano je nekoliko kompliciranijih formula.

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

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

Osnove INDEX MATCH

Budući da je svrha ovog vodiča pokazati koliko je ova značajka dobra, mi Pogledajmo osnovne informacije o načelima njegovog rada. I pokazat ćemo primjere, a također ćemo razmotriti zašto, bolji je od VLOOKUP-a ().

Sintaksa i upotreba funkcije INDEX

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

=INDEX(niz, broj retka, broj stupca):

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

Primjer jednostavne formule:

=INDEX(A1:S10,2,3;XNUMX;XNUMX)

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

Prilično jednostavno, zar ne? Ali kada radite sa stvarnim dokumentima, malo je vjerojatno da ćete imati informacije o brojevima stupaca ili ćelija. 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, vrsta podudaranja)

• tražena vrijednost – broj ili tekst koji treba pronaći;
• pretraživani niz – područje gdje će se vršiti pretraga;
• vrsta podudaranja – određuje hoće li se tražiti toč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 navedene vrijednosti;
  • 0 – pokazuje točno podudaranje s traženom vrijednošću. U kombinaciji INDEX() MATCH() gotovo uvijek će vam trebati točno podudaranje, pa pišemo 0;
  • -1 – Prikazuje najmanju vrijednost koja je veća ili jednaka vrijednosti navedenoj u formuli. Sortiranje se provodi silaznim redoslijedom.

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

=EXPOSE(London,B1:B3,0)

Kako raditi s funkcijom INDEX MATCH 

Vjerojatno ste već počeli shvaćati princip po kojem je izgrađen zajednički rad ovih funkcija. Ukratko, dakle INDEX() traži željenu vrijednost među navedenim redcima i stupcima. A MATCH() pokazuje brojeve ovih vrijednosti:

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

Još uvijek vam je teško razumjeti kako to funkcionira? Možda će primjer bolje objasniti. Pretpostavimo da imate popis svjetskih prijestolnica i njihovog stanovništva:

Kako bismo saznali broj stanovnika određenog glavnog grada, primjerice glavnog grada Japana, koristimo se sljedećom formulom:

=INDEX(C2:C10, MATCH(Japan, A2:A10,0;XNUMX))

Objašnjenje:

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

Tako gornja formula postaje standardna formula INDEKS(C2:C10,3;XNUMX). Formula pretražuje od ćelija C2 do C10 i vraća podatke iz treće ćelije u tom rasponu, to jest C4, jer odbrojavanje počinje od drugog retka.

Ne želite u formuli propisati ime grada? Zatim ga zapišite u bilo koju ćeliju, recimo F1, i upotrijebite ga kao referencu u formuli MATCH(). I na kraju dobijete formulu dinamičkog pretraživanja:

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

Važno! Broj redaka u poredak INDEX() mora biti isti kao broj redaka u razmatrani niz u MATCH(), inače ćete dobiti pogrešan rezultat.

Čekaj malo, zašto jednostavno ne upotrijebiš formulu VLOOKUP()?

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

 Kakva je svrha gubljenja vremena 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 da biste razumjeli kako funkcije INDEX() i MATCH() rade zajedno. Ostali primjeri će pokazati što su ove funkcije sposobne u situacijama kada je VLOOKUP nemoćan. 

INDEX MATCH ili VLOOKUP

Prilikom odlučivanja koju formulu pretraživanja koristiti, mnogi se slažu da su INDEX() i MATCH() daleko bolji od VLOOKUP-a. Međutim, mnogi ljudi i dalje koriste VLOOKUP(). Prvo, VLOOKUP() je jednostavniji, a drugo, korisnici ne razumiju u potpunosti sve prednosti rada s INDEX() i MATCH(). Bez tog znanja nitko neće pristati trošiti svoje vrijeme na proučavanje složenog sustava.

Evo ključnih prednosti INDEX() i MATCH() nad 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 lijevim stupcima tablice. Ali INDEX() i MATCH() mogu to 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 stupaca. Formula VLOOKUP() pokazuje netočne rezultate prilikom uklanjanja ili dodavanja stupaca jer VLOOKUP() treba točan broj stupca da bi bio uspješan. Naravno, kada se stupci dodaju ili uklanjaju, mijenjaju se i njihovi brojevi. 

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

1. Nema ograničenja u opsegu pretraživanja. Kada koristite VLOOKUP(), ukupni broj kriterija pretraživanja ne smije premašiti 255 znakova ili ćete dobiti #VALUE! Dakle, ako vaši podaci sadrže veliki broj znakova, INDEX() i MATCH() su najbolja opcija.
2. Visoka brzina obrade. Ako su vaši stolovi relativno mali, malo je vjerojatno da ćete primijetiti bilo kakvu razliku. Ali, ako tablica sadrži stotine ili tisuće redaka i, shodno tome, postoje stotine i tisuće formula, INDEX () i MATCH () će se nositi puno brže od VLOOKUP (). Činjenica je da će Excel obraditi samo stupce navedene u formuli, umjesto obrade cijele tablice. 

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

Primjeri formula 

Već smo shvatili korisnost ovih funkcija, 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 izvesti ovaj oblik pretraživanja. Dakle, ako željene vrijednosti nisu u krajnjem lijevom stupcu, VLOOKUP() neće dati rezultat. Funkcije INDEX() i MATCH() su svestranije, a mjesto vrijednosti ne igra veliku ulogu za njihov rad.

Na primjer, dodati ćemo stupac ranga na lijevu stranu naše tablice i pokušati otkriti koji rang u smislu broja stanovnika zauzima glavni grad naše zemlje.

U ćeliju G1 upisujemo vrijednost koju treba pronaći, a zatim upotrijebimo 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))

Potaknuti. Ako planirate koristiti ovu formulu za više ćelija, svakako popravite raspone pomoću apsolutnog adresiranja (na primjer, $A$2: $A$10 i $C$2: 4C$10).

INDEX EKSPOZIRANIJI EKSPOZIRANIJI  za pretraživanje u stupcima i redovima

U gornjim primjerima koristili smo ove funkcije kao zamjenu za VLOOKUP() za vraćanje vrijednosti iz unaprijed definiranog raspona redaka. Ali što ako trebate napraviti matricu ili dvostrano pretraživanje?

Zvuči komplicirano, ali formula za takve izračune slična je standardnoj formuli INDEX() MATCH(), samo s jednom razlikom: formula MATCH() mora se koristiti dva puta. Prvi put za dobivanje broja retka, a drugi put za dobivanje broja stupca:

=INDEX(niz, MATCH(vrijednost okomitog pretraživanja, stupac pretraživanja, 0), MATCH(vrijednost vodoravnog pretraživanja, redak pretraživanja, 0))

Pogledajmo donju tablicu i pokušajmo napraviti formulu INDEX() EXPRESS() EXPRESS() kako biste prikazali demografske podatke u određenoj zemlji za odabranu godinu.

Ciljana zemlja je u ćeliji G1 (okomito pretraživanje), a ciljna godina je u ćeliji G2 (vodoravno pretraživanje). 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 rastavljanjem na pojedinačne jednadžbe. I tada možete razumjeti što radi svaka pojedinačna funkcija:

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

Pronađeni brojevi redaka i stupaca šalju se odgovarajućoj vrijednosti u formuli INDEX():

=INDEX(B2:D11,2,3;XNUMX;XNUMX)

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

Pretraživanje prema više uvjeta s INDEX i MATCH

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

Ali dobra vijest je ta Pomoću INDEX() i MATCH() možete pretraživati ​​više uvjeta bez potrebe za uređivanjem ili mijenjanjem radnog lista.

Ovdje je opća formula višeuvjetnog pretraživanja za INDEX() MATCH():

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

Napomena: ova se formula mora koristiti zajedno s tipkovničkim prečacem CTRL+SHIFT+ENTER.

Pretpostavimo da trebate pronaći vrijednost koju tražite na temelju 2 uvjeta: 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 usporedbu stanja, F2 – stanje 2, V2:V10 – raspon za usporedbu stanja 2.

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

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

Kako ove formule rade

Ova formula radi na isti način kao standardna formula INDEX() MATCH(). Za traženje više uvjeta, jednostavno stvorite više False i True uvjeta koji predstavljaju točne i netočne pojedinačne uvjete. Zatim se ovi uvjeti primjenjuju na sve odgovarajuće elemente niza. Formula pretvara argumente False i True u 0 odnosno 1 i daje niz gdje je 1 podudarne 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 retku iz željenog stupca.

Formula bez niza ovisi o sposobnosti INDEX() da ih samostalno obradi. Drugi INDEX() u formuli odgovara false (0), tako da prosljeđuje cijeli niz s tim vrijednostima formuli MATCH(). 

Ovo je prilično opširno objašnjenje logike koja stoji iza ove formule. Za više informacija pročitajte članak “INDEX MATCH s više uvjeta".

AVERAGE, MAX i MIN u INDEX 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 s INDEX i MATCH.

INDEX MATCH i MAX

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

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

INDEX MATCH i MIN

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

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

INDEX PRETRAŽIVANJA i ZMIJA

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

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

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

• ako su stupci poredani uzlaznim redoslijedom, postavite 1 (tada će formula izračunati maksimalnu vrijednost, koja je manja ili jednaka prosječnoj vrijednosti);
• ako je sortiranje padajuće, tada -1 (formula će ispisati minimalnu vrijednost koja je veća ili jednaka prosjeku);
• ako polje pretraživanja sadrži vrijednost koja je točno jednaka prosjeku, postavite je na 0. 

 U našem primjeru, populacija je poredana silaznim redoslijedom, pa smo stavili -1. I rezultat je Tokio, budući da je vrijednost broja stanovnika (13,189) najbliža prosječnoj vrijednosti (000).

VLOOKUP() također može izvoditi takve izračune, ali samo kao formula polja: VLOOKUP s AVERAGE, MIN i MAX.

INDEX MATCH i ESND/IFERROR

Vjerojatno ste već primijetili da ako formula ne može pronaći željenu vrijednost, javlja se pogreška # N / A. Standardnu ​​poruku o pogrešci možete zamijeniti nečim informativnijim. Na primjer, postavite argument u formuli U XNUMX. god:

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

Ovom formulom, ako unesete podatke koji nisu u tablici, obrazac će vam dati navedenu poruku.

Ako želite uhvatiti sve pogreške, osim za U XNUMX. god može se koristiti AKO POGREŠKA:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Nešto nije u redu!”)

No zapamtite da maskiranje pogrešaka na ovaj način nije dobra ideja, jer standardne pogreške prijavljuju kršenja u formuli.

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