Cómo utilizar la función XLOOKUP en Excel 2016

Excel 2016 para los suscriptores de Office 365 en Windows y Mac ahora admite una nueva función XLOOKUP, promocionada como un reemplazo considerablemente más simple y más versátil para la muy popular (aunque a menudo difamada) función de búsqueda vertical, VLOOKUP (no sé qué es la X en XLOOKUP significa; ¿extenso, quizás?).

Para aquellos de ustedes que aún no están familiarizados con BUSCARV (considerada la tercera función más utilizada justo después de SUMA y PROMEDIO), esta función busca verticalmente por fila en la columna más a la izquierda de una tabla de búsqueda designada de arriba a abajo hasta que encuentra un valor en una columna de búsqueda designada por un número de compensación que coincide o supera el que está buscando. Aunque es tremendamente útil para ubicar elementos particulares en una larga lista o columna de una tabla de datos en su hoja de trabajo, la función VLOOKUP tiene varias limitaciones que no comparte esta nueva función de búsqueda, como XLOOKUP:

• Predeterminado para encontrar coincidencias exactas para su valor de búsqueda en el rango de búsqueda
• Puede buscar tanto verticalmente (por fila) como horizontalmente (por columna) en una tabla, reemplazando así la necesidad de usar la función HLOOKUP al buscar horizontalmente por columna
• Puede buscar hacia la izquierda o hacia la derecha para que el rango de búsqueda en su tabla de búsqueda no tenga que estar ubicado en una columna a la izquierda de la designada como rango de retorno para que la función funcione
• Cuando se usa el valor predeterminado de coincidencia exacta, funciona incluso cuando los valores en el rango de búsqueda no están ordenados en un orden particular
• Puede buscar desde la fila inferior hasta la parte superior en el rango de la matriz de búsqueda, utilizando un argumento de modo de búsqueda opcional

La función BUSCAR X tiene cinco argumentos posibles, los tres primeros son obligatorios y los dos últimos opcionales, utilizando la siguiente sintaxis:

XLOOKUP ( lookup_value , lookup_array , return_array , [ match_mode ], [ search_mode ])

El argumento lookup_value obligatorio designa el valor o elemento que está buscando. El argumento de matriz de búsqueda requerido designa el rango de celdas que se buscarán para este valor de búsqueda, y el argumento de matriz de retorno designa el rango de celdas que contienen el valor que desea que se devuelva cuando Excel encuentra una coincidencia exacta.

* Tenga en cuenta que al designar los argumentos lookup_array y return_array en su función XLOOKUP, ambos rangos deben tener la misma longitud, de lo contrario Excel devolverá el #VALUE! error a su fórmula. Esta es una razón más para que use nombres de rango o nombres de columna de una tabla de datos designada al definir estos argumentos en lugar de señalarlos o escribir sus referencias de celda .

El argumento opcional match_mode puede contener cualquiera de los siguientes cuatro valores:

• 0 para una coincidencia exacta (el valor predeterminado, igual que cuando no se designa ningún argumento match_mode )
• -1 para la concordancia exacta o el siguiente valor menor
• 1 para la concordancia exacta o el siguiente valor mayor
• 2 para una coincidencia parcial utilizando caracteres comodín unidos a la referencia de celda en el argumento lookup_value

El argumento opcional modo_de_búsqueda puede contener cualquiera de los siguientes cuatro valores:

• 1 para buscar de principio a fin, es decir, de arriba a abajo (el valor predeterminado, igual que cuando no se designa ningún argumento search_mode )
• -1 para buscar del último al primero, es decir, de abajo hacia arriba
• 2 para una búsqueda binaria en orden ascendente
• -2 para búsqueda binaria en orden descendente

La mejor manera de comprender el poder y la versatilidad de la nueva función XLOOKUP es verla en acción en una hoja de cálculo de Excel. En la siguiente figura, tengo una hoja de trabajo con una tabla simple de datos de ventas de 2019 organizada por país. Para usar XLOOKUP para devolver las ventas totales de esta tabla en la celda E4 según el país que ingresó en la celda D4 de la hoja de trabajo, siga estos pasos:

Coloque el cursor de celda en la celda E4 de la hoja de trabajo

Haga clic en la opción Búsqueda y referencia en la pestaña Fórmulas seguida de XLOOKUP cerca de la parte inferior del menú desplegable para abrir su cuadro de diálogo Argumentos de función.

Haga clic en la celda D4 en la hoja de trabajo para ingresar su referencia de celda en el cuadro de texto del argumento Lookup_value.

Presione Tab para seleccionar el cuadro de texto del argumento Lookup_array, luego haga clic en la celda A4 y mantenga presionada la tecla Mayús mientras presiona Ctrl-flecha hacia abajo para seleccionar A4: A8 como el rango para buscar (porque el rango A3: B8 está definido como una tabla de datos de Excel, La Tabla1 [País] aparece en el cuadro de texto en lugar del rango A4: A8).

