Potražite jednu vrijednost pomoću VLOOKUP-a i HLOOKUP-a u Excelu

Najpopularnije funkcije pretraživanja u programu Excel 2013 su funkcije HLOOKUP (za horizontalno traženje) i VLOOKUP (za okomito traženje). Te se funkcije nalaze na padajućem izborniku Traži i referenca na kartici Formule na vrpci kao iu kategoriji Traži i referenca u dijaloškom okviru Umetanje funkcije.

Oni su dio moćne grupe funkcija koje mogu vratiti vrijednosti tražeći ih u podatkovnim tablicama.

Funkcija VLOOKUP pretražuje okomito (od vrha do dna) krajnji lijevi stupac tablice pretraživanja dok program ne pronađe vrijednost koja odgovara ili premašuje onu koju tražite. Funkcija HLOOKUP pretražuje vodoravno (s lijeva na desno) najviši redak tablice pretraživanja dok ne pronađe vrijednost koja odgovara ili premašuje onu koju tražite.

Funkcija VLOOKUP koristi sljedeću sintaksu:

VLOOKUP(vrijednost_pretraživanja,niz_tablice,broj_indeksa_stupca,[pretraživanje_raspona])

Funkcija HLOOKUP slijedi gotovo identičnu sintaksu:

HLOOKUP(vrijednost_pretraživanja,niz_tablice,broj_indeksa_reda,[traženje_raspona])

U obje funkcije, argument lookup_value je vrijednost koju želite potražiti u tablici pretraživanja, a table_array je raspon ćelija ili naziv tablice pretraživanja koja sadrži i vrijednost za traženje i srodnu vrijednost za vraćanje.

Col_index_num argument u funkciji VLOOKUP je broj stupca čije su vrijednosti u odnosu na tražena_vrijednost argument u vertikalnoj tablici. Row_index_num argument u funkciji HLOOKUP je broj reda čije su vrijednosti u odnosu na tražena_vrijednost u horizontalnom stolu.

Prilikom ulaska u col_index_num ili row_index_num argumente u VLOOKUP i HLOOKUP funkcija, morate unijeti vrijednost veću od nule koji ne prelazi ukupan broj stupaca ili redaka u tablici Lookup.

Neobavezni argument range_lookup u funkcijama VLOOKUP i HLOOKUP logički je TRUE ili FALSE koji određuje želite li da Excel pronađe točno ili približno podudaranje za lookup_value u polju table_array.

Kada navedete TRUE ili izostavite argument range_lookup u funkciji VLOOKUP ili HLOOKUP, Excel pronalazi približno podudaranje. Kada navedete FALSE kao argument range_lookup , Excel pronalazi samo točna podudaranja.

Pronalaženje približnih podudaranja odnosi se samo kada tražite numeričke unose (a ne tekst) u prvom stupcu ili retku vertikalne ili horizontalne tablice pretraživanja. Kada Excel ne točno pronalazi u ovom Traži stupcu ili retku, ona smješta sljedeću najvišu vrijednost koja ne prelazi tražena_vrijednost argument, a zatim vraća vrijednost u stupcu ili retku koju odredi col_index_num ili row_index_num argumenata.

Kada koristite funkcije VLOOKUP i HLOOKUP, tekst ili brojčani unosi u stupcu ili retku za traženje (tj. krajnji lijevi stupac vertikalne tablice pretraživanja ili gornji redak horizontalne tablice pretraživanja) moraju biti jedinstveni. Ovi unosi također moraju biti raspoređeni ili sortirani uzlaznim redoslijedom; odnosno abecednim redoslijedom za tekstualne unose i redoslijedom od najnižeg do najvišeg za numeričke unose.

Slika prikazuje primjer korištenja funkcije VLOOKUP za vraćanje napojnice od 15% ili 20% iz tablice napojnice, ovisno o ukupnom iznosu čeka prije oporezivanja. Ćelija F3 sadrži funkciju VLOOKUP:

