Cuando tiene que consultar los datos de varias hojas o tablas, buscarlos físicamente puede ser un trabajo agotador. Google Sheets ayuda a superar este problema con su función VLOOKUP.
Con la función VLOOKUP, o de búsqueda vertical, puede hacer que las Hojas de cálculo de Google busquen automáticamente valores y recuperen los datos que coincidan de otra tabla. Esta tabla puede estar en la misma hoja o en una hoja diferente.
El único problema es que la sintaxis de la función VLOOKUP sólo permite buscar una columna a la vez.
Así que si tiene varios criterios o quiere comprobar varias columnas interrelacionadas al mismo tiempo, tiene que encontrar una forma de sortear esta limitación.
Esto puede hacerse combinando la función VLOOKUP con otras funciones de Google Sheets.
Glosario
Sintaxis de una función VLOOKUP normal
En general, una función VLOOKUP tiene la siguiente sintaxis:
=VLOOKUP(clave_buscada, rango, índice, [is_sorted])Aquí,
- search_key es el valor de la clave que se quiere buscar en otra tabla. Puede ser un valor o una referencia a una celda que contenga el valor.
- range es el rango de celdas (en la tabla de origen) dentro del cual quiere que la función VLOOKUP busque la search_key . Recuerde asegurarse de que este rango contiene la columna con la search_key como primera columna. Además, asegúrese de que contiene la columna con su valor objetivo (que desea recuperar).
- el índice es el número de columna dentro del rango que contiene el valor objetivo que desea recuperar. Tenga en cuenta que la primera columna dentro del rango tiene un índice de 1, la segunda columna tiene un índice de 2, y así sucesivamente.
- is_sorted es un parámetro opcional. Puede ser TRUE o FALSE. Indica si la columna de búsqueda debe estar ordenada o no.
Por ejemplo, en la siguiente imagen, tenemos dos tablas. Una que contiene la información personal de los empleados y otra que contiene el total de sus ventas en un mes determinado.
>Ambas tablas tienen una columna común, el ID del empleado, por lo que podría considerarse como un valor clave o un identificador único en ambas tablas.
Para mostrar la tarifa por hora de, por ejemplo, el ID de empleado " E010 ", deberá recuperarla de la tabla de empleados mediante la función VLOOKUP. Esto es lo que introducirá en la celda E3
=VLOOKUP(A3,$A$3:$C$8,3,false)Para que obtenga el siguiente resultado:
>¿Cuándo necesitamos hacer VLOOKUP de criterios múltiples en las hojas de cálculo de Google?
El caso anterior sólo implicaba la búsqueda de un único criterio, una coincidencia para el ID del empleado. Sin embargo, la mayoría de las veces el criterio es más complejo que eso.
Puede haber varias razones para los criterios múltiples de VLOOKUP. He aquí algunos ejemplos:
- Podría tener columnas separadas para los nombres y apellidos en la tabla de origen. Así que tendría que buscar ambas columnas para recuperar el valor correspondiente, como se muestra a continuación:
>Puede que necesite comprobar la satisfacción de 2 o más condiciones para recuperar un valor. Por ejemplo, podría necesitar la suma de las puntuaciones de sólo los alumnos que hayan aprobado un examen y hayan estudiado francés, como se muestra a continuación:
>Puede que necesite buscar en otra tabla el salario de un empleado de un determinado departamento en un determinado código de área.
>
Hay muchas otras situaciones como las anteriores. Pero usted entiende lo esencial.
Podría argumentar que utilizar un FILTRO sería una opción más fácil para las situaciones anteriores. Sin embargo, el FILTRO no puede recuperar datos de una hoja diferente.
El uso de funciones IF también será exagerado porque entonces tendrá que lidiar con múltiples IFs anidados (¡y a nadie le gusta eso!).
Cómo hacer VLOOKUP de múltiples criterios en Google Sheets
Tomemos el siguiente ejemplo, donde se tiene una tabla que contiene los Bonos correspondientes a cada Departamento y Código de Área.
>En la tabla 2, tenemos que buscar la Bonificación correspondiente a un Departamento concreto en un código de Área determinado y mostrar el valor recuperado en la columna Bonificación (columna E).
Hay dos maneras de lograr esto utilizando la función VLOOKUP:
- Utilizando una columna auxiliar
- Utilización de la función ARRAYFORMULA
Uso de una columna auxiliar para VLOOKUP de múltiples criterios en Google Sheets
El primer método implica el uso de una columna adicional "Helper", que contendrá una combinación de las celdas de los criterios.
En nuestro ejemplo, podemos insertar la columna "Helper" justo antes de la columna "Department", para que sea la primera columna del rango de búsqueda.
La columna Helper puede contener una combinación del código de Departamento y de Área para cada fila, separados por un espacio.
Veamos los pasos para crear y utilizar esta columna de ayuda con VLOOKUP para obtener el valor de Bono apropiado para la Tabla 2:
- Inserte una nueva columna justo antes de la primera columna de la Tabla 1. Esta actuará como nuestra columna de ayuda. Para ello, debe hacer clic con el botón derecho del ratón en la cabecera de la primera columna (columna A) y seleccionar "Insertar 1 izquierda" en el menú contextual que aparece.
>Seleccione la primera celda de la columna recién creada (celda A4) y escriba la fórmula: =B4&" "&C4.
- Pulse la tecla Return. La celda A4 debe contener ahora el contenido de la celda B4 y C5 juntos, separados sólo por un espacio.
>Haga doble clic en el asa de relleno de la celda A4 para copiar la fórmula al resto de las celdas de la columna A. Este es el aspecto que debería tener la columna A en este punto:
>Ahora que la columna de ayuda está lista, podemos seguir adelante y utilizar la función VLOOKUP. Observará que la adición de una nueva columna ha desplazado el contenido de la tabla 2 una celda hacia la derecha. Si lo desea, puede seleccionar el contenido y desplazarlo de nuevo a una celda a la izquierda.
>A continuación, seleccione la celda E17 (columna de bonificación de la tabla 2) y escriba la fórmula: =VLOOKUP(B17&""&7,$A$4:$D$12,4, false)
- Pulse la tecla de retorno.
- Ahora debería ver el valor de bonificación correspondiente a las "Operaciones BH12" de la Tabla 1.
>Haga doble clic en el tirador de relleno de la celda E17 para copiar la fórmula en el resto de las celdas de la columna A.
- Ahora debería ver todos los valores de bonificación correspondientes a cada departamento y código de área en la tabla 2.
>
Nota : Cuando escriba la fórmula VLOOKUP, no olvide bloquear las referencias del segundo parámetro pulsando la tecla F4. Esto asegurará que el rango de búsqueda no se desplace cuando la fórmula se copie al resto de las celdas.
Explicación de la fórmula
Vamos a desglosar la fórmula VLOOKUP a continuación para entender cada parámetro utilizado:
=VLOOKUP(B17&" "&C17,$A$4:$D$12,4, false)
- search_key : La clave de búsqueda que utilizamos aquí es una combinación de valores de Departamento y Código de área que queremos buscar, separados por un espacio (que es exactamente el formato que utilizamos en nuestra columna Helper).
- rango : Sabemos que nuestro rango de búsqueda debe tener siempre la columna de búsqueda como primera columna. El rango de búsqueda comienza ahora desde A4 hasta D12, ya que A4 es la primera celda de nuestra columna de ayuda.
- index : Debido a la adición de una columna adicional a la izquierda, nuestra columna objetivo se ha desplazado ahora una celda a la derecha. Por lo tanto, la columna Bonus se encuentra ahora en el índice 4 de nuestro rango de búsqueda.
- is_sorted : Un valor FALSE para este parámetro indica que la primera columna del rango de búsqueda no necesita estar ordenada de forma ascendente.
Dado que tanto la clave de búsqueda como la columna Helper tienen exactamente el mismo formato (teniendo Departamento seguido de espacio, seguido de código de Área), la función VLOOKUP puede buscar fácilmente el Bono correspondiente y devolverlo.
Uso de la función ARRAYFORMULA para VLOOKUP de múltiples criterios en Google Sheets
Este método hace más o menos lo mismo que el primer método. La única diferencia es que esta vez la columna de ayuda se crea dinámicamente, en lugar de tener que crear físicamente una columna extra para ello.
El método hace uso de la función ARRAYFORMULA para crear una tabla "virtual" que contenga las siguientes columnas:
- Una columna que contiene una combinación de celdas en los criterios
- La columna de destino del rango de búsqueda
Apliquemos esto a nuestro caso. Estos son los pasos para crear y utilizar la función ARRAYFORMULA con VLOOKUP para obtener el valor de Bonus apropiado para la Tabla 2:
- Seleccione la celda E17 (columna Bono de la Tabla 2) y escriba la fórmula
=ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false))- Pulse la tecla de retorno.
- Ahora debería ver el valor del Bono correspondiente a Departamento: Operaciones y Código de Área:BH12 de la Tabla 1.
>Haga doble clic en el asa de relleno de la celda E17 para copiar la fórmula al resto de las celdas de la columna A.
- Ahora debería ver todos los valores de Bonus correspondientes a cada Departamento y Código de Área en la Tabla 2.
>
Nota : Cuando escriba la fórmula VLOOKUP, no olvide bloquear las referencias del segundo parámetro pulsando la tecla F4. Esto asegurará que el rango de búsqueda no se desplace cuando la fórmula se copie al resto de las celdas.
Explicación de la fórmula
Vamos a desglosar la fórmula que hemos utilizado para entender qué ha pasado exactamente:
=ARRAYFORMULA(VLOOKUP(B17&" "&C17,{$A$4:$A$12&" "&$B$4:$B$12,$C$4:$C$12},2,false))
- En primer lugar, utilizamos ARRAYFORMULA para crear una especie de tabla virtual. Esta tabla virtual debe contener las siguientes columnas:
- Una columna que contiene una combinación de valores de las celdas $A$4:$A$12 y de las celdas $B$4:$B$12 , separados por un espacio entre cada valor de la celda: A$2:$A$9&" "&$B$2:$B$9
- Una columna que contiene los valores de la columna Bonus de la tabla 1: C$4:$C$12
Se especifica entre llaves porque queremos devolver una matriz o una tabla virtual de celdas: =ArrayFormula({$A$2:$A$9&" "&$B$2:$B$9,$C$2:$D$9})
Observe que hemos utilizado el "," como separador para la fórmula de la matriz porque queremos tratar las partes como columnas contiguas, como se muestra a continuación:
>
- Si quisiéramos que fueran filas una al lado de la otra, habríamos utilizado un punto y coma ";" en lugar de una coma ','.
- A continuación, aplicamos la función VLOOKUP utilizando la fórmula de la matriz anterior como segundo parámetro. El resto de los parámetros son los mismos que antes:
- search_key: La clave de búsqueda vuelve a ser una combinación de valores de Departamento y Código de área que queremos buscar, separados por un espacio.
- rango: El rango es ahora la matriz o tabla virtual: {$A$2:$A$9&" "&$B$2:$B$9,$C$2:$D$9}
- índice: Dado que la tabla virtual creada consta de sólo dos columnas, donde Bonus es la segunda columna, utilizamos 2 como valor del índice.
- is_sorted: Un valor FALSE para este parámetro indica que la primera columna del rango de búsqueda no necesita estar ordenada de forma ascendente.
Dado que tanto la clave de búsqueda como la primera columna de la matriz devuelta en el rango tienen exactamente el mismo formato (teniendo Departamento seguido de espacio, seguido de código de Área), la función VLOOKUP puede buscar fácilmente el Bono correspondiente y devolverlo.
Esto ha sido sólo un breve repaso de dos formas en las que podemos utilizar la función VLOOKUP cuando hay múltiples criterios a tener en cuenta.
Le animamos a que pruebe por sí mismo los ejemplos que hemos tratado en este tutorial. Esto le ayudará a hacerse una idea de cómo funcionan las fórmulas y le proporcionará una comprensión más profunda de las mismas.
¡Espero que este tutorial le haya resultado útil!
Otros tutoriales de Google Sheets que pueden gustarle:
- ¿Cómo hacer VLOOKUP desde otra hoja en Google Sheets?
- ¿Cómo utilizar la función INDIRECTO en las Hojas de cálculo de Google
- Cómo comparar dos columnas en las Hojas de cálculo de Google (para obtener coincidencias y diferencias)
- Cómo combinar celdas en las hojas de cálculo de Google
- Cómo obtener el último valor de una columna en Google Sheets (fórmula de búsqueda)
- Cómo utilizar la función SUMIFS en Google Sheets