Cómo usar XLOOKUP() con varios criterios en Excel

XLOOKUP() en Excel es la función más reciente del grupo de funciones de búsqueda (LOOKUP(), VLOOKUP(), HLOOKUP() y XLOOKUP()) y ofrece muchas ventajas, más funcionalidades y mayor flexibilidad.

En este tutorial, primero veremos para qué sirve la función XLOOKUP() de Excel y por qué mejora a las funciones de búsqueda anteriores; después, revisaremos su sintaxis básica y, por último, iremos al grano: cómo usar XLOOKUP() con varios criterios.

Si necesitas aprender los fundamentos de Excel, el curso para principiantes Introduction to Excel es el lugar ideal para empezar.

Por qué usar XLOOKUP() en Excel

La función XLOOKUP() busca en un rango o matriz de datos y devuelve el elemento correspondiente a la primera coincidencia. Si no encuentra coincidencias, XLOOKUP() puede devolver una coincidencia aproximada si se indica un tipo de coincidencia específico. En muchos aspectos, XLOOKUP() supera a sus predecesoras en Excel (VLOOKUP(), HLOOKUP() y LOOKUP()).

En concreto, permite:

• buscar datos tanto en horizontal como en vertical, y en cualquier dirección
• usar varios criterios de búsqueda
• devolver una coincidencia aproximada, con coincidencia exacta por defecto
• hacer coincidencias parciales
• devolver varias columnas y filas
• mostrar un texto personalizado cuando no hay coincidencias.

Además, XLOOKUP() rinde mejor que las funciones de búsqueda antiguas de Excel, algo clave cuando buscamos en grandes volúmenes de datos.

Cómo usar XLOOKUP() con una condición

Veamos brevemente la sintaxis básica de XLOOKUP():

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

donde los parámetros obligatorios son:

• lookup_value—el valor que quieres buscar

• lookup_array—la matriz o rango donde buscar

• return_array—la matriz o rango desde donde devolver el resultado

y los parámetros opcionales son:

• [if_not_found]—el texto a devolver cuando no haya coincidencias

• [match_mode]—el tipo de coincidencia (exacta o aproximada) y qué devolver si no se encuentra coincidencia.

• [search_mode]—el modo de búsqueda (directa o inversa, búsqueda binaria en la matriz de búsqueda ordenada ascendente o descendente).

Más adelante veremos ejemplos con los parámetros opcionales.

Si quieres aprender o repasar otras funciones y fórmulas útiles de Excel, echa un vistazo al tutorial The 15 Basic Excel Formulas Everyone Needs to Know y a la Excel Formulas Cheat Sheet.

Cómo usar XLOOKUP() con varios criterios

Como vemos en su sintaxis básica, XLOOKUP() está pensada por defecto para trabajar con un único valor de búsqueda de una variable.

Sin embargo, podemos adaptarla para usarla sobre varias variables a la vez, buscando un valor distinto en cada variable. Es decir, podemos realizar búsquedas con XLOOKUP() aplicando varios criterios. Para hacerlo, hay dos enfoques principales: concatenación y expresiones booleanas. Veremos ambos enseguida.

Antes de entrar en lo técnico, veamos la tabla de Excel con la que trabajaremos. Recoge información sobre 10 gatos: sus nombres, colores y edades:

En nuestros ejemplos, usaremos XLOOKUP() para encontrar una de las tres características de un gato usando las otras dos.

Que la tabla sea simple está bien: podríamos localizar cada dato a simple vista. Aquí lo importante es entender los principios de usar XLOOKUP() con varios criterios. Una vez los domines, podrás aplicarlos a escenarios reales más complejos, por ejemplo, con más de dos criterios o requisitos específicos.

XLOOKUP() con varios criterios usando concatenación

El enfoque de concatenación es muy directo: hay que concatenar entre sí los valores de búsqueda y las matrices correspondientes. Veamos cómo funciona.

Supón que queremos encontrar la edad de una gata blanca y gris llamada Nala. Para mayor comodidad, añadimos estos dos criterios en la misma hoja donde está la tabla:

En este caso, la fórmula de XLOOKUP() sería:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Devolverá 2.5, la edad de la primera Nala blanca y gris (mi gata 😺). Recuerda: XLOOKUP() devuelve el elemento correspondiente a la primera coincidencia.

Para entender qué representan los dos primeros componentes de la fórmula—los que llevan el ampersand—podemos ejecutar cada uno en una celda aparte de Excel:

=B2:B11&C2:C11

El resultado es NalaWhite y Grey.

=B2:B11&C2:C11

El resultado es:

Sí, así de sencillo: hemos concatenado los valores de búsqueda y las matrices de búsqueda entre sí, siguiendo la sintaxis básica de XLOOKUP() que vimos antes.

XLOOKUP() con varios criterios usando expresiones booleanas

Ahora resolvamos la misma tarea—encontrar la edad de una Nala blanca y gris—con el segundo enfoque: expresiones booleanas. La fórmula de XLOOKUP() sería:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Como era de esperar, también devolverá 2.5, la edad de la primera Nala blanca y gris.

Veamos los componentes. Aquí, 1 significa TRUE; es decir, buscamos el valor TRUE en la matriz de búsqueda que representa el segundo componente de la fórmula—(B2:B11=G2)*(C2:C11=G3).