Presione Tab para seleccionar el cuadro de texto del argumento Return_array, luego haga clic en la celda B4 y mantenga presionada la tecla Mayús mientras presiona Ctrl-flecha hacia abajo para seleccionar B4: B8 como el rango que contiene los valores que se devolverán según los resultados de la búsqueda (que aparece como Table1 [Ventas totales] en el cuadro de texto).

Haga clic en Aceptar para ingresar la fórmula XLOOKUP en la celda E4.

Creando una fórmula con XLOOKUP en la celda E4 que devuelve las ventas según el país ingresado en la celda D4.

Excel ingresa la fórmula XLOOKUP en la celda E4 de la hoja de trabajo y devuelve 4900 como resultado porque Costa Rica está ingresada actualmente en la celda de búsqueda D4 y, como puede ver en la tabla de ventas de 2019, este es de hecho el total de ventas realizadas para este país.

Debido a que XLOOKUP funciona de derecha a izquierda tan bien como de izquierda a derecha, también puede usar esta función para devolver el país de esta tabla de ventas en función de una cifra de ventas en particular. La siguiente figura le muestra cómo hacer esto. Esta vez, crea la fórmula XLOOKUP en la celda D4 y designa el valor ingresado en la celda E4 (11,000, en este caso) como el argumento lookup_value.

Además, ingresa -1 como argumento match_mode para anular la coincidencia exacta predeterminada de la función de modo que Excel devuelva el país con una coincidencia exacta con el valor de ventas ingresado en la celda de búsqueda E4 o el que tiene las ventas totales más bajas siguientes (México con $ 10,000 en este caso, ya que no hay ningún país en esta tabla con $ 11,000 de ventas totales). Sin designar un argumento match_mode para esta fórmula, Excel devolvería #NA como resultado, porque no hay una coincidencia exacta con $ 11,000 en esta tabla de ventas.

Creando una fórmula con XLOOKUP en la celda D4 que devuelve el país según las ventas ingresadas en la celda E4

Debido a que la función XLOOKUP es igualmente cómoda buscando horizontalmente por columna que buscando verticalmente por fila, puede usarla para crear una fórmula que realiza una búsqueda bidireccional (reemplazando la necesidad de crear una fórmula que combine las funciones INDICE y COINCIDIR como en el pasado). La siguiente figura, que contiene la tabla de programación de producción de 2019 para los números de pieza, AB-100 a AB-103 para los meses de abril a diciembre, muestra cómo se hace esto.

Crear una fórmula con funciones XLOOKUP anidadas para devolver el número de unidades producidas para una pieza en un mes en particular.

En la celda B12, creé la siguiente fórmula:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Esta fórmula comienza definiendo una función XLOOKUP que busca verticalmente por fila una coincidencia exacta con la entrada de la pieza realizada en la celda denominada part_lookup (celda B10, en este caso) en el rango de celdas $ A $ 3: $ A $ 6 de la tabla de producción . Sin embargo, tenga en cuenta que el argumento return_array para esta función de BÚSQUEDA original es en sí mismo una segunda función de XLOOKUP.

Esta segunda función XLOOKUP anidada busca en el rango de celdas $ B $ 2: $ J $ 2 horizontalmente por columna para obtener una coincidencia exacta con la entrada de fecha realizada en la celda denominada date_lookup (celda B11, en este caso). El argumento return_array para esta segunda función XLOOKUP anidada es $ B $ 3: $ J $ 6, el rango de celdas de todos los valores de producción en la tabla.

La forma en que funciona esta fórmula es que Excel primero calcula el resultado de la segunda función XLOOKUP anidada realizando una búsqueda horizontal que, en este caso, devuelve la matriz en el rango de celdas D3: D6 de la columna Jun-19 (con los valores: 438, 153, 306 y 779) como resultado. Este resultado, a su vez, se convierte en el argumento return_array para la función XLOOKUP original que realiza una búsqueda vertical por fila para obtener una coincidencia exacta con la entrada del número de pieza realizada en la celda B11 (denominada part_lookup). Debido a que, en este ejemplo, esta celda part_lookup contiene AB-102, la fórmula devuelve solo el valor de producción de junio de 19, 306, del resultado de la segunda función XLOOKUP siguiente.

¡Ahí tienes! Un primer vistazo a XLOOKUP, una nueva función de búsqueda poderosa, versátil y bastante fácil de usar que no solo puede realizar las búsquedas de un solo valor realizadas por las funciones VLOOKUP y HLOOKUP, sino también las búsquedas de valores bidireccionales realizadas combinando las También funciones INDICE y MATCH.

* Desafortunadamente, la función XLOOKUP no es compatible con versiones anteriores de Microsoft Excel que solo admiten las funciones VLOOKUP y HLOOKUP o compatible con versiones actuales que aún no la incluyen como una de sus funciones de búsqueda, como Excel 2019 y Excel Online . Esto significa que si comparte un libro de trabajo que contiene fórmulas XLOOKUP con compañeros de trabajo o clientes que están usando una versión de Excel que no incluye esta nueva función de búsqueda, todas estas fórmulas devolverán #NAME? valores de error cuando abren su hoja de trabajo.