XLOOKUP(): la función de búsqueda más versátil de Excel

Si alguna vez has usado VLOOKUP() y te has quedado atascado porque necesitabas buscar hacia la izquierda, XLOOKUP() resuelve ese problema. Es la función de búsqueda más reciente de Excel. Puede sustituir VLOOKUP(), HLOOKUP() e incluso la mayoría de fórmulas con INDEX() y MATCH() con una sola fórmula sencilla.

XLOOKUP() puede buscar en cualquier dirección, devolver más de una columna a la vez y mostrar un mensaje personalizado cuando falta un valor. 

En esta guía, veremos primero la sintaxis. Después construiremos fórmulas reales paso a paso, usando un único conjunto de datos de principio a fin. También cubriremos las coincidencias exactas y aproximadas, cómo devolver más de una columna, cómo gestionar errores y cómo se compara XLOOKUP() con VLOOKUP() y INDEX()/MATCH(). 

Cómo usar XLOOKUP() en Excel

Vamos a crear una fórmula real de XLOOKUP() desde cero. 

Nuestro conjunto de datos tiene cinco productos, cada uno con un ID, un nombre, una categoría y un precio, de las columnas A a D, con encabezados en la fila 1.

Conjunto de datos para XLOOKUP(). Imagen del autor.

Imagina que queremos encontrar el precio de "P104". Veamos cómo hacerlo paso a paso.

Buscar un único valor

Haz clic en una celda vacía donde quieras que aparezca el resultado. Yo uso F2. Y escribe la siguiente fórmula:

=XLOOKUP("P104", A2:A6, D2:D6)

En esta fórmula: 

• "P104" es el lookup_value, lo que buscamos

• A2:A6 es el lookup_array, el rango en el que Excel busca

• D2:D6 es el return_array, el rango del que Excel extrae la respuesta

Excel busca en la columna A, "Product ID", "P104", lo encuentra en la fila 5 y devuelve el valor correspondiente de la columna D: 45 $.

En lugar de escribir directamente "P104", puedes referenciar una celda para cambiar el valor de búsqueda sin editar la fórmula. En nuestro caso, la celda A5 contiene "P104", así que la fórmula pasa a ser:

=XLOOKUP(A5, A2:A6, D2:D6)

Usa XLOOKUP() en Excel. Imagen del autor.

Ahora puedes escribir cualquier ID de producto en A5 y el precio se actualiza automáticamente.

Devolver un valor de otra columna

Aquí se ve la gran diferencia entre XLOOKUP() y VLOOKUP(). No estás limitado a devolver un valor de una columna a la derecha de tu columna de búsqueda.

Supongamos que, en lugar del precio, quieres la categoría de "P102". La configuración es la misma, solo apunta el return_array a la columna C, "Category":

=XLOOKUP(A3, A2:A6, C2:C6)

Aquí, A3 devuelve Home. Si cambias a A5, se actualiza a Electronics. Del mismo modo, cambia de nuevo el return_array para traer el nombre del producto, el precio u otra columna, usando el mismo lookup_value y lookup_array.

XLOOKUP() devuelve un valor de otra columna. Imagen del autor.

Con VLOOKUP(), contarías columnas desde tu columna de búsqueda y ajustarías un índice de columna cada vez que quisieras un resultado distinto. Con XLOOKUP(), solo apuntas directamente a la columna que quieres. 

Copiar la fórmula a varias filas

Hasta ahora hemos buscado un valor cada vez. Pero la mayoría de hojas reales necesitan buscar muchos valores a la vez, por ejemplo, una lista completa de IDs de pedido, cada uno con su precio correspondiente.

Así es como lo montas: la columna A, empezando en A2, ya contiene nuestra lista de Product IDs. Queremos que los precios aparezcan al lado de cada uno, en la columna F.