=VLOOKUP(Ukupno_predporeza,Tablica_napojnice,IF(Postotak_napojnice=0,15,2,3))

Ova formula vraća iznos napojnice na temelju postotka napojnice u ćeliji F1 i iznosa prije oporezivanja čeka u ćeliji F2.

Da biste koristili ovu tablicu napojnice, unesite postotak napojnice (15% ili 20%) u ćeliju F1 (pod nazivom Tip_Percentage) i iznos čeka prije poreza u ćeliju F2 (nazvan Prettax_Total). Excel zatim traži vrijednost koju unesete u ćeliju Prettax_Total u prvom stupcu tablice za traženje, koja uključuje raspon ćelija A2:C101 i zove se Tip_Table.

Excel onda kreće niz vrijednosti u prvom stupcu Tip_Table dok se ne pronađe utakmicu, nakon čega se program koristi col_index_num argument u funkciji VLOOKUP odrediti koji savjet iznos iz tog reda stola da se vrate u ćelije F3.

Ako Excel utvrdi da vrijednost unesena u ćeliju Prettax_Total (16,50 USD u ovom primjeru) ne odgovara točno jednoj od vrijednosti u prvom stupcu Tip_Table, program nastavlja pretraživati ​​niz usporedbeni raspon sve dok ne naiđe na prvu vrijednost koja prelazi ukupan iznos prije oporezivanja (17,00 u ćeliji A19 u ovom primjeru).

Excel zatim seli natrag do prethodnog reda u tablici i vraća vrijednost u stupcu koji odgovara col_index_num argument funkcije VLOOKUP. (To je zato što je izborni argument range_lookup izostavljen iz funkcije.)

Imajte na umu da primjer tablice savjeta koristi funkciju IF za određivanje argumenta col_index_num za funkciju VLOOKUP u ćeliji F3.

Funkcija IF određuje broj stupca koji će se koristiti u tablici savjeta uparujući postotak unesen u Tip_Percentage (ćelija F1) s 0,15. Ako se poklapaju, funkcijske vraća 2 što je col_index_num argumenta i funkcije VLOOKUP vraća vrijednost od drugog stupca (15% kolona B) u rasponu Tip_Table.

Inače, IF funkcija vraća 3 kao col_index_num argument i VLOOKUP funkcija vraća vrijednost iz trećeg stupca (20% stupac C) u Tip_Table rasponu.

Sljedeća slika prikazuje primjer koji koristi funkciju HLOOKUP za traženje cijene svake pekarske stavke pohranjene u zasebnoj tablici za traženje cijena, a zatim za vraćanje te cijene u stupac Cijena/doz na popisu dnevne prodaje. Ćelija F3 sadrži izvornu formulu s funkcijom HLOOKUP koja se zatim kopira u stupac F:

=HLOOKUP(stavka,tablica_cijena,2,FALSE)

U ovoj funkciji HLOOKUP, naziv raspona Stavka koji se daje stupcu Stavka u rasponu C3:C62 definiran je kao argument lookup_value, a naziv raspona ćelija Tablica cijena koji je dan rasponu ćelija I1:M2 je argument table_array .

Row_index_num argument je 2 jer želite Excel vratiti cijene u drugom redu cijena pregledna tablica, a dodatni range_lookup argument je FALSE, jer je naziv stavke na popisu dnevne prodaje mora točno odgovarati stavku ime u cijenu pregledna tablica .

Ako funkcija HLOOKUP koristi raspon tablice cijena za unos cijene po tucetu za svaku stavku pekarskih proizvoda na popisu dnevne prodaje, ažuriranje bilo koje prodaje na popisu čini vrlo jednostavnim.

Sve što trebate učiniti je promijeniti njegovu cijenu/doz trošak u ovom rasponu, a funkcija HLOOKUP odmah ažurira novu cijenu na popisu dnevne prodaje gdje god se artikal proda.