Cómo usar XLOOKUP en Google Sheets (con ejemplos)

Según Exploding Topics, más de 900 millones de personas usan Google Sheets cada mes. Pero ¿cuántas usan XLOOKUP en Google Sheets?

Este tutorial le mostrará cómo utilizarlo para extraer datos de la misma hoja de cálculo o de dos hojas de cálculo diferentes.

También explica el único caso en el que FILTER funciona mejor que XLOOKUP: devolver todas las coincidencias.

En esta guía:

• Qué hace XLOOKUP y sus seis argumentos
• Cómo usar XLOOKUP, paso a paso
• XLOOKUP desde otra hoja
• XLOOKUP entre dos hojas de cálculo
• Ejemplos: búsquedas horizontales, varios criterios, comodines, intervalos con nombre, valores predeterminados, FILTER y la última coincidencia o la más cercana
• XLOOKUP vs. VLOOKUP

¿Qué es XLOOKUP en Google Sheets?

En términos sencillos, XLOOKUP te ayuda a encontrar información específica en tus hojas de cálculo de Google. Tú le dices a XLOOKUP qué debe buscar (por ejemplo, la nota de un alumno) y dónde (en qué columna del libro de notas). A continuación, XLOOKUP encuentra rápidamente la información correspondiente y te la devuelve.

XLOOKUP es una alternativa más flexible a funciones más antiguas como VLOOKUP y HLOOKUP.

Sintaxis y argumentos de XLOOKUP

XLOOKUP tiene seis argumentos. Solo los tres primeros son obligatorios.

=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

Excel llama a esos mismos argumentos lookup_value, lookup_array, return_array e if_not_found, y algunos ejemplos de abajo usan esos nombres.

Cómo usar XLOOKUP en Google Sheets: paso a paso

En esta sección aprenderás a usar XLOOKUP en vertical para encontrar una coincidencia exacta con una sola clave de búsqueda.

1. Abre en Google Sheets la hoja de cálculo que contiene tu tabla.

2. En lugar de escribir la clave de búsqueda directamente en la función, puedes escribirla en una celda y usar después la referencia de esa celda.

3. Escribe la función XLOOKUP y selecciona la celda con la clave de búsqueda como primer parámetro.

4. Como segundo parámetro, selecciona la columna en la que quieres buscar la clave de búsqueda.

5. Como tercer parámetro, selecciona la columna de la que quieres obtener el resultado. Omite los parámetros opcionales para usar los valores predeterminados.

6. Cierra el paréntesis y pulsa "Intro". Ya tienes el resultado.

Cómo usar XLOOKUP desde otra hoja

Supongamos que eres profesor y utilizas una hoja de cálculo de Google para hacer un seguimiento de las calificaciones de tus alumnos. Llamaremos a la hoja de cálculo ’Exámenes de los alumnos“.”

Ahora, la hoja de cálculo tiene dos hojas:

• Uno llamado “Estudiantes” - Tiene los nombres y números de identificación de los estudiantes y
• Uno llamado “Test Scores” - Tiene los números de identificación y las puntuaciones de los estudiantes.

- Ficha 1: Estudiantes

- Hoja 2: Resultados de las pruebas

Ten en cuenta que estas dos hojas están en la misma hoja de cálculo. Más adelante te mostraremos cómo utilizar XLOOKUP en dos hojas de cálculo separadas.

Quiere ver la puntuación de cada alumno junto a su nombre en la hoja “Alumnos”. Elijamos a Juan Pérez. 

1. En la celda bajo “Test Score” para John Doe, utilice esta fórmula XLOOKUP:

Desglosemos esta fórmula:

• Valor de búsqueda (A2): El número de identificación que busca.
• Matriz de búsqueda (‘Test Scores’!A:A): Esta es la columna de la hoja “Test Scores” donde la función XLOOKUP debe encontrar los números de identificación.
• Devuelve Array (‘Test Scores’!B:B): Si encuentra una coincidencia, devuelve el valor correspondiente de la matriz de retorno (la puntuación del alumno).
• Si no se encuentra (“Not Found”): Si no encuentra ninguna coincidencia, muestra “No encontrado”.”

En términos sencillos, esto es lo que dice la fórmula:

2. Pulse “Intro” en el teclado o haga clic en cualquier lugar de la hoja. Aparecerá la partitura.

