La nueva función XLOOKUP se implementará en Office 365 a partir de noviembre de 2019. Joe McDaid del equipo de Excel diseñó XLOOKUP para unificar a las personas que usan VLOOKUP y las personas que usan INDEX / MATCH. Esta sección discutirá los 12 beneficios de XLOOKUP:
- La coincidencia exacta es la predeterminada.
- El tercer argumento basado en números enteros de BUSCARV es ahora una referencia adecuada.
- IFNA está integrado para manejar valores perdidos.
- XLOOKUP no tiene problemas para ir hacia la izquierda.
- Encuentre la siguiente coincidencia más pequeña o más grande sin ordenar la tabla.
- XLOOKUP puede hacer HLOOKUP.
- Encuentra la última coincidencia buscando desde abajo.
- Los comodines están "desactivados" de forma predeterminada, pero puede volver a activarlos.
- Devuelve los 12 meses en una sola fórmula.
- Puede devolver una referencia de celda si XLOOKUP está al lado de dos puntos como XLOOKUP (); XLOOKUP ()
- Puede hacer una coincidencia bidireccional como INDEX (, MATCH, MATCH).
- Puede sumar todas las búsquedas en una sola fórmula como podría hacer BÚSQUEDA.
Aquí está la sintaxis: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).
Beneficio 1 de XLOOKUP: Coincidencia exacta por defecto
El 99% de mis fórmulas VLOOKUP terminan en FALSE o 0 para indicar una coincidencia exacta. Si siempre usa la versión de coincidencia exacta de BUSCARV, puede comenzar a dejar el modo de coincidencia fuera de su función BUSCAR X.
En la siguiente figura, busca W25-6 desde la celda A4. Quiere buscar ese elemento en L8: L35. Cuando se encuentra, desea el precio correspondiente de la columna N. No es necesario especificar False como match_mode porque XLOOKUP tiene como valor predeterminado una coincidencia exacta.
Beneficio 2 de XLOOKUP: el Results_Array es una referencia en lugar de un entero
Piense en la fórmula VLOOKUP que usaría antes de XLOOKUP. El tercer argumento habría sido un 3 para indicar que deseaba devolver la tercera columna. Siempre existía el peligro de que un compañero de trabajo despistado hubiera insertado (o eliminado) una columna en su tabla. Con una columna adicional en la tabla, VLOOKUP que había estado devolviendo un precio comenzaría a devolver una descripción. Debido a que XLOOKUP apuntaba a una referencia de celda, la fórmula se reescribe para seguir apuntando al precio que ahora está en la columna O.
Beneficio 3 de XLOOKUP: IFNA está integrado como argumento opcional
El temido error # N / A se devuelve cuando su valor de búsqueda no se encuentra en la tabla. En el pasado, para reemplazar # N / A con otra cosa, tendría que usar IFERROR o IFNA envuelto alrededor de VLOOKUP.
Gracias a una sugerencia de Rico en mi canal de YouTube, el equipo de Excel incorporó un cuarto argumento opcional para if_not_found. Si desea reemplazar esos errores # N / A con cero, simplemente agregue 0 como el cuarto argumento. O puede utilizar algún texto, como "Valor no encontrado".
Beneficio 4 de XLOOKUP: No hay problema para mirar a la izquierda del campo clave
VLOOKUP no puede mirar hacia la izquierda del campo clave sin recurrir a VLOOKUP (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Con XLOOKUP, no hay problema para tener el Results_array a la izquierda de Lookup_array.
Beneficio 5 de XLOOKUP: siguiente coincidencia más pequeña o siguiente más grande sin clasificar
VLOOKUP tenía una opción para buscar la coincidencia exacta o simplemente un valor menor. Puede dejar el cuarto argumento fuera de BUSCARV o cambiar Falso a Verdadero. Para que esto funcione, la tabla de búsqueda debe ordenarse en secuencia ascendente.
Pero BUSCARV no tenía la capacidad de devolver la coincidencia exacta o el siguiente elemento más grande. Para eso, tenía que cambiar al uso de MATCH con un -1 como match_mode y tenía que tener cuidado de que la tabla de búsqueda se ordenara de forma descendente.
El quinto argumento opcional de XLOOKUP match_mode puede buscar solo la coincidencia exacta, igual o simplemente menor, igual o simplemente mayor. Tenga en cuenta que los valores en XLOOKUP tienen más sentido que en MATCH:
- -1 encuentra el valor igual o menor
- 0 encuentra una coincidencia exacta
- 1 encuentra el valor igual o simplemente mayor.
Pero, la parte más sorprendente: la tabla de búsqueda no tiene que ser ordenada y cualquier match_mode funcionará.
A continuación, un match_mode de -1 encuentra el siguiente elemento más pequeño.
Aquí, un match_mode de 1, encuentra qué vehículo se necesita dependiendo del número de personas en el grupo. Tenga en cuenta que la tabla de búsqueda no está ordenada por pasajeros y que el nombre del vehículo está a la izquierda de la clave.
La mesa dice:
- El autobús tiene capacidad para 64 personas
- Coche tiene capacidad para 4 personas
- La motocicleta tiene capacidad para 1 persona
- Tour Van tiene capacidad para 12 personas
- Furgoneta para 6 personas.
Como beneficio adicional, los datos están ordenados por Vehículo (en la solución anterior, usando MATCH, la tabla tendría que ordenarse de forma descendente por Capacidad. Además: el Vehículo está a la izquierda de Capacidad.
Beneficio 6 de XLOOKUP: XLOOKUP de lado reemplaza HLOOKUP
Lookup_array y results_array pueden ser horizontales con XLOOKUP, lo que simplifica la sustitución de HLOOKUP.
Beneficio 7 de XLOOKUP: busque desde la parte inferior la última coincidencia
Tengo un video antiguo en YouTube que responde a una pregunta de una granja de caballos británica. Tenían una flota de vehículos. Cada vez que un vehículo entraba por combustible o servicio, registraban el vehículo, la fecha y el kilometraje en una hoja de cálculo. Querían encontrar el último kilometraje conocido de cada vehículo. Si bien los MAXIFS de la era de Excel-2017 podrían resolver esto hoy, la solución hace muchos años era una fórmula arcana que usaba BUSCAR e involucraba división por cero.
Hoy, el sexto argumento opcional de XLOOKUP le permite especificar que la búsqueda debe comenzar desde la parte inferior del conjunto de datos.
Nota
Si bien esta es una gran mejora, solo le permite encontrar la primera o la última coincidencia. Algunas personas esperaban que esto le permitiera encontrar la segunda o tercera coincidencia, pero esa no es la intención del argumento search_mode.
Precaución
La figura anterior muestra que existen modos de búsqueda que utilizan la antigua búsqueda binaria. Joe McDaid desaconseja su uso. Primero, el algoritmo de búsqueda mejorado de 2018 es lo suficientemente rápido como para que no haya un beneficio de velocidad significativo. En segundo lugar, corre el riesgo de que un compañero de trabajo despistado clasifique la tabla de búsqueda e introduzca respuestas incorrectas.
Beneficio 8 de XLOOKUP: los comodines están "desactivados" de forma predeterminada
La mayoría de la gente no se dio cuenta de que BUSCARV trata el asterisco, el signo de interrogación y la tilde como caracteres comodín como se describe en "# 51 Usar un comodín en BUSCARV" en la página 143. Con BUSCARX, los comodines están desactivados de forma predeterminada. Si desea que XLOOKUP trate estos caracteres como comodines, use 2 como Match_Mode.
Beneficio 9 de XLOOKUP: ¡Devuelva los 12 meses en una sola fórmula!
Este es realmente un beneficio de Dynamic Arrays, pero es mi razón favorita para amar XLOOKUP. Cuando tenga que devolver los 12 meses en una búsqueda, una sola fórmula ingresada en B6 con un return_array rectangular devolverá múltiples resultados. Esos resultados se extenderán a las celdas adyacentes.
En la siguiente figura, una sola fórmula ingresada en B7 devuelve las 12 respuestas que se muestran en B7: M7.
Beneficio 10 de XLOOKUP: puede devolver una referencia de celda si está adyacente a dos puntos
Este es complejo pero hermoso. En el pasado, había siete funciones que cambiaban de devolver un valor de celda a devolver una referencia de celda si la función tocaba dos puntos. Para ver un ejemplo, consulte Use A2: INDEX () como OFFSET no volátil. XLOOKUP es la función de ocho para ofrecer este comportamiento, uniendo CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET y SWITCH.
Considere la siguiente figura. Alguien seleccione Cherry en E4 y Fig en E5. Quieres una fórmula que sume todo, desde B6 hasta B9.
En la figura anterior, puede ver que una XLOOKUP de E4 devolverá el 15 de la celda B6. Una XLOOKUP de E5 devolverá el 30 de B9. Sin embargo, si toma las dos funciones XLOOKUP de las celdas D9 y D10 y las junta con dos puntos en el medio, el comportamiento de XLOOKUP cambia. En lugar de devolver 15, el primer XLOOKUP devuelve la dirección de celda B6.
Para probar esto, seleccioné D7 y uso Fórmulas, Evaluar fórmula. Después de presionar Evaluar dos veces, la siguiente parte a calcular es XLOOKUP ("Cherry", A4: A29, B4: B29), como se muestra aquí.
Presione Evaluar nuevamente y sorprendentemente, la fórmula XLOOKUP devuelve $ B $ 6 en lugar de los 15 almacenados en B6. Esto sucede porque hay dos puntos inmediatamente después de esta fórmula de XLOOKUP.
Presione Evaluar dos veces más y la fórmula intermedia será = SUMA (B6: B9).
Este es un comportamiento asombroso que la mayoría de la gente no conoce. El MVP de Excel, Charles Williams, me dice que se puede activar con cualquiera de estos tres operadores junto a XLOOKUP:
- Colon
- Espacio (operador de intersección)
- Coma (operador de la Unión)
Beneficio 11 de XLOOKUP: coincidencia bidireccional como INDICE (, MATCH, MATCH)
Para todos mis amigos de VLOOKUP, la gente de INDEX / MATCH ha estado esperando para ver si XLOOKUP puede manejar una coincidencia de dos vías. La gran noticia: puede hacerlo. La mala noticia: la metodología es un poco diferente de lo que esperarían los fanáticos de INDEX / MATCH. Podría estar un poco por encima de sus cabezas. Pero estoy seguro de que pueden adoptar este método.
Para una coincidencia bidireccional, desea encontrar qué fila contiene el número de cuenta A621 que se muestra en J3. Entonces, XLOOKUP comienza bastante fácil: = XLOOKUP (J3, A5: A15. Pero luego debe proporcionar un results_array. Puede usar el mismo truco que en XLOOKUP Benefit 9: Devolver los 12 meses en una sola fórmula anterior, pero utilícelo para devolver un vector vertical. Un XLOOKUP interno busca el mes J4 en los encabezados de mes en B4: G4. El return_array se especifica como B5: G15. El resultado es que el XLOOKUP interno devuelve un arreglo como el que se muestra en I10 : I20 a continuación. Dado que A621 se encuentra en la quinta celda de la matriz_buscada y 104 se encuentra en la quinta celda de la matriz_resultados, obtiene la respuesta correcta de la fórmula. A continuación, J6 muestra la forma anterior. J7 devuelve la nueva forma.
Beneficio 12 de XLOOKUP: sume todos los valores de búsqueda en una sola fórmula
La antigua función BÚSQUEDA ofrecía dos trucos extraños. Primero, si está tratando de calcular la cantidad total de gastos de bonificación que se acumularán, puede solicitar a LOOKUP que busque todos los valores en una sola fórmula. En la imagen de abajo, BÚSQUEDA (C4: C14 está realizando 11 búsquedas. Pero la función BÚSQUEDA no ofrecía una coincidencia exacta y requería que se ordenara la tabla de búsqueda.
Con XLOOKUP, puede especificar un rango como lookup_value y XLOOKUP devolverá todas las respuestas. El beneficio es que XLOOKUP puede hacer coincidencias exactas.
Consejo adicional: ¿Qué tal una BÚSQUEDA retorcida?
El MVP de Excel, Mike Girvin, a menudo muestra un truco de la función LOOKUP donde el Lookup_Vector es vertical y el Result_Vector es horizontal. XLOOKUP no admitirá este truco de forma nativa. Pero, si hace un poco de trampa y envuelve el results_array en la función TRANSPOSE, puede administrar una búsqueda retorcida.
