Cómo usar las funciones VLOOKUP y HLOOKUP en Excel 2016

  1. Software
  2. Oficina de Microsoft
  3. Excel
  4. Cómo usar las funciones VLOOKUP y HLOOKUP en Excel 2016

Libro Relacionado

Por Greg Harvey

Las más populares de las funciones de búsqueda de Excel 2016 son HLOOKUP (para búsqueda horizontal) y VLOOKUP (para búsqueda vertical). Estas funciones se encuentran en el menú desplegable Búsqueda y referencia de la ficha Fórmulas de la cinta, así como en la categoría Búsqueda y referencia de la ventana de diálogo Insertar función. Forman parte de un potente grupo de funciones que pueden devolver valores buscándolos en tablas de datos.

La función VLOOKUP busca verticalmente (de arriba a abajo) la columna de la izquierda de una tabla de búsqueda hasta que el programa localiza un valor que coincide o excede el que usted está buscando. La función HLOOKUP busca horizontalmente (de izquierda a derecha) la fila superior de una tabla de búsqueda hasta que localice un valor que coincida o supere el que está buscando.

La función VLOOKUP utiliza la siguiente sintaxis:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

La función HLOOKUP sigue una sintaxis casi idéntica:

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

En ambas funciones, el argumento lookup_value es el valor que desea buscar en la tabla Lookup, y table_array es el rango de celdas o nombre de la tabla Lookup que contiene tanto el valor a buscar como el valor relacionado a devolver.

El argumento col_index_num designa la columna de la tabla de búsqueda que contiene los valores que devuelve la función VLOOKUP basándose en la comparación del valor del argumento lookup_value con los del argumento table_array. Se determina el argumento col_index_num contando cuántas columnas tiene esta columna a la derecha de la primera columna de la tabla de consulta vertical, y se incluye la primera columna de la tabla de consulta en este recuento.

El argumento row_index_num designa la fila que contiene los valores que son devueltos por la función HLOOKUP en una tabla horizontal. El argumento row_index_num se determina contando cuántas filas hacia abajo tiene esta fila desde la fila superior de la tabla de Búsqueda horizontal. Una vez más, se incluye la línea superior de la tabla Búsqueda en este recuento.

Al introducir los argumentos col_index_num o row_index_num en las funciones VLOOKUP y HLOOKUP, el valor introducido no puede exceder el número total de columnas o filas de la tabla Lookup.

El argumento opcional range_lookup en las funciones VLOOKUP y HLOOKUP es el TRUE o FALSE lógico que especifica si desea que Excel encuentre una coincidencia exacta o aproximada para el valor lookup_value en el array table_array. Cuando especifica TRUE u omite el argumento range_lookup en la función VLOOKUP o HLOOKUP, Excel encuentra una coincidencia aproximada. Cuando se especifica FALSE como argumento range_lookup, Excel sólo encuentra coincidencias exactas.

La búsqueda de coincidencias aproximadas sólo se aplica cuando se buscan entradas numéricas (en lugar de texto) en la primera columna o fila de la tabla de búsqueda vertical u horizontal. Cuando Excel no encuentra una coincidencia exacta en esta columna o fila, localiza el siguiente valor más alto que no exceda el argumento lookup_value y luego devuelve el valor en la columna o fila designado por los argumentos col_index_num o row_index_num.

Cuando se utilizan las funciones VLOOKUP y HLOOKUP, las entradas de texto o numéricas en la columna o fila de Búsqueda (es decir, la columna más a la izquierda de una tabla de Búsqueda vertical o la fila superior de una tabla de Búsqueda horizontal) deben ser únicas. Estas entradas también deben organizarse o clasificarse en orden ascendente; es decir, en orden alfabético para las entradas de texto y en orden descendente a descendente para las entradas numéricas.

La figura muestra un ejemplo del uso de la función VLOOKUP para devolver una propina del 15% o 20% de una tabla de propinas, dependiendo del total antes de impuestos del cheque. La celda F3 contiene la función VLOOKUP:

