Kako koristiti funkcije INDEX i MATCH u Excelu

2024 Autor: Abigail Brown | [email protected]. Zadnja izmjena: 2023-12-17 06:41

Šta treba znati

Funkcija INDEX se može koristiti sama, ali ugniježđenje funkcije MATCH unutar nje stvara napredno traženje.
Ova ugniježđena funkcija je fleksibilnija od VLOOKUP-a i može dati rezultate brže.

Ovaj članak objašnjava kako zajedno koristiti funkcije INDEX i MATCH u svim verzijama Excela, uključujući Excel 2019 i Microsoft 365.

Što su funkcije INDEX i MATCH?

INDEX i MATCH su Excel funkcije traženja. Iako su to dvije potpuno odvojene funkcije koje se mogu koristiti same, mogu se kombinirati i za stvaranje naprednih formula.

Funkcija INDEX vraća vrijednost ili referencu na vrijednost unutar određenog odabira. Na primjer, može se koristiti za pronalaženje vrijednosti u drugom redu skupa podataka, ili u petom redu i trećoj koloni.

Dok bi se INDEX mogao koristiti samostalno, ugniježđenje MATCH u formulu ga čini malo korisnijim. Funkcija MATCH traži navedenu stavku u rasponu ćelija, a zatim vraća relativnu poziciju stavke u rasponu. Na primjer, može se koristiti za određivanje da je određeno ime treća stavka na listi imena.

INDEX i MATCH sintaksa i argumenti

Ovako je potrebno napisati obje funkcije da bi ih Excel razumio:

=INDEX(niz, broj_reda, [broj_kolone])

array je opseg ćelija koje će formula koristiti. To može biti jedan ili više redova i stupaca, kao što je A1:D5. Potrebno je.
row_num je red u nizu iz kojeg se vraća vrijednost, kao što je 2 ili 18. Potrebno je osim ako nije prisutan column_num.
column_num je stupac u nizu iz kojeg se vraća vrijednost, kao što je 1 ili 9. Nije obavezno.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value je vrijednost koju želite upariti u lookup_array. To može biti broj, tekst ili logička vrijednost koja se upisuje ručno ili na koju se poziva putem reference ćelije. Ovo je obavezno.
lookup_array je raspon ćelija kroz koje treba gledati. Može biti jedan red ili jedna kolona, kao što je A2:D2 ili G1:G45. Ovo je obavezno.
match_type može biti -1, 0 ili 1. Određuje kako se lookup_value podudara sa vrijednostima u lookup_array (pogledajte dolje). 1 je zadana vrijednost ako je ovaj argument izostavljen.

