Les funcions de cerca més populars a Excel 2013 són les funcions HLOOKUP (per a la cerca horitzontal) i VLOOKUP (per a la cerca vertical). Aquestes funcions es troben al menú desplegable Cerca i referència a la pestanya Fórmules de la cinta, així com a la categoria Cerca i referència al quadre de diàleg Insereix funció.
Formen part d'un grup potent de funcions que poden retornar valors buscant-los a les taules de dades.
La funció VLOOKUP cerca verticalment (de dalt a baix) la columna més a l'esquerra d'una taula de cerca fins que el programa localitza un valor que coincideixi o superi el que esteu buscant. La funció HLOOKUP cerca horitzontalment (d'esquerra a dreta) la fila superior d'una taula de cerca fins que localitza un valor que coincideixi o superi el que esteu buscant.
La funció BUSCARV utilitza la sintaxi següent:
BUSCAR V(valor_cerca, matriu_taula, num_índex_col,[cerca_interval])
La funció HLOOKUP segueix la sintaxi gairebé idèntica:
CERC_HL(valor_cerca, matriu_taula,núm_índex_fila,[cerca_interval])
En ambdues funcions, l' argument valor_de_cerca és el valor que voleu cercar a la taula de cerca, i matriu_taula és l'interval de cel·les o el nom de la taula de cerca que conté tant el valor a cercar com el valor relacionat a retornar.
L' argument col_index_num de la funció VLOOKUP és el número de la columna els valors de la qual es comparen amb l' argument lookup_value en una taula vertical. L' argument row_index_num de la funció HLOOKUP és el número de la fila els valors de la qual es comparen amb el valor de cerca en una taula horitzontal.
Quan introduïu els arguments col_index_num o row_index_num a les funcions BUSCARV i BUSCAR HL , heu d'introduir un valor superior a zero que no superi el nombre total de columnes o files a la taula de cerca.
L' argument range_lookup opcional tant a les funcions BUSCARV com a BUSCAR HL és el VERTADER o FALS lògic que especifica si voleu que Excel trobi una coincidència exacta o aproximada per al valor_cerca a la matriu_taula.
Quan especifiqueu TRUE o ometeu l' argument range_lookup a la funció BUSCAR V o BUSCAR HL , Excel troba una coincidència aproximada. Quan especifiqueu FALSE com a argument range_lookup , Excel només troba coincidències exactes.
La cerca de coincidències aproximades només s'aplica quan cerqueu entrades numèriques (en lloc de text) a la primera columna o fila de la taula de cerca vertical o horitzontal. Quan Excel no troba una coincidència exacta en aquesta columna o fila de cerca, localitza el següent valor més alt que no superi l' argument search_value i després retorna el valor a la columna o fila designada pels arguments col_index_num o row_index_num .
Quan utilitzeu les funcions BUSCAR V i BUSCAR HL, el text o les entrades numèriques de la columna o fila de cerca (és a dir, la columna més a l'esquerra d'una taula de cerca vertical o la fila superior d'una taula de cerca horitzontal) han de ser úniques. Aquestes entrades també s'han d'ordenar o ordenar en ordre ascendent; és a dir, l'ordre alfabètic de les entrades de text i l'ordre de menor a major per a les entrades numèriques.
La figura mostra un exemple d'ús de la funció BUSCAR V per retornar una propina del 15% o del 20% d'una taula de propina, depenent del total abans d'impostos del xec. La cel·la F3 conté la funció BUSCAR V:
=BUSCARV(Total_pretax, Taula_de propina, SI(Percentatge_de propina=0,15,2,3))
Aquesta fórmula retorna l'import de la propina en funció del percentatge de propina a la cel·la F1 i l'import abans d'impostos del xec a la cel·la F2.
Per utilitzar aquesta taula de propina, introduïu el percentatge de la propina (15% o 20%) a la cel·la F1 (anomenada Tip_Percentage) i l'import del xec abans d'impostos a la cel·la F2 (anomenada Pretax_Total). Aleshores, Excel cerca el valor que introduïu a la cel·la Pretax_Total de la primera columna de la taula de cerca, que inclou l'interval de cel·les A2:C101 i s'anomena Tip_Table.
Llavors, Excel baixa els valors de la primera columna de Tip_Table fins que troba una coincidència, i després el programa utilitza l' argument col_index_num a la funció VLOOKUP per determinar quina quantitat de propina d'aquesta fila de la taula ha de tornar a la cel·la F3.
Si Excel troba que el valor introduït a la cel·la Pretax_Total ($16,50 en aquest exemple) no coincideix exactament amb un dels valors de la primera columna de Tip_Table, el programa continua buscant l'interval de comparació fins que trobi el primer valor que supera. el total abans d'impostos (17,00 a la cel·la A19 en aquest exemple).
A continuació, Excel torna a pujar a la fila anterior de la taula i retorna el valor de la columna que coincideix amb l' argument col_index_num de la funció BUSCAR V. (Això és perquè l' argument opcional range_lookup s'ha omès de la funció.)
Tingueu en compte que l'exemple de la taula de suggeriments utilitza una funció IF per determinar l' argument col_index_num per a la funció BUSCARV a la cel·la F3.
La funció SI determina el número de columna que s'utilitzarà a la taula de suggeriments fent coincidir el percentatge introduït a Tip_Percentage (cel·la F1) amb 0,15. Si coincideixen, la funció retorna 2 com a argument col_index_num i la funció BUSCARV retorna un valor de la segona columna (la columna B del 15%) a l'interval Tip_Table.
En cas contrari, la funció SI retorna 3 com a argument col_index_num i la funció BUSCARV retorna un valor de la tercera columna (la columna del 20% C) a l'interval Tip_Table.
La figura següent mostra un exemple que utilitza la funció HLOOKUP per cercar el preu de cada article de fleca emmagatzemat en una taula de cerca de preus independent i després per tornar aquest preu a la columna Preu/Doz de la llista de vendes diàries. La cel·la F3 conté la fórmula original amb la funció HLOOKUP que després es copia a la columna F:
=BUSCARH(element,taula_preus,2,FALSE)
En aquesta funció HLOOKUP, el nom d'interval Element que es dóna a la columna Element a l'interval C3:C62 es defineix com l' argument valor_cerca i la taula de preus del nom de l'interval de cel·les que es dóna a l'interval de cel·les I1:M2 és l' argument table_array .
L' argument row_index_num és 2 perquè voleu que Excel torni els preus a la segona fila de la taula de cerca de preus, i l' argument opcional range_lookup és FAL perquè el nom de l'article a la llista de vendes diàries ha de coincidir exactament amb el nom de l'article a la taula de cerca de preus. .
En fer que la funció HLOOKUP utilitzi l'interval de la taula de preus per introduir el preu per dotzena de cada article de fleca a la llista de vendes diàries, podeu actualitzar qualsevol de les vendes de la llista.
Tot el que heu de fer és canviar el seu cost Preu/Doz en aquest rang, i la funció HLOOKUP actualitza immediatament el nou preu a la llista de vendes diàries allà on es ven l'article.