Este componente contiene dos multiplicandos, cada uno comprobando una condición: el primero verifica si el nombre coincide con el que buscamos y el segundo, si el color coincide. Ejecutemos cada multiplicando en una celda aparte:

=B2:B11=G2

El resultado es:

=C2:C11=G3

El resultado es:

Ahora ejecutemos todo el segundo componente:

=(B2:B11=G2)*(C2:C11=G3)

El resultado es:

Aunque vemos dos valores 1 en el resultado (dos TRUE), XLOOKUP() devuelve el elemento correspondiente a la primera coincidencia.

El enfoque con expresiones booleanas para usar XLOOKUP() con varios criterios parece más complejo y menos intuitivo que la concatenación. Y sería aún peor con más de dos criterios. ¿Por qué no usar siempre concatenación entonces?

Porque las expresiones booleanas nos dan mucha más flexibilidad al usar XLOOKUP() con varios criterios. Veamos sus ventajas frente a la concatenación.

Comprobar el valor FALSE

En la fórmula anterior comprobábamos si la expresión booleana era TRUE, pasando el valor 1:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

En algunos casos, podemos necesitar comprobar si la expresión es FALSE. Por ejemplo, queremos la edad del primer gato que no sea una Nala blanca y gris. Debemos modificar así:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

El resultado será 1, la edad del primer gato que no es una Nala blanca y gris (en concreto, es una Nala tricolor, pero no blanca y gris).

Usar operadores lógicos

Con expresiones booleanas no estamos limitados a comprobar igualdad. Por ejemplo, queremos encontrar el color de una Nala con menos de 2 años:

La fórmula de XLOOKUP() sería:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

El resultado será Tricolor.

Cumplir al menos un criterio

Hasta ahora comprobábamos que se cumplieran todos los criterios. En otros casos, podemos necesitar cumplir al menos uno.

Para verlo, volvamos a la tarea inicial—encontrar la edad de una Nala blanca y gris:

Esta vez, queremos la edad de un gato que sea o bien Nala o bien blanco y gris. La fórmula sería:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Devuelve 1, la edad de una Nala tricolor.

Recuerda que, cuando buscábamos la edad de un gato que fuera Nala y además blanco y gris, la fórmula era:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Cambiar * por + marca la diferencia.

Si quieres exprimir Excel al máximo, plantéate el itinerario de habilidades Excel Fundamentals, completo, práctico y bien estructurado.

Parámetros opcionales de XLOOKUP() con varios criterios

Igual que en el uso con un único criterio, podemos aprovechar las opciones adicionales de XLOOKUP() al trabajar con varios criterios. Aquí entran en juego los parámetros opcionales [if_not_found], [match_mode] y [search_mode].

Veamos rápidamente ejemplos de cada uno con XLOOKUP() y varios criterios. Para simplificar, usaremos el enfoque de concatenación en todos.

Devolver un texto personalizado cuando no hay coincidencias

Aquí buscamos la edad de una Nala negra—un caso inexistente en nuestra tabla:

En este caso, añadimos a la fórmula el parámetro opcional [if_not_found] con el texto a devolver si no hay coincidencias:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Efectivamente, la función devolvió el texto indicado: No cat is found. Si no indicáramos texto, devolvería #N/A.

Devolver una coincidencia aproximada

Imagina que buscamos la edad de una Nala tricolor, pero no estamos seguros de si en la tabla figura como "Tricolor" o "Tricolour". Aquí necesitamos una coincidencia aproximada con comodines y pasar a XLOOKUP() el argumento [match_mode] igual a 2.

En la tabla usamos un asterisco (*), que representa cualquier número de caracteres, incluido 0. Puedes consultar otros comodines en la documentación de Microsoft Office.

La fórmula de XLOOKUP() sería:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Y devolverá 1, la edad de la primera (y única) Nala tricolor de la tabla.

Ten en cuenta que el argumento 2 indica que buscamos una coincidencia aproximada, no la edad de una Nala de color "Tricolo*r". En nuestro caso, el asterisco ha capturado la palabra "Tricolor", pero haría lo mismo con "Tricolour" o, por ejemplo, "Tricolooor".

Realizar una búsqueda inversa

Hagamos el mismo ejercicio del principio—buscar la edad de una Nala blanca y gris—, pero esta vez empezando por el último elemento.

Para ello, añadimos a XLOOKUP() el parámetro opcional [search_mode] con valor -1, así:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

La función devuelve 7, la edad de la primera Nala blanca y gris desde el final de la matriz de búsqueda.

XLOOKUP() vs. INDEX() y MATCH()

En versiones antiguas de Excel, para reproducir algo similar a XLOOKUP() con varios criterios había que combinar INDEX() y MATCH(). Sin entrar en la sintaxis de ambas, veamos cómo encontrar la edad de la primera Nala blanca y gris con el método antiguo INDEX() + MATCH() y con la nueva XLOOKUP() (para las referencias de celda, consulta la tabla anterior):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Aunque los argumentos se parecen, estas son las principales ventajas de usar XLOOKUP() frente al método antiguo:

• Basta con una sola función: no hace falta combinarlas.

• Posibilidad de aplicar el enfoque de concatenación cuando convenga.

• Disponibilidad de parámetros opcionales (a MATCH() le faltan).