Najbolj priljubljene funkcije iskanja v programu Excel 2016 so funkciji HLOOKUP (za vodoravno iskanje) in VLOOKUP (za vertikalno iskanje). Te funkcije se nahajajo v spustnem meniju Iskanje in sklic na zavihku Formule na traku ter v kategoriji Iskanje in sklic v pogovornem oknu Vstavi funkcijo. So del močne skupine funkcij, ki lahko vrnejo vrednosti tako, da jih poiščejo v podatkovnih tabelah.
Funkcija VLOOKUP išče navpično (od zgoraj navzdol) po skrajnem levem stolpcu iskalne tabele, dokler program ne najde vrednosti, ki se ujema ali presega tisto, ki jo iščete navzgor. Funkcija HLOOKUP išče vodoravno (od leve proti desni) najvišjo vrstico iskalne tabele, dokler ne najde vrednosti, ki se ujema ali presega tisto, ki jo iščete.
Funkcija VLOOKUP uporablja naslednjo sintakso:
VLOOKUP(vrednost_iskanja,matrika_tabel,številka_indeks_stolpca,[iskanje_obsega])
Funkcija HLOOKUP sledi skoraj identični sintaksi:
HLOOKUP(iskana_vrednost,matrika_tabel,številka_indeks_vrstice,[iskanje_obsega])
V obeh funkcijah je argument lookup_value vrednost, ki jo želite poiskati v iskalni tabeli, table_array pa obseg celic ali ime iskalne tabele, ki vsebuje tako vrednost, ki jo želite poiskati, kot povezano vrednost, ki jo je treba vrniti.
Col_index_num trditev označuje stolpec iskalno tabelo, ki vsebuje vrednosti, ki jih vrne funkcija VLOOKUP, ki temelji na ujemanje vrednost lookup_value argument proti tistim, v argumentu table_array. Določite argument col_index_num , ki šteje, koliko stolpcev je ta stolpec na desni strani od prvega stolpca navpične iskalne tabele, in v to število vključite prvi stolpec iskalne tabele.
Row_index_num trditev označuje vrstica, ki vsebuje vrednosti so se vrnili s funkcijo HLOOKUP v horizontalno ploščo. Argument row_index_num določite tako, da preštejete, koliko vrstic navzdol je v tej vrstici od zgornje vrstice vodoravne iskalne tabele. Ponovno v to štetje vključite zgornjo vrstico iskalne tabele.
Pri vnosu col_index_num ali row_index_num argumente funkcij VLOOKUP in HLOOKUP, vrednost, ki jo entercannot presega skupno število stolpcev ali vrstic v tabeli Iskanje.
Opcijski obseg_iskanja argument tako v VLOOKUP in funkcij HLOOKUP je logično pravilna ali napačna, ki določa, ali želite, Excel, da bi našli natančno ali približno tekmo za lookup_value v table_array. Ko določite TRUE ali opusti obseg_iskanja argument v funkciji VLOOKUP ali HLOOKUP, Excel najde približno tekmo. Ko kot argument range_lookup podate FALSE , Excel najde samo natančna ujemanja.
Iskanje približnih ujemanj se nanaša le, če iščete številske vnose (namesto besedila) v prvem stolpcu ali vrstici navpične ali vodoravne iskalne tabele. Ko Excel ne najde natančno ujemanje v tej Iskanje stolpec ali vrstico, da najde naslednjo najvišjo vrednost, ki ne presega lookup_value navedbe in nato vrne vrednost v stolpcu ali vrstici, ki jih imenuje col_index_num ali row_index_num argumentov.
Pri uporabi funkcij VLOOKUP in HLOOKUP morajo biti besedilni ali številčni vnosi v stolpcu ali vrstici Iskanje (to je skrajni levi stolpec navpične iskalne tabele ali zgornja vrstica vodoravne iskalne tabele) edinstveni. Te vnose je treba tudi razvrstiti ali razvrstiti v naraščajočem vrstnem redu; to je abecedni vrstni red za vnose besedila in vrstni red od najnižje do najvišje za številske vnose.
Na sliki je prikazan primer uporabe funkcije VLOOKUP za vrnitev 15- ali 20-odstotne napitnine iz tabele napitnine, odvisno od vsote čeka pred obdavčitvijo. Celica F3 vsebuje funkcijo VLOOKUP:
=VLOOKUP(Predavek_skupaj,Tip_Tabela,IF(Odstotek_napitnine=0,15,2,3))
Ta formula vrne znesek napitnine na podlagi odstotka napitnine v celici F1 in zneska pred obdavčitvijo čeka v celici F2.
Uporaba funkcije VLOOKUP za vrnitev zneska napitnine za dodajanje iz tabele za iskanje.
Če želite uporabiti to tabelo napitnine, vnesite odstotek napitnine (15 % ali 20 %) v celico F1 (imenovano Tip_Percentage) in znesek čeka pred davkom v celico F2 (imenovano Pretax_Total). Excel nato poišče vrednost, ki jo vnesete v celico Predax_Total v prvem stolpcu iskalne tabele, ki vključuje obseg celic A2:C101 in se imenuje Tip_Table.
Excel nato premakne vrednosti v prvem stolpcu Tab_Table navzdol, dokler ne najde ujemanja, pri čemer program uporabi argument col_index_num v funkciji VLOOKUP, da določi, kateri znesek napitnine iz te vrstice tabele naj vrne v celico F3. Če Excel ugotovi, da se vrednost, vnesena v celico Pretax_Total (16,50 USD v tem primeru), ne ujema natančno z eno od vrednosti v prvem stolpcu Tab_Table, program nadaljuje z iskanjem po obsegu primerjave, dokler ne naleti na prvo vrednost, ki presega vsota pred obdavčitvijo (17,00 v celici A19 v tem primeru). Excel nato premakne nazaj do prejšnje vrstice v tabeli in vrne vrednost v stolpcu, ki se ujema z col_index_num argument funkcije VLOOKUP. (To je zato, ker izbirna možnost range_lookupargument je bil izpuščen iz funkcije.)
Upoštevajte, da primer tabele namigov na sliki uporablja funkcijo IF za določitev argumenta col_index_num za funkcijo VLOOKUP v celici F3. Funkcija IF določi številko stolpca, ki se uporablja v tabeli z nasveti, tako da se ujema odstotek, vneseni v Tip_Percentage (celica F1), z 0,15. Če se ujemata, funkcija vrne 2 kot col_index_num argumenta in funkcijo VLOOKUP vrne vrednost iz drugega stebra (15% stolpec B) v Tip_Table območju. Sicer IF vrne 3 kot col_index_num argument in vrne VLOOKUP vrednost iz tretjega stebra (20% stolpec C) v Tip_Table območju.
Naslednja slika prikazuje primer, ki uporablja funkcijo HLOOKUP za iskanje cene vsakega pekovskega izdelka, shranjenega v ločeni tabeli za iskanje cen, in nato za vrnitev te cene v stolpec Cena/Doz na seznamu dnevne prodaje. Celica F3 vsebuje izvirno formulo s funkcijo HLOOKUP, ki se nato kopira navzdol po stolpcu F:
Uporaba funkcije HLOOKUP za vrnitev cene pekovskega izdelka iz iskalne tabele.
=HLOOKUP(predmet,tabela_cenov,2,FALSE)
V tej funkciji HLOOKUP je ime obsega Postavka, ki je dano stolpcu Postavka v obsegu C3:C62, definirano kot argument iskalne_vrednosti, ime obsega celice Tabela Cena, ki je dana obsegu celic I1:M2, pa je argument table_array . Row_index_num trditev 2, ker želite Excel vrniti cene v drugi vrsti so cene Iskanje mizo in neobvezno obseg_iskanja argument FALSE, ker mora biti ime elementa na seznamu Daily prodaje ujema natanko ime elementa cen Iskanje mizo .
Če funkcija HLOOKUP uporablja razpon tabele cen za vnos cene na ducat za vsako postavko pekovskih izdelkov na seznamu dnevne prodaje, je posodobitev katere koli prodaje na seznamu zelo preprosta. Vse kar morate storiti je, da spremenite njegovo ceno/doz strošek v tem razponu in funkcija HLOOKUP takoj posodobi novo ceno na seznamu dnevne prodaje, kjer koli je artikel prodan.