Según Temas explosivos, más de 900 millones de personas utilizan Google Sheets mensualmente. Pero, ¿cuántos utilizan 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 hablaremos de la mejor y más sencilla alternativa a XLOOKUP.
Siga leyendo.
¿Qué es XLOOKUP?
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 los antiguos funciones como VLOOKUP y HLOOKUP.
¿Cómo se utiliza XLOOKUP en Google Sheets?
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.
- En la celda bajo “Test Score” para John Doe, utilice esta fórmula XLOOKUP:
=XLOOKUP(A2, ‘Resultados’!A:A, ‘Resultados’!B:B, “No encontrado”)
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:
Busca el número de identificación de este alumno en la hoja de resultados de los exámenes. Si lo encuentras, dime su puntuación. Si no lo encuentras, di ‘No encontrado’.
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í, debes asegurarte de que ambas hojas están abiertas en tu navegador y de que haces referencia a la otra hoja correctamente en tu fórmula XLOOKUP.
¿Cómo funciona?
Utiliza esta fórmula:
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/sheet-id”, “Hoja1!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/sheet-id”, “Hoja1!B:B”), “No encontrado”)
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?
- Abre la hoja de tu colega y copia el ID de la URL. Empieza después de ‘/d/’ y termina antes de la siguiente “/”.”
- Sustituya los dos “sheet-id” de la fórmula siguiente por el ID que acaba de copiar.
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0“, “Hoja1!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0“, “Hoja1!B:B”), “No encontrado”)
- 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:
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados de las pruebas!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados de las pruebas!B:B”), “No encontrado”)
5. En la celda bajo “Test Score” para Jane Smith, utilice 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 desea obtener Puntuación de Jane Smith en la fila 3, cambiar “=XLOOKUP(A2” a “=XLOOKUP(A3.”
Fórmula antigua con (A2:
=XLOOKUP(A2, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados!B:B”), “No encontrado”)
Nueva fórmula con (A3:
=XLOOKUP(A3, IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados de las pruebas!A:A”), IMPORTRANGE(“https://docs.google.com/spreadsheets/d/15MmbjgdGPJ8VwJvYU4BY6GpDDqm59QEut-kQ65Z8er0”, “Resultados de las pruebas!B:B”), “No encontrado”)
Ejemplos de XLOOKUP en acción
Ejemplo 1: Valor omitido
Puede utilizar 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 una lista 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.
- Abra la hoja 3 (“Consolidado” en nuestro ejemplo).
- En la celda C2 de Consolidated, introduce la siguiente fórmula XLOOKUP:
=XLOOKUP(B2, Hoja2!A:A, Hoja2!B:B, 0)
Esta fórmula lo hará:
- Busque el ID de Empleado en B2 dentro del rango de IDs de Empleado en la Hoja 2 (Hoja2!A:A).
- Si se encuentra, devolverá la Cifra de Ventas correspondiente de la Hoja 2 (Hoja2!B:B).
- 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á
=XLOOKUP(B3, Ventas!A:A, Ventas!B:B, 0)
Use en la celda y pulse Intro en su teclado.
Ejemplo 2: Devolver varios resultados
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.
- 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.
- Identificar la matriz de búsqueda, la columna que contiene los nombres de los autores: Autor.
- Identifique las matrices de retorno, las columnas de las que desea devolver datos:
- ISBN
- Cantidad en stock
- Crea la fórmula XLOOKUP:
=ARRAYFORMULA(XLOOKUP(valor_de_consulta, matriz_de_consulta, {matriz_de_de_devolución1, matriz_de_devolución2}, “”, 0))
Recuerde que debe modificar esta fórmula para adaptarla a su situación.
=ARRAYFORMULA(XLOOKUP(“J.K. Rowling”, B2:B100, {C2:C100, D2:D100}, “”, 0))
5. Abra su hoja y utilice su fórmula en una celda vacía.
6. Pulse “Intro”. Como puedes ver, XLOOKUP sólo devuelve un valor, aunque nuestra fórmula pide explícitamente que se comprueben todas las filas.
7. Sin embargo, todavía hay una pequeña forma de recuperar todos los datos. Haga clic en los primeros datos devueltos.
Y arrástralo hacia abajo. Aparecerán más datos, aunque suelen repetirse.
¿Se puede utilizar XLOOKUP para devolver varios resultados en Hojas de cálculo de Google?
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?
- Cambia tu fórmula a:
=ARRAYFORMULA(FILTRO({C2:C100, D2:D100}, B2:B100 = “J.K. Rowling”))
9. Pulsa “Enter”. Y voilà, el FILTRO extrajo los datos de varias filas.
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.
He aquí la diferencia en términos de sintaxis:
VLOOKUP
=VLOOKUP(valor_buscado, matriz_tabla, número_índice_col, [rango_buscado])
XLOOKUP
=XLOOKUP(valor_de_busqueda, matriz_de_busqueda, matriz_de_retorno, [si_no_se_encuentra], [modo_coincidencia], [modo_busqueda])
Uso de XLOOKUP en Hojas de cálculo de Google
Acabamos de ver cómo utilizar XLOOKUP en Google Sheets a través de ejemplos. También compartimos fórmulas en bruto y explicamos cómo utilizarlas.
English 
Español 
Português do Brasil 
Français