Ver video - Función XLOOKUP de Excel (10 ejemplos de XLOOKUP)
Sobresalir Función XLOOKUP finalmente ha llegado.
Si ha estado utilizando BUSCARV o INDICE / COINCIDIR, estoy seguro de que le encantará la flexibilidad que ofrece la función BUSCAR X.
En este tutorial, cubriré todo lo que hay que saber sobre la función XLOOKUP y algunos ejemplos que le ayudarán a saber cómo utilizarla mejor.
¡Entonces empecemos!
¿Qué es XLOOKUP?
XLOOKUP es una nueva función de Office 365 y es una versión nueva y mejorada de la función VLOOKUP / HLOOKUP.
Hace todo lo que solía hacer BUSCARV, y mucho más.
XLOOKUP es una función que le permite buscar rápidamente un valor en un conjunto de datos (vertical u horizontal) y devolver el valor correspondiente en alguna otra fila / columna.
Por ejemplo, si tiene los puntajes de los estudiantes en un examen, puede usar XLOOKUP para verificar rápidamente cuánto obtuvo un estudiante usando el nombre del estudiante.
El poder de esta función se hará aún más claro a medida que profundice en algunos Ejemplos de XLOOKUP más adelante en este tutorial.
Pero antes de entrar en los ejemplos, hay una gran pregunta: ¿cómo obtengo acceso a XLOOKUP?
¿Cómo acceder a XLOOKUP?
A partir de ahora, XLOOKUP solo está disponible para los usuarios de Office 365.
Por lo tanto, si usa versiones anteriores de Excel (2010/2013/2016/2019), no podrá usar esta función.
Tampoco estoy seguro de si esto alguna vez se lanzará para versiones anteriores o no (tal vez Microsoft pueda crear un complemento de la forma en que lo hizo para Power Query). Pero a partir de ahora, solo puede usarlo si está en Office 365.
Haga clic aquí para actualizar a Office 365
En caso de que ya esté en Office 365 (edición Home, Personal o University) y no tenga acceso a él, puede ir a la pestaña Archivo y luego hacer clic en Cuenta.
Habría un programa de Office Insider y puede hacer clic y unirse al programa de Office Insider. Esto le dará acceso a la función XLOOKUP.
Espero que XLOOKUP esté disponible pronto en todas las versiones de Office 365.
Nota: XLOOKUP también está disponible para Office 365 para Mac y Excel para la Web (Excel en línea)Sintaxis de la función XLOOKUP
A continuación se muestra la sintaxis de la función BUSCAR X:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Si ha utilizado BUSCARV, notará que la sintaxis es bastante similar, con algunas características adicionales asombrosas, por supuesto.
No se preocupe si la sintaxis y el argumento parecen demasiado. Los cubro con algunos ejemplos fáciles de XLOOKUP más adelante en este tutorial que lo aclararán como el cristal.La función XLOOKUP puede contar con 6 argumentos (3 obligatorios y 3 opcionales):
- valor de búsqueda - el valor que busca
- lookup_array - la matriz en la que busca el valor de búsqueda
- return_array - la matriz de la que desea obtener y devolver el valor (correspondiente a la posición donde se encuentra el valor de búsqueda)
- [if_not_found] - el valor que se devolverá en caso de que no se encuentre el valor de búsqueda. En caso de que no especifique este argumento, se devolverá un error # N / A
- [match_mode] - Aquí puede especificar el tipo de coincidencia que desea:
- 0 - Coincidencia exacta, donde lookup_value debe coincidir exactamente con el valor en lookup_array. Esta es la opción por defecto.
- -1 - Busca la coincidencia exacta, pero si la encuentra, devuelve el siguiente elemento / valor más pequeño.
- 1 - Busca la coincidencia exacta, pero si la encuentra, devuelve el siguiente elemento / valor más grande.
- 2 - Para hacer una coincidencia parcial usando comodines (* o ~)
- [modo de búsqueda] - Aquí especifica cómo la función XLOOKUP debe buscar el lookup_array
- 1 - Esta es la opción predeterminada donde la función comienza a buscar el lookup_value desde la parte superior (primer elemento) hasta la parte inferior (último elemento) en el lookup_array
- -1 - Realiza la búsqueda de abajo hacia arriba. Útil cuando desea encontrar el último valor coincidente en el lookup_array
- 2 - Realiza una búsqueda binaria donde los datos deben ordenarse en orden ascendente. Si no está ordenado, esto puede dar error o resultados incorrectos.
- -2 - Realiza una búsqueda binaria donde los datos deben ordenarse en orden descendente. Si no está ordenado, esto puede dar error o resultados incorrectos.
Ejemplos de funciones XLOOKUP
Ahora vayamos a la parte interesante: algunos ejemplos prácticos de XLOOKUP.
Estos ejemplos lo ayudarán a comprender mejor cómo funciona XLOOKUP, en qué se diferencia de VLOOKUP e INDEX / MATCH y algunas mejoras y limitaciones de esta función.
Haga clic aquí para descargar el archivo de ejemplo y siga
Ejemplo 1: obtener un valor de búsqueda
Suponga que tiene el siguiente conjunto de datos y desea obtener la puntuación matemática de Greg (el valor de búsqueda).
A continuación se muestra la fórmula que hace esto:
= BUSCAR X (F2, A2: A15, B2: B15)
En la fórmula anterior, acabo de usar los argumentos obligatorios donde busca el nombre (de arriba a abajo), encuentra una coincidencia exacta y devuelve el valor correspondiente de B2: B15.
Una diferencia obvia que tiene la función XLOOKUP y VLOOKUP es la forma en que manejan la matriz de búsqueda. En BUSCARV, tiene la matriz completa donde el valor de búsqueda está en la columna más a la izquierda y luego especifica el número de columna desde donde desea obtener el resultado. XLOOKUP, por otro lado, le permite elegir lookup_array y return_array por separadoUn beneficio instantáneo de tener lookup_array y return_array como argumentos separados significa que ahora puede mira a la izquierda. VLOOKUP tenía esta limitación en la que solo puede buscar y encontrar un valor que esté a la derecha. Pero con XLOOKUP, esa limitación se ha ido.
Aquí hay un ejemplo. Tengo el mismo conjunto de datos, donde el nombre está a la derecha y return_range está a la izquierda.
A continuación se muestra la fórmula que puedo usar para obtener el puntaje de Greg en matemáticas (lo que significa mirar a la izquierda de lookup_value)
= BUSCAR X (F2, D2: D15, A2: A15)
XLOOKUP resuelve otro problema importante: en caso de que inserte una nueva columna o mueva columnas, los datos resultantes seguirían siendo correctos. VLOOKUP probablemente se rompería o daría un resultado incorrecto en tales casos, ya que la mayoría de las veces el valor del índice de la columna está codificado.
Ejemplo 2: buscar y obtener un registro completo
Tomemos los mismos datos como ejemplo.
En este caso, no quiero simplemente buscar la puntuación de Greg en Matemáticas. Quiero sacar las puntuaciones en todas las asignaturas.
En este caso, puedo usar la siguiente fórmula:
= BUSCAR X (F2, A2: A15, B2: D15)
La fórmula anterior usa un rango return_array que es más que una columna (B2: D15). Entonces, cuando el valor de búsqueda se encuentra en A2: A15, la fórmula devuelve la fila completa de return_array.
Además, no puede eliminar solo las celdas que forman parte de la matriz que se completaron automáticamente. En este ejemplo, no puede eliminar H2 o I2. Si lo intenta, no pasará nada. Si selecciona estas celdas, la fórmula en la barra de fórmulas aparecerá atenuada (lo que indica que no se puede cambiar)
Puede eliminar la fórmula en la celda G2 (donde la ingresamos originalmente), eliminará todo el resultado.
Esta es una mejora útil, ya que anteriormente con BUSCARV, tendrá que especificar el número de columna por separado para cada fórmula.
Ejemplo 3: búsqueda bidireccional utilizando XLOOKUP (búsqueda horizontal y vertical)
A continuación se muestra un conjunto de datos donde quiero saber el puntaje de Greg en Matemáticas (el tema en la celda G2).
Esto se puede hacer usando una búsqueda bidireccional donde busco el nombre en la columna A y el nombre del tema en la fila 1. El beneficio de esta búsqueda bidireccional es que el resultado es independiente del nombre del estudiante del nombre del tema. Si cambio el nombre de la asignatura a Química, esta fórmula de XLOOKUP bidireccional seguiría funcionando y me daría el resultado correcto.
A continuación se muestra la fórmula que realizará la búsqueda bidireccional y dará el resultado correcto:
= XLOOKUP (G1, B1: D1, XLOOKUP (F2, A2: A15, B2: D15))
Esta fórmula usa un XLOOKUP anidado, donde primero lo uso para buscar todas las marcas del estudiante en la celda F2.
Entonces, el resultado de XLOOKUP (F2, A2: A15, B2: D15) es {21,94,81}, que en este caso es una matriz de calificaciones obtenidas por Greg.
Esto luego se usa nuevamente dentro de la fórmula XLOOKUP externa como la matriz de retorno. En la fórmula externa de XLOOKUP, busco el nombre del sujeto (que está en la celda G1) y la matriz de búsqueda es B1: D1.
Si el nombre del sujeto es Matemáticas, esta fórmula XLOOKUP externa obtiene el primer valor de la matriz de retorno, que es {21,94,81} en este ejemplo.
Esto hace lo mismo que, hasta ahora, se lograba usando el combo INDICE y MATCH.
Haga clic aquí para descargar el archivo de ejemplo y siga
Ejemplo 4: Cuando no se encuentra el valor de búsqueda (manejo de errores)
El manejo de errores ahora se ha agregado a la fórmula XLOOKUP.
El cuarto argumento de la función XLOOKUP es [if_not_found], donde puede especificar lo que desea en caso de que no se pueda encontrar la búsqueda.
Suponga que tiene el conjunto de datos como se muestra a continuación donde desea obtener el puntaje de matemáticas en caso de que coincida, y en caso de que no se encuentre el nombre, desea regresar: "No apareció"
La siguiente fórmula hará esto:
= XLOOKUP (F2, A2: A15, B2: B15, "No apareció")
En este caso, he codificado de forma rígida lo que quiero obtener en caso de que no haya coincidencia. También puede usar una referencia de celda a una celda o una fórmula.
Ejemplo 5: XLOOKUP anidado (búsqueda en varios rangos)
La genialidad de tener el argumento [if_not_found] es que te permite usar fórmula XLOOKUP anidada.
Por ejemplo, suponga que tiene dos listas separadas como se muestra a continuación. Si bien tengo estas dos tablas en la misma hoja, puede tenerlas en hojas separadas o incluso en libros de trabajo.
A continuación se muestra la fórmula XLOOKUP anidada que buscará el nombre en ambas tablas y devolverá el valor correspondiente de la columna especificada.
= XLOOKUP (A12, A2: A8, B2: B8, XLOOKUP (A12, F2: F8, G2: G8))
En la fórmula anterior, he usado el argumento [if_not_found] para usar otra fórmula XLOOKUP. Esto le permite agregar el segundo XLOOKUP en la misma fórmula y escanear dos tablas con una sola fórmula.
No estoy seguro de cuántas XLOOKUP anidadas puede usar en una fórmula. Intenté hasta las 10 y funcionó, luego me di por vencido 🙂
Ejemplo 6: Encuentre el último valor coincidente
Este era muy necesario y XLOOKUP lo hizo posible. Ahora no es necesario que busque formas complicadas de obtener el último valor coincidente de un rango.
Suponga que tiene el conjunto de datos como se muestra a continuación y desea verificar cuándo fue la última persona contratada en cada departamento y cuál fue la fecha de contratación.
La siguiente fórmula buscará el último valor para cada departamento y dará el nombre de la última contratación:
= BUSCARX (F1, $ B $ 2: $ B $ 15, $ A $ 2: $ A $ 15 ,,, - 1)
Y la siguiente fórmula dará la fecha de contratación de la última contratación para cada departamento:
= BUSCARX (F1, $ B $ 2: $ B $ 15, $ C $ 2: $ C $ 15 ,,, - 1)
Dado que XLOOKUP tiene una función incorporada para especificar la dirección de la búsqueda (del primero al último o del último al primero), esto se hace con una fórmula simple. Con datos verticales, VLOOKUP e INDEX / MATCH siempre miran de arriba a abajo, pero con XLOOKUP y también pueden especificar la dirección de abajo hacia arriba.
Ejemplo 7: Coincidencia aproximada con XLOOKUP (Buscar tipo impositivo)
Otra mejora notable con XLOOKUP es que ahora hay cuatro modos de coincidencia (VLOOKUP tiene 2 y MATCH tiene 3).
Puede especificar cualquiera de los cuatro argumentos para decidir cómo debe coincidir el valor de búsqueda:
- 0 - Coincidencia exacta, donde lookup_value debe coincidir exactamente con el valor en lookup_array. Esta es la opción por defecto.
- -1 - Busca la coincidencia exacta, pero si la encuentra, devuelve el siguiente elemento / valor más pequeño.
- 1 - Busca la coincidencia exacta, pero si la encuentra, devuelve el siguiente elemento / valor más grande.
- 2 - Para hacer una coincidencia parcial usando comodines (* o ~)
A continuación, tengo un conjunto de datos donde quiero encontrar la comisión de cada persona, y la comisión debe calcularse usando la tabla de la derecha.
A continuación se muestra la fórmula que hará esto:
= BUSCAR X (B2, $ E $ 2: $ E $ 6, $ F $ 2: $ F $ 6,0, -1) * B2
Esto simplemente usa el valor de ventas como búsqueda y examina la tabla de búsqueda de la derecha. En esta fórmula, he usado -1 como el quinto argumento ([match_mode]), lo que significa que buscará una coincidencia exacta, y cuando no encuentre una, devolverá el valor un poco más pequeño que el valor de búsqueda .
Y como dije, no necesita preocuparse si sus datos están ordenados o no.
Haga clic aquí para descargar el archivo de ejemplo y siga
Ejemplo 8: búsqueda horizontal
XLOOKUP puede realizar búsquedas tanto verticales como horizontales.
A continuación, tengo un conjunto de datos donde tengo los nombres de los estudiantes y sus puntajes en filas, y quiero buscar el puntaje para el nombre en la celda B7.
La siguiente fórmula hará esto:
= BUSCAR X (B7, B1: O1, B2: O2)
Esto no es más que una búsqueda simple (similar a lo que vimos en el Ejemplo 1), pero horizontal.
Todos los ejemplos que cubro sobre la búsqueda vertical también se pueden hacer con una búsqueda horizontal usando XLOOKUP (adiós a VLOOKUP y HLOOKUP).
Ejemplo 9: búsqueda condicional (usando XLOOKUP con otras fórmulas)
Este es un ejemplo un poco avanzado y también muestra el poder de XLOOKUP cuando necesita hacer búsquedas complejas.
A continuación se muestra un conjunto de datos donde tengo los nombres de los estudiantes y sus puntajes, y quiero saber el nombre del estudiante que obtuvo el puntaje máximo en cada materia y el recuento de estudiantes que obtuvieron más de 80 puntajes en cada materia.
A continuación se muestra la fórmula que dará el nombre del alumno con las calificaciones más altas en cada asignatura:
= XLOOKUP (MAX (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)), XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), $ A $ 2: $ A $ 15)
Dado que XLOOKUP se puede usar para devolver una matriz completa, lo he usado para obtener primero todas las marcas para el tema requerido.
Por ejemplo, para matemáticas, cuando uso XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), me da todos los puntajes en matemáticas. Luego puedo usar la función MAX para encontrar la puntuación máxima en este rango.
Esta puntuación máxima se convierte en mi valor de búsqueda, y el rango de búsqueda sería la matriz devuelta por XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15)
Utilizo esto dentro de otra fórmula de XLOOKUP para buscar el nombre del estudiante que obtuvo las calificaciones máximas.
Y para contar el número de estudiantes que han obtenido más de 80, utilice la siguiente fórmula:
= CONTAR.SI (XLOOKUP (G1, $ B $ 1: $ D $ 1, $ B $ 2: $ D $ 15), "> 80")
Éste simplemente usa la fórmula XLOOKUP para obtener un rango de todos los valores para el tema dado. Luego lo envuelve en la función CONTAR.SI para obtener el número de puntajes que son más de 80.
Ejemplo 10: uso de comodines en XLOOKUP
Al igual que puede usar caracteres comodín en BUSCARV y COINCIDIR, también puede hacerlo con BUSCARX.
Pero hay una diferencia.
En XLOOKUP, debe especificar que está utilizando caracteres comodín (en el quinto argumento). Si no especifica esto, XLOOKUP le dará un error.
A continuación se muestra un conjunto de datos donde tengo los nombres de las empresas y su capitalización de mercado.
Quiero buscar el nombre de una empresa en la columna D y obtener la capitalización de mercado de la tabla de la izquierda. Y dado que los nombres en la Columna D no son coincidencias exactas, tendré que usar caracteres comodín.
A continuación se muestra la fórmula que hará esto:
= XLOOKUP ("*" & D2 & "*", $ A $ 2: $ A $ 11, $ B $ 2: $ B $ 11,, 2)
En la fórmula anterior, he usado un carácter comodín de asterisco (*) antes como después de D2 (debe estar entre comillas dobles y unido con D2 usando ampersand).
Esto le dice a la fórmula que busque en todas las celdas, y si contiene la palabra en la celda D2 (que es Apple), considérela una coincidencia exacta. No importa cuántos y qué caracteres haya antes y después del texto en la celda D2.
Y para asegurarse de que XLOOKUP acepta caracteres comodín, el quinto argumento se ha establecido en 2 (coincidencia de caracteres comodín).
Ejemplo 11: Encuentre el último valor en la columna
Dado que XLOOKUP le permite buscar de abajo hacia arriba, puede encontrar fácilmente el último valor en una lista, así como obtener el valor correspondiente de una columna.
Suponga que tiene un conjunto de datos como se muestra a continuación y desea saber cuál es la última empresa y cuál es la capitalización de mercado de esta última empresa.
La siguiente fórmula le dará el nombre de la última empresa:
= XLOOKUP ("*", A2: A11, A2: A11`` 2, -1)
Y la siguiente fórmula dará la capitalización de mercado de la última empresa de la lista:
= XLOOKUP ("*", A2: A11, B2: B11`` 2, -1)
Estas fórmulas utilizan nuevamente caracteres comodín. En estos, he usado asterisco (*) como valor de búsqueda, lo que significa que esto consideraría la primera celda que encuentra como una coincidencia exacta (ya que el asterisco podría ser cualquier carácter y cualquier número de caracteres).
Y dado que la dirección es de abajo hacia arriba (para los datos dispuestos verticalmente), devolverá el último valor de la lista.
Y la segunda fórmula desde entonces usa un return_range separado para obtener la capitalización de mercado del apellido en la lista.
Haga clic aquí para descargar el archivo de ejemplo y siga
¿Qué pasa si no tiene XLOOKUP?
Dado que XLOOKUP probablemente solo estará disponible para los usuarios de Office 365, una forma de obtenerlo es actualizar a Office 365.
Si ya tiene Office 365 Home, Personal o University Edition, ya tiene acceso a XLOOKUP. Todo lo que necesita hacer es unirse al programa Office Insider.
Para hacer esto, vaya a la pestaña Archivo, haga clic en Cuenta y luego haga clic en la opción de información privilegiada de Office. Habría una opción para unirse al programa de información privilegiada.
En caso de que tenga otras suscripciones de Office 365 (como Enterprise), estoy seguro de que XLOOKUP y otras características increíbles (como matrices dinámicas, fórmulas como SORT y FILTER) estarán disponibles pronto.
En caso de que esté usando Excel 2010/2013/2016/2019, no tendrá XLOOKUP y tendrá que continuar usando el combo VLOOKUP, HLOOKUP e INDEX / MATCH para obtener lo mejor de las fórmulas de búsqueda.
Compatibilidad con versiones anteriores de XLOOKUP
Esto es algo con lo que debe tener cuidado: XLOOKUP es NO compatible con versiones anteriores.
Esto significa que si crea un archivo y usa la fórmula XLOOKUP, y luego lo abre en una versión que no tiene XLOOKUP, mostrará errores.
Dado que XLOOKUP es un gran paso adelante en la dirección correcta, creo que esta se convertirá en la fórmula de búsqueda predeterminada, pero seguramente pasarán algunos años antes de que se adopte ampliamente. Después de todo, todavía veo a algunas personas usando Excel 2003.
Estos son 11 ejemplos de XLOOKUP que pueden ayudarlo a realizar todas las tareas de búsqueda y referencia más rápido y también facilitar su uso.
¡Espero que hayas encontrado útil este tutorial!
