Možete koristiti funkciju MATCH programa Excel da biste pronašli relativni položaj određene vrijednosti u rasponu ćelija ili nizu.
Funkcija MATCH slična je funkciji VLOOKUP jer su obje kategorizirane pod Excel funkcije pretraživanja/reference. VLOOKUP traži određenu vrijednost u stupcu i vraća vrijednost u istom retku dok funkcija MATCH traži određenu vrijednost u rasponu i vraća poziciju te vrijednosti.
Excel funkcija MATCH traži navedenu vrijednost u rasponu ćelija ili nizu i vraća relativni položaj prvog pojavljivanja te vrijednosti u rasponu. Funkcija MATCH se također može koristiti za traženje određene vrijednosti i vraćanje odgovarajuće vrijednosti uz pomoć funkcije INDEX (baš kao Vlookup). Pogledajmo kako koristiti Excel funkciju MATCH za pronalaženje položaja tražene vrijednosti u rasponu ćelija.
Excel MATCH funkcija
Funkcija MATCH je ugrađena funkcija u Excelu i prvenstveno se koristi za lociranje relativnog položaja tražene vrijednosti u stupcu ili retku.
Sintaksa funkcije MATCH:
=MATCH(vrijednost_potraži,niz_potraživanja,[vrsta_podudaranja})Gdje:
lookup_value – Vrijednost koju želite potražiti u određenom rasponu ćelija ili u nizu. To može biti numerička vrijednost, tekstualna vrijednost, logička vrijednost ili referenca ćelije koja ima vrijednost.
niz_potraživanja – Nizovi ćelija u kojima tražite vrijednost. Mora biti jedan stupac ili jedan redak.
match_type – To je izborni parametar koji se može postaviti na 0,1 ili -1, a zadana vrijednost je 1.
- 0 traži točno podudaranje, kada nije pronađeno, vraća pogrešku.
- -1 traži najmanju vrijednost koja je veća ili jednaka lookup_value kada je niz pretraživanja u rastućem redoslijedu.
- 1 traži najveću vrijednost koja je manja ili jednaka vrijednosti look_up kada je niz pretraživanja u silaznom redoslijedu.
Pronađite poziciju točnog podudaranja
Pretpostavimo da imamo sljedeći skup podataka u kojem želimo pronaći poziciju određene vrijednosti.
U ovoj tablici želimo pronaći poziciju imena grada (Memphis) u stupcu (A2:A23), pa koristimo ovu formulu:
=MATCH("memphis",A2:A23,0)Treći argument je postavljen na '0' jer želimo pronaći točno podudaranje naziva grada. Kao što vidite da je naziv grada “memphis” u formuli napisan malim slovima, dok je u tablici prvo slovo imena grada velikim (Memphis). Ipak, formula može pronaći poziciju navedene vrijednosti u zadanom rasponu. To je zato što funkcija MATCH ne razlikuje velika i mala slova.
Bilješka: Ako lookup_value nije pronađena u rasponu pretraživanja ili ako navedete pogrešan raspon pretraživanja, funkcija će vratiti pogrešku #N/A.
Možete koristiti referencu ćelije u prvom argumentu funkcije umjesto izravne vrijednosti. Formula u nastavku pronalazi položaj vrijednosti u ćeliji F2 i vraća rezultat u ćeliju F3.
Pronađite poziciju približnog podudaranja
Postoje dva načina na koja možete potražiti približno ili točno podudaranje vrijednosti pretraživanja i vratiti njezinu poziciju.
- Jedan od načina je pronaći najmanju vrijednost koja je veća ili jednaka (sljedeće najveće podudaranje) navedenoj vrijednosti. To se može postići postavljanjem posljednjeg argumenta (match_type) funkcije kao '-1'
- Drugi način je najveća vrijednost koja je manja ili jednaka (sljedeće najmanje podudaranje) danoj vrijednosti. To se može postići postavljanjem match_type funkcije kao '1'
Sljedeća najmanja utakmica
Ako funkcija ne može pronaći točno podudaranje s navedenom vrijednošću kada je vrsta podudaranja postavljena na '1', ona locira najveću vrijednost koja je nešto manja od navedene vrijednosti (što znači sljedeću najmanju vrijednost) i vraća njenu poziciju . Da bi to funkcioniralo, morate sortirati niz uzlaznim redoslijedom, ako ne, to će rezultirati pogreškom.
U primjeru koristimo donju formulu da pronađemo sljedeće najmanje podudaranje:
=MACH(F2,D2:D23,1)Kada ova formula ne može pronaći točno podudaranje za vrijednost u ćeliji F2, ona pokazuje na poziciju (16) sljedeće najmanje vrijednosti, tj. 98.
Sljedeća najveća utakmica
Kada je vrsta podudaranja postavljena na '-1' i funkcija MATCH ne može pronaći točno podudaranje, pronalazi najmanju vrijednost koja je veća od navedene vrijednosti (što znači sljedeću najveću vrijednost) i vraća njezinu poziciju. Niz pretraživanja mora biti sortiran u silaznom redoslijedu za ovu metodu, inače će vratiti pogrešku.
Na primjer, unesite sljedeću formulu da biste pronašli sljedeće najveće podudaranje s vrijednošću pretraživanja:
=MACH(F2,D2:D23,-1)Ova funkcija MATCH traži vrijednost u F2 (55) u rasponu pretraživanja D2:D23, a kada ne može pronaći točno podudaranje, vraća poziciju (16) sljedeće najveće vrijednosti, tj. 58.
Podudaranje zamjenskih znakova
Zamjenski znakovi se mogu koristiti u funkciji MATCH samo kada je match_type postavljen na '0', a vrijednost pretraživanja je tekstualni niz. Postoje zamjenski znakovi koje možete koristiti u funkciji MATCH: zvjezdica (*) i upitnik (?).
- Upitnik (?) koristi se za podudaranje bilo kojeg pojedinačnog znaka ili slova s tekstualnim nizom.
- zvjezdica (*) koristi se za podudaranje bilo kojeg broja znakova sa nizom.
Na primjer, koristili smo dva zamjenska znaka '?' u lookup_value (Lo??n) funkcije MATCH da bismo pronašli vrijednost koja odgovara tekstualnom nizu s bilo koja dva znaka (na mjestima zamjenskih znakova). A funkcija vraća relativni položaj odgovarajuće vrijednosti u ćeliji E5.
=MATCH("Lo??n",A2:A22,0)
Možete koristiti (*) zamjenski znak na isti način kao (?), ali zvjezdica se koristi za podudaranje bilo kojeg broja znakova, dok se upitnik koristi za podudaranje s bilo kojim pojedinačnim znakom.
Na primjer, ako koristite 'sp*', funkcija bi se mogla podudarati sa zvučnikom, brzinom ili spielbergom itd. Ali ako funkcija pronađe višestruke/duplicirane vrijednosti koje odgovaraju vrijednosti pretraživanja, vratit će samo poziciju prve vrijednosti.
U primjeru smo unijeli “Kil*o” u argument lookup_value. Dakle, funkcija MATCH() traži tekst koji sadrži 'Kil' na početku, 'o' na kraju i bilo koji broj znakova između. 'Kil*o' odgovara Kilimandžaru u nizu i stoga funkcija vraća relativni položaj Kilimandžara, koji je 16.
INDEKS i PODRŽAVANJE
Funkcije MATCH rijetko se koriste same. Često se uparuju s drugim funkcijama kako bi stvorili moćne formule. Kada se funkcija MATCH kombinira s funkcijom INDEX, može izvršiti napredna pretraživanja. Mnogi ljudi još uvijek radije koriste VLOOKUP za traženje vrijednosti jer je jednostavniji, ali INDEX MATCH je fleksibilniji i brži od VLOOKUP-a.
VLOOKUP može tražiti vrijednost samo okomito, tj. stupce, dok kombinacija INDEX MATCH može izvršiti i vertikalna i horizontalna pretraživanja.
Funkcija INDEX koja se koristi za dohvaćanje vrijednosti na određenom mjestu u tablici ili rasponu. Funkcija MATCH vraća relativni položaj vrijednosti u stupcu ili retku. Kada se kombinira, MATCH pronalazi broj retka ili stupca (lokaciju) određene vrijednosti, a funkcija INDEX dohvaća vrijednost na temelju tog broja retka i stupca.
Sintaksa funkcije INDEX:
=INDEX(niz,broj_reda,[broj_stupca],)U svakom slučaju, pogledajmo kako INDEX MATCH radi na primjeru.
U donjem primjeru želimo dohvatiti rezultat 'Quiz2' za učenicu 'Anne'. Za to ćemo koristiti formulu u nastavku:
=INDEX(B2:F20,PODRŽAVA(H2,A2:A20,0),3)INDEX treba broj retka i stupca da dohvati vrijednost. U gornjoj formuli, ugniježđena funkcija MATCH pronalazi broj retka (poziciju) vrijednosti 'Anne' (H2). Zatim dajemo taj broj retka funkciji INDEX s rasponom B2:F20 i brojem stupca (3), koji specificiramo. A funkcija INDEX vraća rezultat '91'.
Dvosmjerno traženje s INDEX i MATCH
Također možete koristiti funkcije INDEX i MATCH za traženje vrijednosti u dvodimenzionalnom rasponu (dvosmjerno traženje). U gornjem primjeru koristili smo funkciju MATCH za lociranje broja retka vrijednosti, ali smo broj stupca unijeli ručno. Ali možemo pronaći i redak i stupac ugniježđenjem dvije funkcije MATCH, jednu u argumentu row_num, a drugu u argumentu stupac_num funkcije INDEX.
Koristite ovu formulu za dvosmjerno traženje s INDEX i MATCH:
=INDEX(A1:F20,PODRŽI(H2,A2:A20,0),PODRŽI(H3,A1:F1,0))Kao što znamo, funkcija MATCH može tražiti vrijednost i vodoravno i okomito. U ovoj formuli, druga funkcija MATCH u argumentu broj_stupca pronalazi poziciju Quiz2 (4) i dostavlja je funkciji INDEX. A INDEX dohvaća rezultat.
Sada znate kako koristiti funkciju Match u Excelu.