Funksionet më të njohura të kërkimit në Excel 2013 janë funksionet HLOOKUP (për kërkimin horizontal) dhe VLOOKUP (për kërkimin vertikal). Këto funksione janë të vendosura në menynë rënëse "Kërkimi dhe referenca" në skedën "Formulat" të shiritit, si dhe në kategorinë "Kërkimi dhe referenca" në kutinë e dialogut "Fut Funksionin".
Ato janë pjesë e një grupi të fuqishëm funksionesh që mund të kthejnë vlera duke i kërkuar në tabelat e të dhënave.
Funksioni VLOOKUP kërkon vertikalisht (nga lart poshtë) kolonën më të majtë të një tabele Lookup derisa programi të gjejë një vlerë që përputhet ose tejkalon atë që po kërkoni. Funksioni HLOOKUP kërkon horizontalisht (nga e majta në të djathtë) rreshtin më të lartë të një tabele "Kërkimi" derisa të gjejë një vlerë që përputhet ose tejkalon atë që po kërkoni.
Funksioni VLOOKUP përdor sintaksën e mëposhtme:
VLOOKUP(vlera_kërkuese, grup_tabele, numri_indeksi_kole, [kërkimi_range])
Funksioni HLOOKUP ndjek sintaksën pothuajse identike:
HLOOKUP(vlera_kërkuese, grup_tabele, numri_indeksi_rreshti, [kërkimi_range])
Në të dy funksionet, argumenti lookup_value është vlera që dëshironi të kërkoni në tabelën e kërkimit dhe tabela_array është diapazoni i qelizave ose emri i tabelës së kërkimit që përmban vlerën për të kërkuar dhe vlerën përkatëse për t'u kthyer.
Col_index_num argument në funksion VLOOKUP është numri i kolonës vlerat e të cilit janë krahasuar me lookup_value argumentit në një tryezë vertikale. Row_index_num argument në funksion HLOOKUP është numri i rreshtit vlerat e të cilit janë krahasuar me lookup_value në një tavolinë horizontale.
Kur hyjnë në col_index_num ose row_index_num argumente në funksionet e VLOOKUP dhe HLOOKUP, ju duhet të shkruani një më i madh se zero vlerë që nuk tejkalon numrin e përgjithshëm të shtyllave ose rreshtave në tabelën e kërkimit.
Opsional range_lookup Argumenti në të dyja funksionet HLOOKUP e VLOOKUP dhe është TRUE logjike apo e rreme që specifikon nëse doni Excel për të gjetur një ndeshje e saktë apo të përafërt për lookup_value në table_array.
Kur specifikoni TRUE ose hiqni argumentin range_lookup në funksionin VLOOKUP ose HLOOKUP, Excel gjen një përputhje të përafërt. Kur specifikoni FALSE si argument range_lookup , Excel gjen vetëm përputhje të sakta.
Gjetja e përputhjeve të përafërta ka të bëjë vetëm kur jeni duke kërkuar hyrje numerike (në vend të tekstit) në kolonën ose rreshtin e parë të tabelës së kërkimit vertikal ose horizontal. Kur Excel nuk gjen një ndeshje e saktë në këtë kolonë lookup ose rresht, ajo ndodhet vlerën e ardhshëm më të lartë që nuk e tejkalon lookup_value argumentin dhe pastaj e kthen vlerën në kolonën apo rresht të caktuar nga col_index_num ose row_index_num argumente.
Kur përdorni funksionet VLOOKUP dhe HLOOKUP, teksti ose hyrjet numerike në kolonën ose rreshtin e kërkimit (d.m.th., kolona më e majtë e një tabele kërkimi vertikal ose rreshti i sipërm i një tabele kërkimi horizontal) duhet të jenë unik. Këto shënime duhet gjithashtu të renditen ose renditen në rend rritës; d.m.th., renditja alfabetike për hyrjet e tekstit dhe renditja nga më e ulëta në më e larta për hyrjet numerike.
Figura tregon një shembull të përdorimit të funksionit VLOOKUP për të kthyer një bakshish 15% ose 20% nga një tabelë bakshish, në varësi të totalit të çekut para tatimit. Qeliza F3 përmban funksionin VLOOKUP:
=VLOOKUP(Taksimi_Total,Tabela_Këshilla,IF(Përqindja_Bashkë=0.15,2,3))
Kjo formulë kthen shumën e bakshishit bazuar në përqindjen e bakshishit në qelizën F1 dhe shumën para tatimit të çekut në qelizën F2.
Për të përdorur këtë tabelë të bakshishit, vendosni përqindjen e bakshishit (15% ose 20%) në qelizën F1 (e quajtur Tip_Percentage) dhe shumën e çekut para taksës në qelizën F2 (e quajtur Pretax_Total). Më pas, Excel kërkon vlerën që futni në qelizën Pretax_Total në kolonën e parë të tabelës "Kërkimi", e cila përfshin gamën e qelizave A2:C101 dhe quhet Tip_Table.
Më pas, Excel lëviz poshtë vlerat në kolonën e parë të Tip_Table derisa të gjejë një përputhje, me ç'rast programi përdor argumentin col_index_num në funksionin VLOOKUP për të përcaktuar se cila shumë majash nga ai rresht i tabelës do të kthehet në qelizën F3.
Nëse Excel zbulon se vlera e futur në qelizën Pretax_Total (16,50 dollarë në këtë shembull) nuk përputhet saktësisht me një nga vlerat në kolonën e parë të Tip_Table, programi vazhdon të kërkojë në intervalin e krahasimit derisa të ndeshet me vlerën e parë që tejkalon totali para tatimit (17.00 në qelizën A19 në këtë shembull).
Më pas, Excel kthehet lart në rreshtin e mëparshëm në tabelë dhe kthen vlerën në kolonë që përputhet me argumentin col_index_num të funksionit VLOOKUP. (Kjo për shkak se argumenti opsional range_lookup është hequr nga funksioni.)
Vini re se shembulli i tabelës tip përdor një funksion IF për të përcaktuar argumentin col_index_num për funksionin VLOOKUP në qelizën F3.
Funksioni IF përcakton numrin e kolonës që do të përdoret në tabelën e tipit duke përputhur përqindjen e futur në Tip_Percentage (qeliza F1) me 0,15. Nëse përputhen, funksioni kthen 2 si argument col_index_num dhe funksioni VLOOKUP kthen një vlerë nga kolona e dytë (kolona 15% B) në diapazonin Tip_Table.
Përndryshe, funksioni IF kthen 3 si argument col_index_num dhe funksioni VLOOKUP kthen një vlerë nga kolona e tretë (kolona 20% C) në diapazonin Tip_Table.
Figura e mëposhtme tregon një shembull që përdor funksionin HLOOKUP për të kërkuar çmimin e çdo artikulli buke të ruajtur në një tabelë të veçantë të kërkimit të çmimeve dhe më pas për ta kthyer atë çmim në kolonën Çmimi/Doz të listës së Shitjeve Ditore. Qeliza F3 përmban formulën origjinale me funksionin HLOOKUP që më pas kopjohet në kolonën F:
=HLOOKUP(artikulli,tabela_çmimi,2,FALSE)
Në këtë funksion HLOOKUP, emri i diapazonit Item që i është dhënë kolonës Item në rangun C3:C62 përcaktohet si argumenti lookup_vlera dhe emri i gamës së qelizave Tabela Çmimi që i jepet diapazonit të qelizave I1:M2 është argumenti i grupit_tabela .
Row_index_num Argumenti është 2 për shkak se ju doni Excel të kthehen çmimet në rreshtin e dytë të tabelës çmimet Lookup, dhe fakultative range_lookup argumenti është i rremë, sepse emri artikull në listën e Daily Shitjeve duhet të përputhen saktësisht emrin pika në tabelën e çmimeve Lookup .
Duke pasur funksionin HLOOKUP të përdorë diapazonin e tabelës së çmimeve për të futur çmimin për një duzinë për çdo artikull të mallrave buke në listën e shitjeve ditore, ju e bëni një çështje shumë të thjeshtë përditësimin e ndonjë prej shitjeve në listë.
E tëra çfarë ju duhet të bëni është të ndryshoni koston e tij Çmimi/Doz në këtë gamë dhe funksioni HLOOKUP përditëson menjëherë çmimin e ri në listën e shitjeve ditore kudo që artikulli shitet.