Traženje podataka u programu Excel 2007 uz HLOOKUP i VLOOKUP

Najpopularnije funkcije pretraživanja u programu Excel 2007 su HLOOKUP (za horizontalno traženje) i VLOOKUP (za okomito traženje). Te se funkcije nalaze u kategoriji Traži i reference na kartici Formule na vrpci iu dijaloškom okviru Umetanje funkcije. Oni su dio moćne grupe funkcija koje mogu vratiti vrijednosti tražeći ih u podatkovnim tablicama.

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

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.

Funkcija HLOOKUP koristi sljedeću sintaksu:

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

Funkcija VLOOKUP slijedi gotovo identičnu sintaksu:

=VLOOKUP(vrijednost_pretraživanja,niz_tablice,broj_indeksa_stupca,[traženje_raspona])

U obje funkcije, argument lookup_value je vrijednost koju želite potražiti u tablici, table_array je raspon ćelija ili naziv tablice koji sadrži i vrijednost za traženje i srodnu vrijednost za vraćanje. Row_index_num argument u funkciji HLOOKUP je broj retka čiju vrijednost za povratak; col_index_num argument u funkciji VLOOKUP je broj stupca čija vrijednost se želite vratiti.

Opcijski 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 table_array . Kada navedete TRUE ili izostavite argument range_lookup , 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). 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.

Slika ispod prikazuje primjer korištenja funkcije VLOOKUP za vraćanje napojnice od 15% ili 20% iz tablice napojnica, 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.

Korištenje funkcije VLOOKUP za vraćanje iznosa napojnice za dodavanje iz Lookup tablice.

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 zatim pomiče vrijednosti u prvom stupcu Tip_Table prema dolje dok ne pronađe podudaranje. 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 opcija raspon_pretraživanja argument izostavljen je iz funkcije).

Primijetite da argument col_index_number koristi IF izraz za određivanje vrijednosti stupca koji treba vratiti. U ovom slučaju, ako je vrijednost Tip_Percentage 0,15, tada funkcija vraća vrijednost u drugom stupcu tablice u retku koji je identificiran pomoću lookup_value. Inače, vraća vrijednost u trećem stupcu istog retka.