Para conseguirlo, haz clic en F2 y escribe la fórmula, haciendo referencia a A2 como valor de búsqueda así:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Ahora vuelve a hacer clic en F2 y arrastra el control de relleno (el pequeño cuadrado en la esquina inferior derecha de la celda) hacia abajo por el resto de filas.

Excel copia la fórmula hacia abajo y cada fila devuelve ahora el precio correcto para su propio Product ID. El ID de A2 obtiene su precio en F2, el de A3 en F3, y así sucesivamente.

Copia la fórmula al resto de celdas. Imagen del autor.

Fíjate en los signos de dólar alrededor de A2:A6 y D2:D6. Esto se llama referencia absoluta. Bloquea el rango para que no se desplace al copiar la fórmula hacia abajo. 

Esto importa aquí, porque cada fila necesita buscar en la misma lista de productos, solo que con un valor de búsqueda distinto cada vez.

Cómo usar coincidencia exacta y aproximada en XLOOKUP()

Por defecto, XLOOKUP() busca una coincidencia exacta. Si no la encuentra, devuelve un error, a menos que hayas configurado el argumento if_not_found que vimos antes. 

Pero XLOOKUP() también puede encontrar la coincidencia más cercana usando el argumento match_mode. 

Coincidencia exacta (por defecto)

No necesitas escribir nada adicional. Todas las fórmulas que hemos creado hasta ahora ya usan coincidencia exacta, porque es el comportamiento predeterminado de XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel busca el valor exacto de A2 y solo devuelve un resultado si encuentra esa coincidencia precisa en el rango de búsqueda.

Si quieres escribirlo de forma explícita, añade un cero como cuarto argumento:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() devuelve una coincidencia exacta. Imagen del autor.

¿Ves el espacio antes del 0? Eso salta if_not_found, que dejamos en blanco aquí, y pasa directamente a match_mode. La mayoría de las veces puedes omitir este argumento y dejar que Excel use por defecto la coincidencia exacta.

Coincidencia aproximada

Ahora imagina que tus datos no tienen un valor para cada posible búsqueda. Piensa en una estructura de comisiones donde los comerciales cobran distintos porcentajes según el tramo en el que caen sus ventas totales. No tendrás una fila para cada importe, solo el inicio de cada tramo.

Aquí es donde ayuda una coincidencia aproximada. 

Configura match_mode en -1 y XLOOKUP() encontrará el valor más grande que sea menor o igual que tu valor de búsqueda.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Supón que tu rango de búsqueda contiene umbrales de ventas de 0 $, 5.000 $ y 10.000 $, cada uno con un tipo de comisión distinto. 

Un comercial con 7.500 $ en ventas no coincide exactamente con ninguna fila. Con match_mode en -1, XLOOKUP() encuentra 5.000 $, el umbral más alto que no supera 7.500 $, y devuelve el tipo de comisión de esa fila.

Si pones match_mode en 1, XLOOKUP() invierte la lógica: encuentra el valor más pequeño que sea mayor o igual que tu valor de búsqueda. Esto es útil para cosas como tramos de peso de envío, donde redondeas al tramo superior en lugar de al inferior.

XLOOKUP() devuelve una coincidencia aproximada. Imagen del autor.

Nota: la coincidencia aproximada asume que tu rango de búsqueda está ordenado, ascendente para -1, descendente para 1. Si no está bien ordenado, XLOOKUP() puede devolver un resultado erróneo sin mostrar error, lo que es peor. Así que comprueba siempre el orden antes de usarlo.

Cómo buscar valores a la izquierda con XLOOKUP()

VLOOKUP() solo busca en la columna más a la izquierda de tu rango y devuelve un valor de una columna a su derecha; no puede hacerlo al revés. Su tercer argumento no es un rango, sino un índice de columna contado desde el borde izquierdo. 

No hay forma de decirle "ve una columna a la izquierda", así que si el valor que necesitas está a la izquierda de tu columna de búsqueda, te toca reorganizar los datos o añadir una columna auxiliar solo para que funcione.

