Kako koristiti funkcije INDEX i MATCH u Excelu

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

Što treba znati

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

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

Što su funkcije INDEX i MATCH?

INDEX i MATCH su Excel funkcije pretraživanja. Iako su to dvije potpuno odvojene funkcije koje se mogu koristiti same za sebe, mogu se i kombinirati 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 retku skupa podataka ili u petom retku i trećem stupcu.

Iako bi se INDEX mogao koristiti sam, ugniježđenje MATCH-a u formulu čini ga malo korisnijim. Funkcija MATCH traži određenu stavku u rasponu ćelija, a zatim vraća relativni položaj stavke u rasponu. Na primjer, može se koristiti za određivanje da je određeno ime treća stavka na popisu imena.

INDEX i MATCH Sintaksa i argumenti

Ovako treba napisati obje funkcije da bi ih Excel razumio:

=INDEX(niz, row_num, [column_num])

array je raspon ćelija koje će formula koristiti. To može biti jedan ili više redaka i stupaca, kao što je A1:D5. Obavezno je.
row_num je redak u nizu iz kojeg se vraća vrijednost, kao što je 2 ili 18. Potreban je osim ako je prisutan column_num.
column_num je stupac u nizu iz kojeg se vraća vrijednost, kao što je 1 ili 9. Nije obavezan.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value je vrijednost koju želite podudarati u lookup_array. To može biti broj, tekst ili logička vrijednost koja se upisuje ručno ili se na nju upućuje putem reference ćelije. Ovo je obavezno.
lookup_array je raspon ćelija koje treba pregledati. To može biti jedan redak ili jedan stupac, 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 s vrijednostima u lookup_array (pogledajte dolje). 1 je zadana vrijednost ako je ovaj argument izostavljen.

Koju vrstu podudaranja koristiti
Vrsta podudaranja	Što radi	Pravilo	Primjer
1	Pronalazi najveću vrijednost koja je manja ili jednaka lookup_value.	Vrijednosti lookup_array 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, pa se umjesto toga vraća pozicija sljedećeg najmanjeg broja, poput 22.
0	Pronalazi prvu vrijednost koja je točno jednaka lookup_value.	Vrijednosti lookup_array 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 moraju biti postavljene silaznim redoslijedom (npr. 2, 1, 0, -1, -2).	lookup_value je 25, ali nedostaje u lookup_array, pa se umjesto toga vraća pozicija sljedećeg najvećeg broja, poput 34.

Koristite 1 ili -1 za slučajeve kada trebate izvršiti približno traženje duž ljestvice, kao kada radite s brojevima i kada su aproksimacije u redu. Ali zapamtite da će, ako ne navedete match_type, 1 biti zadana vrijednost, što može iskriviti rezultate ako stvarno želite točno podudaranje.

Primjer formula INDEX i MATCH

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

INDEX Primjeri

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

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

U ovom prvom primjeru, postoje četiri INDEX formule koje možemo koristiti za dobivanje različitih vrijednosti:

=INDEX(A1:B2, 2, 2) pregledava A1:B2 kako bi pronašao vrijednost u drugom stupcu i drugom retku, a to je Stacy.
=INDEX(A1:B1, 1) pregledava A1:B1 kako bi pronašao vrijednost u prvom stupcu, a to je Jon.
=INDEX(2:2, 1) pregledava sve u drugom redu kako bi locirao vrijednost u prvom stupcu, a to je Tim.
=INDEX(B1:B2, 1) traži kroz B1:B2 kako bi locirao vrijednost u prvom redu, a to je Amy.

PRIMJERI SPARIVANJA

=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 budući da nije pronađen u tablici, 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) identičan je formuli iznad njega, ali budući da niz nije u silaznom redoslijedu kao što zahtijeva -1, dobivamo pogrešku.
=MATCH(13, A1:D1, 0) traži 13 u prvom retku lista, što vraća 4 jer je to četvrta stavka u ovom nizu.

INDEX-MATCH Primjeri

Evo dva primjera gdje možemo kombinirati INDEX i MATCH u jednu formulu:

Pronađi referencu ćelije u tablici

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

Ovaj primjer ugniježđuje formulu MATCH unutar formule INDEX. Cilj je identificirati boju artikla pomoću broja artikla.

Ako pogledate sliku, možete vidjeti u "Odvojenim" recima kako bi formule bile napisane same za sebe, ali budući da ih ugniježđujemo, događa se ovo:

MATCH(F1, A2:A5) traži F1 vrijednost (8795) u skupu podataka A2:A5. Ako odbrojavamo stupac, možemo vidjeti da je 2, tako da je to ono što je funkcija MATCH upravo otkrila.
Indeksni niz je B2:B5 budući da u konačnici tražimo vrijednost u tom stupcu.
Funkcija INDEX sada se može prepisati ovako jer je 2 ono što je MATCH pronašao: INDEX(B2:B5, 2, [column_num]).
Budući da column_num nije obavezan, možemo ga ukloniti da ostane ovo: INDEX(B2:B5, 2).
Dakle, ovo je kao normalna INDEX formula gdje nalazimo vrijednost druge stavke u B2:B5, koja je crvena.

Traženje prema naslovima redaka i stupaca

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

U ovom primjeru MATCH i INDEX, radimo dvosmjerno pretraživanje. Ideja je vidjeti koliko smo novca zaradili od zelenih predmeta u svibnju. Ovo je stvarno slično gornjem primjeru, ali je dodatna MATCH formula ugniježđena u INDEX.

MATCH(G1, A2:A13, 0) je prva riješena stavka u ovoj formuli. Traži G1 (riječ "May") u A2:A13 da dobije određenu vrijednost. Ovdje ga ne vidimo, ali je 5.
MATCH(G2, B1:E1, 0) je druga MATCH formula, i stvarno je slična prvoj, ali umjesto toga traži G2 (riječ "Green") u naslovima stupaca na B1:E1. Ovaj se rješava na 3.
Sada možemo prepisati formulu INDEX ovako da vizualiziramo što se događa: =INDEX(B2:E13, 5, 3). Ovo traži u cijeloj tablici, B2:E13, za peti red i treći stupac, koji vraća 180 USD.

Pravila SPARANJA i INDEKS

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

MATCH ne razlikuje velika i mala slova, tako da se velika i mala slova tretiraju na isti način kada se podudaraju tekstualne vrijednosti.
MATCH vraća N/A iz više razloga: ako je match_type 0 i lookup_value nije pronađen ako je match_type -1 i lookup_array nije u silaznom redoslijedu, ako je match_type 1 i lookup_array nije u rastućem poredak, a ako lookup_array nije jedan red ili stupac.
Možete koristiti zamjenski znak u argumentu lookup_value ako je match_type 0, a lookup_value tekstualni niz. Upitnik odgovara bilo kojem pojedinačnom znaku, a zvjezdica odgovara bilo kojem nizu znakova (npr.npr. =MATCH("Jo", 1:1, 0)). Da biste koristili MATCH za traženje stvarnog upitnika ili zvjezdice, prvo upišite ~.
INDEX vraća REF! ako row_num i column_num ne pokazuju na ćeliju unutar polja.

Povezane Excel funkcije

Funkcija MATCH slična je funkciji 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-a koji zahtijeva INDEX za napredna pretraživanja, VLOOKUP formule trebaju samo tu jednu funkciju.