Lectura recomendada: ¿Cómo utilizar la función XLOOKUP en Excel?

¿Cómo se utiliza la función XLOOKUP en dos hojas de cálculo de Google distintas?

¿Se puede utilizar la función XLOOKUP en dos hojas de cálculo de Google distintas?

Sí. Envuelve el intervalo de búsqueda y el intervalo de resultados en IMPORTRANGE para que XLOOKUP los lea desde el otro archivo. La primera vez que conectas los dos archivos, Google Sheets te pide que permitas el acceso.

¿Cómo funciona?

Utiliza esta fórmula:

Volvamos al ejemplo de nuestro profesor.

Imagina que estás colaborando con otro profesor en un proyecto. Cada uno tiene su propia hoja de Google con los datos de los alumnos.

Tu hoja de Google se llama “Estudiantes” y contiene los nombres de los estudiantes y sus números de identificación.

Por otro lado, la hoja de su colega se llama “Resultados de exámenes” y contiene los números de identificación de los alumnos y sus resultados en un examen reciente.

Desea combinar esta información en su hoja “Alumnos” para ver la puntuación de cada alumno junto a su nombre.

¿Cómo se hace?

1. Abre la hoja de tu colega y copia el ID de la URL. Empieza después de ‘/d/’ y termina antes de la siguiente “/”.”

2. Sustituya los dos “sheet-id” de la fórmula siguiente por el ID que acaba de copiar.

3. Copie el nombre de la hoja de la hoja de cálculo de su colega.

4. Sustituye los dos “Hoja1” de la fórmula por el nombre de la hoja de tu colega, “Resultados de los exámenes”. Así es como quedará tu fórmula final:

5. En la celda bajo "Test Score" de Jane Smith, usa la fórmula XLOOKUP anterior.

6. Pulse “Intro” en el teclado o haga clic en cualquier lugar de la hoja. Aparecerá la partitura.

7. Si quieres obtener la puntuación de Jane Smith en la fila 3, cambia "=XLOOKUP(A2" por "=XLOOKUP(A3".