XLOOKUP() no tiene esta limitación, porque le indicas rangos separados para la búsqueda y el retorno.

Volviendo a nuestra lista de productos: Product ID está en la columna A y Product Name en la columna B. 

Supón que tienes un nombre de producto y quieres encontrar su ID; lo contrario de lo que hace VLOOKUP().

En C9, escribe Bluetooth Speaker (un nombre que existe en la lista). En E2, introduce:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() busca en la columna B "Bluetooth Speaker", lo encuentra en la fila 5 y devuelve "P104" de la columna A, aunque la columna A esté a la izquierda de la B. 

XLOOKUP() permite buscar a la izquierda, a diferencia de VLOOKUP(). Imagen del autor.

Cómo devolver varias columnas con XLOOKUP()

XLOOKUP() puede devolver varias columnas a la vez, en una sola fórmula, gracias a las matrices dinámicas. Para ello, apunta el return_array a un rango en lugar de a una sola columna, y XLOOKUP() derrama la fila coincidente en tantas celdas como sean necesarias. 

Probémoslo con nuestra lista de productos. 

En C9, escribo un Product ID, por ejemplo, "P104". En E2 introduzco:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Fíjate en que el return_array es B2:D6, de tres columnas de ancho, que cubre Product Name, Category y Price. Al pulsar Intro, XLOOKUP() encuentra "P104" en la columna A y devuelve Bluetooth Speaker, Electronics y 45 $ de una vez, derramándolos en E9, F9 y G9.

XLOOKUP() devuelve varias columnas con una sola fórmula. Imagen del autor.

Esto es lo que significa rango de derrame. E9 tiene la fórmula real, y F9 y G9 se rellenan solos, sin fórmulas propias. 

Haz clic en F9 y verás en la barra de fórmulas una versión atenuada de la fórmula original, no algo escrito directamente ahí. Si intentas escribir en F9 mientras el derrame de E9 está activo, Excel mostrará un error de derrame. 

Veremos cómo solucionarlo en la sección de errores comunes.

Cómo gestionar valores faltantes en XLOOKUP()

Por defecto, XLOOKUP() muestra un error #N/A cuando no puede encontrar una coincidencia. El argumento if_not_found puede arreglarlo mostrando lo que tú quieras en lugar del error por defecto.  

Supón que en C9 escribo un Product ID que no existe en mis datos, como "P999".

Devolver un mensaje personalizado

En G2, introduzco:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Como "P999" no existe en la columna A, XLOOKUP() se salta el error y devuelve "Not Found". Cambia la frase por la que quieras, como "Check Product ID" o "No Match".

Muestra un mensaje personalizado en lugar del error por defecto. Imagen del autor.

Devolver una celda en blanco

A veces quieres una celda vacía sin texto. Para ello, usa dos comillas seguidas sin nada entre medias, así:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Úsalo, por ejemplo, cuando esta fórmula aparece en un informe donde un "Not Found" suelto quedaría feo junto a una columna de valores reales.

Muestra una celda en blanco en lugar del error por defecto. Imagen del autor.

Dejarlo como un error genuino

Si omites if_not_found por completo, XLOOKUP() vuelve a #N/A. Y no siempre es malo. Si construyes una fórmula para tu propio uso y quieres detectar al momento un error tipográfico o un dato que falta, ver #N/A es más útil que una celda en blanco que oculta el problema.

Nota: if_not_found solo captura coincidencias faltantes. Si tu lookup_array y tu return_array tienen tamaños distintos, por ejemplo, XLOOKUP() seguirá mostrando un #VALUE!, y if_not_found no lo evitará. Veremos ese caso concreto más abajo en Errores comunes.

Funciones avanzadas de XLOOKUP()

Veamos las funciones avanzadas de XLOOKUP()

Buscar de abajo arriba