Koju vrstu podudaranja koristiti
Tip podudaranja	Šta radi	Pravilo	Primjer
1	Pronalazi najveću vrijednost koja je manja ili jednaka lookup_value.	Vrijednosti lookup_array-a moraju biti postavljene uzlaznim redoslijedom (npr. -2, -1, 0, 1, 2; ili A-Z;, ili FALSE, TRUE.	lookup_value je 25, ali nedostaje u lookup_array, tako da se umjesto toga vraća pozicija sljedećeg najmanjeg broja, poput 22.
0	Pronalazi prvu vrijednost koja je tačno jednaka lookup_value.	Vrijednosti lookup_array-a mogu biti bilo kojim redoslijedom.	lookup_value je 25, tako da vraća poziciju 25.
-1	Pronalazi najmanju vrijednost koja je veća ili jednaka lookup_value.	Vrijednosti lookup_array-a moraju biti postavljene u opadajućem redoslijedu (npr. 2, 1, 0, -1, -2).	lookup_value je 25, ali nedostaje u lookup_array, tako da se umjesto toga vraća pozicija sljedećeg najvećeg broja, poput 34.

Koristite 1 ili -1 za vrijeme kada trebate izvršiti približno traženje duž skale, na primjer kada radite s brojevima i kada su aproksimacije u redu. Ali zapamtite da ako ne navedete match_type, 1 će biti zadana, što može iskriviti rezultate ako zaista želite tačno podudaranje.

Primjer INDEX i MATCH formula

Prije nego pogledamo kako kombinirati INDEX i MATCH u jednu formulu, moramo razumjeti kako ove funkcije rade same.

INDEX Primjeri

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

U ovom prvom primjeru, postoje četiri INDEX formule koje možemo koristiti da dobijemo različite vrijednosti:

=INDEX(A1:B2, 2, 2) pregledava A1:B2 da pronađe vrijednost u drugom stupcu i drugom redu, a to je Stacy.
=INDEX(A1:B1, 1) pregledava A1:B1 da pronađe vrijednost u prvoj koloni, a to je Jon.
=INDEX(2:2, 1) pregledava sve u drugom redu kako bi locirao vrijednost u prvoj koloni, a to je Tim.
=INDEX(B1:B2, 1) gleda kroz B1:B2 kako bi locirao vrijednost u prvom redu, a to je Amy.

Primjeri MATCH

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Evo četiri jednostavna primjera funkcije MATCH:

=MATCH("Stacy", A2:D2, 0) traži Stacy u rasponu A2:D2 i vraća 3 kao rezultat.
=MATCH(14, D1:D2) traži 14 u rasponu D1:D2, ali pošto nije pronađeno u tabeli, MATCH pronalazi sljedeću najveću vrijednost to je manje ili jednako 14, što je u ovom slučaju 13, što je na poziciji 1 lookup_array.
=MATCH(14, D1:D2, -1) je identična formuli iznad, ali pošto niz nije u opadajućem redoslijedu kao što -1 zahtijeva, dobijamo grešku.
=MATCH(13, A1:D1, 0) traži 13 u prvom redu lista, što vraća 4 pošto je to četvrta stavka u ovom nizu.

Index-MATCH Primjeri

Evo dva primjera gdje možemo kombinirati INDEX i MATCH u jednoj formuli:

Pronađi referencu ćelije u tabeli

=INDEX(B2:B5, MATCH(F1, A2:A5))

Ovaj primjer je ugniježđenje formule MATCH unutar formule INDEX. Cilj je identificirati boju artikla koristeći broj artikla.

Ako pogledate sliku, možete vidjeti u "Razdvojenim" redovima kako bi formule bile napisane same, ali pošto ih ugnijezdimo, događa se ovo:

MATCH(F1, A2:A5) traži vrijednost F1 (8795) u skupu podataka A2:A5. Ako odbrojimo kolonu, možemo vidjeti da je 2, tako da je to ono što je funkcija MATCH upravo shvatila.
INDEX niz je B2:B5 pošto konačno tražimo vrijednost u toj koloni.
Funkcija INDEX bi se sada mogla prepisati ovako jer 2 je ono što je MATCH pronašao: INDEX(B2:B5, 2, [broj_stupca]).
Pošto je broj_stupca opciono, možemo to ukloniti da ostane sa ovim: INDEX(B2:B5, 2).
Dakle, ovo je kao normalna INDEX formula u kojoj nalazimo vrijednost druge stavke u B2:B5, koja je crvena.

Traži po naslovima redova i kolona

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

U ovom primjeru MATCH i INDEX, radimo dvosmjerno traženje. Ideja je da vidimo koliko smo novca zaradili od zelenih artikala u maju. Ovo je zaista slično gornjem primjeru, ali dodatna MATCH formula je ugniježđena u INDEX.

MATCH(G1, A2:A13, 0) je prva stavka riješena u ovoj formuli. Traži G1 (riječ "maj") u A2:A13 da dobije određenu vrijednost. Ne vidimo to ovdje, ali je 5.
MATCH(G2, B1:E1, 0) je druga MATCH formula, i zaista je slična prvoj, ali umjesto toga traži G2 (riječ "zeleno") u naslovima kolona na B1:E1. Ovaj se rješava na 3.
Sada možemo prepisati INDEX formulu ovako da vizualizujemo šta se dešava: =INDEX(B2:E13, 5, 3). Ovo traži u cijeloj tabeli, B2:E13, peti red i treću kolonu, što vraća $180.

Pravila MATCH i INDEX

Postoji nekoliko stvari koje treba imati na umu kada pišete formule sa ovim funkcijama:

MATCH ne razlikuje velika i mala slova, tako da se velika i mala slova tretiraju isto kada se podudaraju tekstualne vrijednosti.
MATCH vraća N/A iz više razloga: ako je match_type 0 i lookup_value nije pronađena ako je match_type -1 i lookup_array nije u opadajućem redoslijedu, ako je match_type 1 i lookup_array nije u rastućem poredak, i ako lookup_array nije jedan red ili kolona.
Možete koristiti zamjenski znak u argumentu lookup_value ako je match_type 0 i lookup_value je tekstualni niz. Znak pitanja odgovara bilo kojem pojedinačnom znaku, a zvjezdica odgovara bilo kojem nizu znakova (npr.g., =MATCH("Jo", 1:1, 0)). Da biste koristili MATCH da pronađete stvarni upitnik ili zvjezdicu, prvo upišite ~.
INDEX vraća REF! ako row_num i column_num ne pokazuju na ćeliju unutar niza.

Srodne Excel funkcije

Funkcija MATCH je slična LOOKUP, ali MATCH vraća poziciju stavke umjesto same stavke.

VLOOKUP je još jedna funkcija pretraživanja koju možete koristiti u Excelu, ali za razliku od MATCH koja zahtijeva INDEX za napredno traženje, VLOOKUP formule trebaju samo tu jednu funkciju.