Fórmula antigua con (A2:

Nueva fórmula con (A3:

Ejemplos de XLOOKUP en Google Sheets

Ahora que sabes usar XLOOKUP para encontrar una coincidencia exacta de un solo valor, es el momento de ver otras formas de usarla. En los ejemplos de abajo aprenderás a usar XLOOKUP para hacer búsquedas horizontales, buscar con varios criterios, devolver varias columnas a la vez, buscar con comodines y usar intervalos con nombre. Los últimos ejemplos muestran un valor predeterminado cuando no hay coincidencias, FILTER para devolver todas las coincidencias y cómo encontrar la última coincidencia o la más cercana.

XLOOKUP horizontal (en lugar de HLOOKUP)

XLOOKUP también busca en una fila, así que puedes usarla en lugar de HLOOKUP. Sigue estos pasos para hacer una búsqueda horizontal.

1. En una celda vacía, escribe la fórmula XLOOKUP y añade la clave de búsqueda como primer parámetro. En este caso, la clave de búsqueda es el encabezado de columna que quieres encontrar.

2. Como segundo parámetro, selecciona la fila en la que quieres buscar la clave de búsqueda. En este caso, los encabezados están en la fila 1.

3. Como tercer parámetro, indica la fila de la que quieres obtener el resultado. Omite los parámetros opcionales para usar los valores predeterminados.

4. Cierra el paréntesis y pulsa "Intro" para obtener el resultado.

XLOOKUP con varios criterios

Para buscar varios valores con XLOOKUP, puedes combinarla con ARRAYFORMULA.

1. En una celda vacía, escribe la función XLOOKUP.

2. Como primer parámetro, selecciona la primera clave de búsqueda, añade un ampersand (&) y selecciona la segunda clave de búsqueda.

3. Como segundo parámetro, escribe la función ARRAYFORMULA y selecciona las columnas en las que quieres buscar las claves de búsqueda, unidas con un ampersand (&).

4. Como tercer parámetro, selecciona la columna de la que quieres que se devuelva el resultado.

5. Cierra el paréntesis y pulsa "Intro" para obtener el resultado.

Devuelve varias columnas con una sola XLOOKUP

Para devolver varios valores de la fila que coincide, como el email, el producto y el importe, puedes usar la función ARRAYFORMULA. XLOOKUP sigue devolviendo solo la primera coincidencia. Para obtener todas las filas que coinciden, usa FILTER, como verás más abajo.

1. En una celda vacía, escribe la función XLOOKUP y añade la clave de búsqueda como primer parámetro.

2. Como segundo parámetro, selecciona la columna en la que quieres buscar la clave de búsqueda.

3. Como tercer parámetro, añade la función ARRAYFORMULA y selecciona las columnas de las que quieres obtener los resultados.

4. Cierra los dos paréntesis y pulsa "Intro" para ver los resultados.

XLOOKUP con comodines

Si solo tienes o recuerdas partes de tu clave de búsqueda, puedes usar caracteres comodín para encontrar una coincidencia.

• * coincide con cero o más caracteres.
• ? coincide exactamente con un carácter.
• ~ anula los caracteres comodín para que puedas buscarlos.

1. Escribe la clave de búsqueda parcial en una celda para usarla como referencia después.

2. En una celda vacía, escribe la función XLOOKUP.

3. Añade el comodín y la clave de búsqueda parcial, como se muestra abajo. El carácter comodín debe ir entre comillas y unirse a la clave de búsqueda parcial con un ampersand (&).

4. Como segundo parámetro, selecciona la columna en la que quieres buscar la coincidencia.

5. Como tercer parámetro, selecciona la columna de la que quieres obtener el resultado.

6. Como buscas con un comodín, tienes que indicar que el quinto parámetro es "2". Sin embargo, no hace falta indicar el cuarto ni el sexto parámetro si quieres los valores predeterminados.

XLOOKUP con intervalos con nombre

Por último, veamos cómo usar intervalos con nombre en XLOOKUP para que el proceso sea aún más fácil.

1. Abre la hoja de cálculo con tu tabla y ve a Datos > Intervalos con nombre.

2. En el panel lateral, haz clic en "Añadir un intervalo".

3. Selecciona la columna a la que quieres poner nombre y escribe el nombre. Haz clic en "Listo".

4. Repite los pasos 2 y 3 hasta que hayas puesto nombre a todas las columnas necesarias.

5. En una celda vacía, escribe la función XLOOKUP y añade la clave de búsqueda.

6. Como segundo parámetro, empieza a escribir el nombre de la columna en la que se busca y selecciónala en el desplegable.

7. Como tercer parámetro, empieza a escribir el nombre de la columna que se devuelve y selecciónala como antes.

8. Cierra el paréntesis y pulsa "Intro" para ver el resultado.

Muestra un valor predeterminado cuando no hay coincidencias

Puedes usar XLOOKUP en Google Sheets para encontrar valores que faltan en un conjunto de datos comparando dos listas (por ejemplo, una lista de productos existentes y otra de productos nuevos).

Veamos un ejemplo.

Eres jefe de ventas y realizas el seguimiento del rendimiento de tus empleados en una hoja de Google. Usted tiene:

• Hoja 1 (Datos de los empleados): Contiene los nombres y números de identificación de los empleados.

• Hoja 2: Contiene los números de identificación de los empleados y sus cifras de ventas.

• Hoja 3: Contiene la información consolidada. Sin embargo, algunos empleados no tienen datos de ventas, como se muestra a continuación.

Tenga en cuenta que estas tres hojas se encuentran en la misma hoja de cálculo.

1. Abra la hoja 3 (“Consolidado” en nuestro ejemplo).
2. En la celda C2 de Consolidated, introduce la siguiente fórmula XLOOKUP:

Esta fórmula lo hará:

1. Busque el ID de Empleado en B2 dentro del rango de IDs de Empleado en la Hoja 2 (Hoja2!A:A).
2. Si se encuentra, devolverá la Cifra de Ventas correspondiente de la Hoja 2 (Hoja2!B:B).
3. Si no se encuentra, devolverá el valor 0, indicando que no hay ventas.

Tenga en cuenta que debe editar la fórmula antes de utilizarla. Por ejemplo, mi hoja se llama “Ventas”, no “Hoja2” y el ID “102” está en la fila B3, no en la B2. Por lo tanto, mi nueva fórmula será

Úsala en la celda y pulsa Intro en el teclado.

Devuelve todas las coincidencias con FILTER

A menudo nos preguntan si XLOOKUP puede devolver varios resultados en las hojas de cálculo de Google.

La mejor manera de responder a esta pregunta es con un ejemplo.

Supongamos que gestionas una pequeña librería y llevas un registro de tu inventario en una hoja de Google. Tu hoja tiene las siguientes columnas:

• Título del libro
• Autor
• ISBN
• Cantidad en stock
• Precio

Quiere encontrar todos los libros que escribió un autor concreto, así como sus ISBN y cantidades en stock.

1. Identifique su valor de búsqueda. En este ejemplo, buscaré el nombre de un autor, ya que es el valor que quiero encontrar: J.K. Rowling.
2. Identificar la matriz de búsqueda, la columna que contiene los nombres de los autores: Autor.
3. Identifique las matrices de retorno, las columnas de las que desea devolver datos:
   • ISBN
   • Cantidad en stock
4. Crea la fórmula XLOOKUP:

Recuerde que debe modificar esta fórmula para adaptarla a su situación.

5. Abra su hoja y utilice su fórmula en una celda vacía.

6. Pulsa "Intro". Como ves, XLOOKUP solo devuelve el primer libro que coincide, aunque la fórmula revisa todas las filas.

¿Puede XLOOKUP devolver todas las filas que coinciden en Google Sheets?

La verdad es que no. XLOOKUP normalmente devuelve un solo o la primera que encuentre. Lamentablemente, no admite de forma nativa la devolución de múltiples coincidencias, como hace FILTER. Por lo tanto, en su forma estándar, XLOOKUP no es adecuado para extraer varias filas.

¿Te has fijado que he mencionado FILTRO? ¿Cómo funciona?

7. Cambia tu fórmula a:

8. Pulsa "Intro". Y listo: FILTER ha extraído datos de varias filas.

Encuentra la última coincidencia o el valor más cercano

Los dos últimos argumentos cambian la forma en que busca XLOOKUP. Estos ejemplos usan la tabla de ventas de la sección paso a paso, donde la columna F contiene el importe y la columna G, el agente de ventas.

Para obtener el importe de la última venta de la lista de Agent2, pon search_mode en -1 para que XLOOKUP busque de abajo arriba:

=XLOOKUP("Agent2", G:G, F:F, "Not found", 0, -1)

Para encontrar el valor más cercano en lugar de uno exacto, pon match_mode en -1 (el siguiente menor) o 1 (el siguiente mayor). Supón que K2:K5 contiene umbrales de ventas y L2:L5 la tasa de comisión de cada uno. Esta fórmula devuelve la tasa del umbral más alto que no supera el importe de F2:

=XLOOKUP(F2, K2:K5, L2:L5, , -1)

Ninguna de las dos fórmulas necesita datos ordenados. Solo los modos de búsqueda binaria, 2 y -2, los necesitan.

XLOOKUP vs. VLOOKUP: ¿cuál es la diferencia?

VLOOKUP busca verticalmente, de izquierda a derecha, dentro de una tabla (de ahí la “V”); el valor de búsqueda debe estar en la primera columna. XLOOKUP, sin embargo, es más flexible. Puede buscar vertical y horizontalmente; no es necesario especificar un índice de columna.

Además, VLOOKUP solo puede devolver valores a la derecha de la columna de búsqueda, mientras que XLOOKUP puede devolver valores de cualquiera de los dos lados.

He aquí la diferencia en términos de sintaxis:

VLOOKUP

XLOOKUP

Conclusión

Como has visto, la función XLOOKUP de Google Sheets es más potente que VLOOKUP y HLOOKUP, y más fácil de usar que alternativas como INDEX & MATCH. Has repasado la función y su sintaxis, y ya sabes usar XLOOKUP para hacer búsquedas verticales y horizontales de una sola coincidencia exacta. También sabes extraer datos de otra hoja o de otro archivo, buscar con varios criterios o comodines y devolver varias columnas a la vez. Cuando una búsqueda no encuentra nada, missing_value te da un valor predeterminado, y cuando necesitas todas las coincidencias, FILTER hace lo que XLOOKUP no puede.

Si repites las mismas búsquedas en varios archivos cada semana, Sheetgo, una plataforma de automatización de datos con IA, puede conectar esas hojas de cálculo y mantener los datos combinados actualizados según una programación, sin fórmulas IMPORTRANGE que mantener.