Pon search_mode en -1 y XLOOKUP() recorre el rango desde abajo hacia arriba, en lugar de arriba abajo. Úsalo cuando tus datos tienen valores duplicados y quieres la entrada más reciente, no la primera.

Supón que en mi conjunto de datos tengo tres Customer IDs duplicados. En la celda E2, introduzco la fórmula por defecto que usa el enfoque de arriba abajo: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Esto se calcula desde el principio de la tabla y muestra el resultado de la primera coincidencia. 

Pero si quiero buscar desde abajo, usaré esta fórmula: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

En esta fórmula, -1 le indica a Excel que empiece a buscar desde el final del conjunto de datos. Y esto devolverá 340, que es el primer valor coincidente desde abajo.  

XLOOKUP() recorre los datos de abajo a arriba. Imagen del autor.

Modo de búsqueda binaria

Configura search_mode en 2 y XLOOKUP() cambiará de comprobar fila a fila a usar una búsqueda binaria. 

Así funciona: 

• Mira primero el elemento central del rango. 

• Si no coincide, descarta la mitad donde el valor no puede estar y mira el centro de lo que queda. 

• Repite el proceso, partiendo los datos por la mitad cada vez, hasta encontrar la coincidencia. 

• Si pones search_mode en -2, hace lo mismo pero empezando por el último elemento en lugar del primero.

Aquí, en E2, introduce:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Esta fórmula devuelve 200 en su lugar, una fila completamente distinta.

El modo de búsqueda binaria da un resultado incorrecto. Imagen del autor.

Porque la búsqueda binaria solo funciona si tu lookup_array está ordenado. Como nuestra columna de Customer ID no está ordenada, va C100, C101, C100, C102, C100, y la búsqueda binaria descarta la mitad equivocada y devuelve una coincidencia errónea. 

Ahora veamos qué pasa cuando los datos están realmente ordenados. 

Ordena el mismo registro por Customer ID, ascendente (los tres C100 al principio) y ejecuta de nuevo la misma fórmula. 

El modo de búsqueda binaria funciona con datos ordenados. Imagen del autor.

Esta vez, E2 devuelve 120, lo mismo que te daría una búsqueda normal. Esa es la diferencia. La búsqueda binaria solo se salta datos que puede descartar con seguridad, y eso solo se cumple cuando los datos están ordenados de antemano. 

Fórmulas XLOOKUP() anidadas

Anida una XLOOKUP() dentro de otra cuando el resultado de una búsqueda tiene que alimentar una segunda búsqueda, en lugar de un rango fijo.

Imagina que quieres encontrar un precio, pero de qué columna lo sacas depende de una búsqueda aparte, por ejemplo, el nivel de precios de un cliente.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

En esta fórmula, la XLOOKUP() interna primero averigua de qué rango de columna tirar, según D9, y luego la XLOOKUP() externa usa ese resultado como su return_array. 

XLOOKUP() anidadas. Imagen del autor.

XLOOKUP() vs. VLOOKUP()

Ya hemos visto en detalle cómo funciona XLOOKUP(). Aquí tienes su comparación con VLOOKUP().

Dos notas rápidas: 

• VLOOKUP() usa por defecto la coincidencia aproximada. Si olvidas el cuarto argumento, no mostrará un error; devolverá un valor incorrecto en silencio.

• Si insertas una columna en una hoja con VLOOKUP(), las fórmulas empiezan a devolver el valor equivocado sin avisar. XLOOKUP() evita esto porque referencia rangos directamente en lugar de contar columnas.

Nada de esto hace que VLOOKUP() quede obsoleto. Sigue funcionando bien para hojas simples y estáticas. Pero para cualquier cosa que construyas a partir de ahora, XLOOKUP() es la opción más segura.

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

Antes de que existiera XLOOKUP(), INDEX() y MATCH() juntos eran la forma estándar de esquivar las limitaciones de VLOOKUP(). Usa dos funciones en lugar de una. 

• MATCH() encuentra la posición de tu valor de búsqueda. 

• INDEX() usa esa posición para extraer el resultado de donde le indiques: izquierda o derecha, da igual.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Compáralo con la versión de XLOOKUP() que hemos usado en todo el artículo:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() solo funciona en Microsoft 365 y Excel 2021 en adelante, como ya comentamos. Si tu libro debe abrirse en Excel 2019 o 2016, o lo compartes con gente en versiones antiguas, INDEX() y MATCH() siguen funcionando en todas partes.

También hay un caso más puntual. 

INDEX() y MATCH() separan "encontrar la posición" de "devolver el valor", lo que ayuda en algunas fórmulas anidadas complejas. Es poco habitual, así que la mayoría no lo necesitará.

Fuera de estos dos casos, XLOOKUP() es la mejor opción para un libro nuevo. 

Errores comunes de XLOOKUP() y cómo solucionarlos

Aquí tienes una referencia rápida de los problemas más habituales y cómo arreglar cada uno.

Errores #N/A

Es la respuesta por defecto de XLOOKUP() cuando no encuentra una coincidencia. Yo me topé con esto antes, cuando mi lookup_value y lookup_array no cuadraban, y XLOOKUP() no tenía con qué comparar en la columna A.

Para solucionarlo: comprueba que tu lookup_value existe realmente en tu lookup_array, con la misma ortografía y formato. Si es normal que falten coincidencias, usa el argumento if_not_found para sustituir #N/A por algo más útil, como "Not Found" o una celda en blanco, como ya vimos. 

Rango de búsqueda incorrecto

Ocurre cuando tu lookup_array y return_array no están bien alineados: filas o columnas equivocadas, o rangos que no coinciden en tamaño. 

Según la versión de Excel o Sheets, puede aparecer como un error #VALUE! o a veces como #N/A con un mensaje adicional explicando el problema real. No dejes que el #N/A te haga pensar que falta una coincidencia. 

Para solucionarlo: asegúrate de que lookup_array y return_array cubren el mismo número de filas o columnas. Si lookup_array es A2:A5, return_array también debe abarcar de la fila 2 a la 5, no de la 4 a la 5 ni cualquier otro rango desajustado.

Incompatibilidad de tipos de datos

VLOOKUP() puede dar error porque esperaba un número pero recibió texto. XLOOKUP() puede tropezar con algo parecido. Si tu lookup_value es un número pero tu lookup_array almacena esos mismos valores como texto, o al revés, XLOOKUP() no encontrará coincidencia aunque los valores parezcan idénticos en pantalla.

Para solucionarlo: comprueba si tu lookup_value y lookup_array usan el mismo tipo de dato. Los números guardados como texto suelen alinearse a la izquierda en la celda en lugar de a la derecha. Envuelve tu lookup_value con VALUE() para convertir texto en número, o usa TEXT() para hacer lo contrario, según el caso.

Errores de derrame

Ocurren cuando tu return_array abarca más de una columna, pero las celdas donde XLOOKUP() necesita volcar el resultado no están vacías. Excel necesita espacio libre junto a la celda con la fórmula para derramar los resultados. Si ya hay algo, la fórmula falla.

Para solucionarlo: limpia las celdas donde deben ir los resultados y luego introduce la fórmula. Si necesitas algo en esas celdas, mueve tu fórmula de XLOOKUP() a otro sitio con espacio libre alrededor.

Conclusión 

Ya sabes usar XLOOKUP() para valores individuales, varias columnas, datos faltantes e incluso búsquedas anidadas entre dos rangos. No es poca cosa.

A partir de ahora, haz de XLOOKUP() tu opción por defecto para cualquier libro nuevo. Es más flexible que VLOOKUP(), más legible que INDEX/MATCH() y te evita envolver cada fórmula en IFERROR().

Si quieres profundizar, también merece la pena aprender INDEX() y MATCH() por separado.