Vlookup formula djeluje samo kada se niz tablice u formuli ne mijenja, ali ako je u tablicu umetnut novi stupac ili je stupac izbrisan, formula daje netočan rezultat ili odražava pogrešku, kako bi formula bila bez pogrešaka u takvim dinamičnim situacijama koristimo funkciju podudaranja da bismo stvarno podudarali indeks podataka i vratili stvarni rezultat.
Kombinirajte VLOOKUP s Matchom
Vlookup formula je najčešće korištena funkcija koja se koristi za pretraživanje i vraćanje iste vrijednosti u navedenom indeksu stupca ili vrijednosti iz drugog indeksa stupca s referencom na podudarnu vrijednost iz prvog stupca. Glavni izazov s kojim se suočava tijekom upotrebe vlookupa je taj što je indeks stupca koji se treba navesti statičan i nema dinamičku funkcionalnost. Pogotovo kada radite na više kriterija koji zahtijevaju ručnu promjenu indeksa referentnog stupca. Na taj se način ta potreba ispunjava upotrebom formule “MATCH” kako bi se imao bolji zahvat ili kontrola indeksa stupca koji se često mijenja u formuli VLOOKUP.
VLookup i Formula za podudaranje
# 1 - VLOOKUP Formula
Formula VLOOKUP funkcije u Excelu
Ovdje su svi argumenti koje treba unijeti obvezni.
- Lookup_value - Ovdje treba unijeti referentnu ćeliju ili tekst s dvostrukim navodnicima da bi se identificirali u rasponu stupaca.
- Polje tablice - Ovaj argument zahtijeva unos raspona tablice u kojem treba tražiti vrijednost_traženja i podaci koji se preuzimaju nalaze se u određenom rasponu stupaca.
- Col_index_num - U ovom argumentu treba unijeti indeksni broj stupca ili broj stupca iz referentnog prvog stupca iz kojeg treba povući odgovarajuću vrijednost s istog položaja kao i vrijednost koja se pretražuje u prvom stupcu.
- [Range_lookup] - Ovaj će argument dati dvije mogućnosti.
- TRUE - Približno podudaranje: - Argument se može unijeti kao TRUE ili numerički „1“, što vraća približno podudaranje koje odgovara referentnom stupcu ili prvom stupcu. Štoviše, vrijednosti u prvom stupcu polja tablice moraju se sortirati u rastućem redoslijedu.
- FALSE - Točno podudaranje: - Ovdje argument koji treba unijeti može biti FALSE ili numerički „0“. Ova će opcija vratiti točno podudaranje vrijednosti koja odgovara za identifikaciju iz položaja u rasponu prvog stupca. Ako ne potražite vrijednost iz prvog stupca, vratila bi se poruka o pogrešci "# N / A".
# 2 - Formula za utakmicu
Funkcija Match vraća položaj ćelije vrijednosti unesene za zadani niz tablice.
Svi argumenti unutar sintakse su obvezni.
- Lookup_value - ovdje uneseni argument može biti referenca na ćeliju vrijednosti ili tekstualni niz s dvostrukim navodnicima čiji se položaj ćelije treba povući.
- Lookup_array - potrebno je unijeti raspon polja za tablicu čija se vrijednost ili sadržaj ćelije želi identificirati.
- [vrsta podudaranja] - Ovaj argument pruža tri mogućnosti kako je objašnjeno u nastavku.
- "1-manje od" - ovdje je argument koji treba unijeti numerički "1" koji će vratiti vrijednost koja je manja ili jednaka vrijednosti pretraživanja. A također se niz pretraživanja mora sortirati u rastućem redoslijedu.
- "0-točno podudaranje" - ovdje argument koji treba unijeti trebao bi biti numerički "0". Ova će opcija vratiti točan položaj usklađene vrijednosti pretraživanja. Međutim, pretraživački niz može biti u bilo kojem redoslijedu.
- "-1-veće od" - argument koji treba unijeti trebao bi biti numerički "-1". Treća opcija pronalazi najmanju vrijednost koja je veća ili jednaka vrijednosti pretraživanja. Ovdje se redoslijed za pretraživački niz mora postaviti u opadajućem redoslijedu.
# 3 - VLOOKUP s MATCH Formulom
= VLOOKUP (lookup_value, niz_tablica, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
Kako koristiti VLOOKUP s formulom podudaranja u Excelu?
Primjer u nastavku pomoći će razumjeti funkcioniranje vlookupa i formule za podudaranje prilikom sastavljanja.
Ovaj VLookup s Match Excel Predloškom možete preuzeti ovdje - VLookup s Match Excel PredloškomRazmotrite donju tablicu podataka koja opisuje specifikacije određenog vozila koje se kupuje.
Da bismo dobili jasnoću kombinirane funkcije za vlookup i funkciju podudaranja, shvatimo kako djeluje pojedinačna formula, a zatim dođite do rezultata vlookup podudaranja kada se sastave.
1. korak - Primijenimo formulu vlookup na pojedinačnoj razini da bismo došli do rezultata.
Izlaz je prikazan u nastavku:
Ovdje se vrijednost pretraživanja odnosi na $ B9 koji je model "E", a niz pretraživanja daje se kao raspon tablice podataka s apsolutnom vrijednošću "$", indeks stupca odnosi se na stupac "4", što je broj za stupac "Type" i traženje raspona dobiva točno podudaranje.
Stoga se primjenjuje sljedeća formula za vraćanje vrijednosti za stupac "Gorivo".
Izlaz je prikazan u nastavku:
Ovdje vrijednost pretraživanja s apsolutnim nizom "$" primijenjena za vrijednost pretraživanja i lookup_array pomaže popraviti referentnu ćeliju čak i ako se formula kopira u drugu ćeliju. U stupcu "Gorivo" moramo promijeniti indeks stupca u "5" jer se mijenja vrijednost iz koje su potrebni podaci za preuzimanje.
Korak # 2 - Primijenimo sada formulu Match kako bismo dohvatili položaj za zadanu vrijednost pretraživanja.
Izlaz je prikazan u nastavku:
Kao što se može vidjeti na gornjoj snimci zaslona, ovdje pokušavamo dohvatiti položaj stupca iz polja tablice. U ovom slučaju, broj stupca koji se treba izvući naziva se ćelijom C8 koja je stupac "Vrsta", a raspon pretraživanja koji se traži daje se kao raspon zaglavlja stupaca, a vrsta podudaranja daje točno podudaranje kao " 0 ”.
Tako će donja tablica dati željeni rezultat za položaje stupca "Gorivo".
Sada se ovdje stupac koji se traži daje ćelija D8, a željeni indeks stupca vraća se na "5".
Korak # 3 - Sada će se formula Match koristiti unutar funkcije vlookup za dobivanje vrijednosti s identificiranog položaja stupca.
Izlaz je prikazan u nastavku:
U gornjoj formuli funkcija podudaranja postavlja se umjesto parametra indeksa stupca funkcije vlookup. Ovdje će funkcija podudaranja identificirati referentnu ćeliju tražene vrijednosti "C8" i vratiti broj stupca kroz zadani niz tablice. Ovaj položaj stupca poslužit će u svrhu ulaza u argument indeksa stupca u funkciji vlookup. Što će zauzvrat pomoći vlookupu da identificira vrijednost koju treba vratiti iz rezultantnog broja indeksa stupca?
Slično tome, primijenili smo vlookup s formulom podudaranja i za stupac "Gorivo".
Izlaz je prikazan u nastavku:
Na taj način ovu kombinacijsku funkciju možemo primijeniti i na ostale stupce "Vrsta" i "Gorivo".
Stvari koje treba zapamtiti
- VLOOKUP se može primijeniti na vrijednosti pretraživanja samo na njegovoj prednjoj lijevoj strani. Sve vrijednosti koje se pretražuju s desne strane tablice podataka vratit će vrijednost pogreške "# N / A".
- Raspon table_array upisan u drugom argumentu trebao bi biti apsolutna referenca ćelije "$", to će zadržati fiksni raspon polja tablice kada primjenjuje formulu pretraživanja na druge ćelije, inače će se referentne ćelije za raspon polja tablice pomaknuti na sljedeću ćeliju referenca.
- Vrijednost unesena u vrijednost pretraživanja ne smije biti manja od najmanje vrijednosti u prvom stupcu polja tablice, inače će funkcija vratiti vrijednost pogreške "# N / A".
- Prije primjene približnog podudaranja “TRUE” ili “1” u posljednjem argumentu, uvijek zapamtite da sortirate niz tablice u rastućem redoslijedu.
- Funkcija podudaranja vraća samo položaj vrijednosti u polju tablice vlookup i ne vraća vrijednost.
- U slučaju da funkcija podudaranja ne može identificirati položaj vrijednosti pretraživanja u polju tablice, tada formula vraća vrijednost "# N / A" u vrijednosti pogreške.
- Funkcije pretraživanja i podudaranja ne razlikuju velika i mala slova kada se vrijednost podudaranja podudara s odgovarajućom vrijednošću teksta u polju tablice.