=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Porcentaje=0.15,2,3))

Esta fórmula devuelve la cantidad de la punta basada en el porcentaje de punta en la celda F1 y la cantidad antes de impuestos del cheque en la celda F2.

Usando la función VLOOKUP para devolver la cantidad de la punta a añadir desde una tabla de Búsqueda.

Para usar esta tabla de propinas, introduzca el porcentaje de la propina (15% o 20%) en la celda F1 (denominada Tip_Porcentaje) y el importe del cheque antes de impuestos en la celda F2 (denominada Pretax_Total). Excel busca entonces el valor que se introduce en la celda Total_pre-impuestos en la primera columna de la tabla de Búsqueda, que incluye el rango de celdas A2:C101 y se llama Tip_Table.

Excel entonces mueve hacia abajo los valores en la primera columna de la tabla Tip_Table hasta que encuentra una coincidencia, con lo cual el programa usa el argumento col_index_num en la función VLOOKUP para determinar qué cantidad de punta de esa fila de la tabla regresa a la celda F3. Si Excel encuentra que el valor ingresado en la celda Total antes de impuestos ($16.50 en este ejemplo) no coincide exactamente con uno de los valores de la primera columna de la tabla Tip_Table, el programa continúa buscando en el rango de comparación hasta que encuentre el primer valor que exceda el total antes de impuestos (17.00 en la celda A19 en este ejemplo). Excel vuelve a la fila anterior de la tabla y devuelve el valor de la columna que coincide con el argumento col_index_num de la función VLOOKUP. (Esto se debe a que el argumento opcional range_lookup ha sido omitido de la función.)

Note que el ejemplo de la tabla de puntas en la figura usa una función IF para determinar el argumento col_index_num para la función VLOOKUP en la celda F3. La función IF determina el número de columna que se utilizará en la tabla de puntas haciendo coincidir el porcentaje introducido en Porcentaje_de_punta (celda F1) con 0,15. Si coinciden, la función devuelve 2 como argumento col_index_num y la función VLOOKUP devuelve un valor de la segunda columna (la columna B del 15%) en el rango Tip_Table. De lo contrario, la función IF devuelve 3 como argumento col_index_num y la función VLOOKUP devuelve un valor de la tercera columna (la columna C del 20%) en el rango Tip_Table.

La siguiente figura muestra un ejemplo que utiliza la función HLOOKUP para buscar el precio de cada artículo de panadería almacenado en una tabla de búsqueda de precios separada y luego devolver ese precio a la columna Precio/Doz de la lista de ventas diarias. La celda F3 contiene la fórmula original con la función HLOOKUP que luego se copia en la columna F:

Uso de la función HLOOKUP para devolver el precio de un producto de panadería desde una mesa de búsqueda

 =HLOOKUP(item,Price_table,2,FALSE)

En esta función HLOOKUP, el nombre de rango Item que se le da a la columna Item en el rango C3:C62 se define como el argumento lookup_value y el nombre de rango de celda Tabla de precios que se le da al rango de celdas I1:M2 es el argumento table_array. El argumento row_index_num es 2 porque desea que Excel devuelva los precios en la segunda fila de la tabla Prices Lookup, y el argumento opcional range_lookup es FALSE porque el nombre del artículo en la lista de ventas diarias debe coincidir exactamente con el nombre del artículo en la tabla Prices Lookup.

Al hacer que la función HLOOKUP utilice el rango de la tabla de precios para introducir el precio por docena de cada artículo de panadería en la lista de ventas diarias, es muy sencillo actualizar cualquiera de las ventas de la lista. Todo lo que tienes que hacer es cambiar su precio/gasto de docena en este rango, y la función HLOOKUP actualiza inmediatamente el nuevo precio en la lista de ventas diarias dondequiera que se venda el artículo.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *