1. Software Microsoft Office Excel Cómo usar la función XLOOKUP en Excel 2016

Por Greg Harvey

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

Para aquellos de ustedes que aún no están familiarizados con VLOOKUP (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 encontrar un valor en un columna de búsqueda designada por un número de desplazamiento que coincide o supera el que está buscando. Aunque es tremendamente útil para localizar elementos particulares en una larga lista o columna de una tabla de datos en su hoja de trabajo, la función BUSCARV tiene varias limitaciones que esta nueva función de búsqueda no comparte, como XLOOKUP:

  • El valor predeterminado es 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 BUSCAR 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 ubicarse en una columna a la izquierda de la designada como el rango de retorno para que la función funcione Cuando se usa la coincidencia exacta predeterminada, funciona incluso cuando los valores en el rango de búsqueda no se ordenan en un orden particular Puede buscar desde la fila inferior hasta la superior en el rango de matriz de búsqueda, utilizando un argumento de modo de búsqueda opcional

La función XLOOKUP 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 requerido lookup_value designa el valor o elemento que está buscando. El argumento de matriz de búsqueda requerida designa el rango de celdas en las que se buscará este valor de búsqueda, y el argumento return_array designa el rango de celdas que contiene el valor que desea devolver cuando Excel encuentra una coincidencia exacta.

* Tenga en cuenta 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 # ¡VALOR! error a tu fórmula Esta es una razón más para que use nombres de rango o columnas 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 coincidencia exacta o el siguiente valor menor 1 para la coincidencia exacta o el siguiente valor mayor 2 para la coincidencia parcial con caracteres comodín unidos a la referencia de celda en el argumento lookup_value

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

  • 1 para buscar desde el primero hasta el último, es decir, de arriba hacia abajo (el valor predeterminado, igual que cuando no se designa ningún argumento search_mode) -1 para buscar el ú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 simple tabla 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 ingrese en la celda D4 de la hoja de trabajo, siga estos pasos:

  1. 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 de argumento Valor de búsqueda. Presione la tecla 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 la flecha Ctrl-abajo para seleccionar A4: A8 como el rango para buscar (porque el rango A3: B8 se define como una tabla de datos de Excel, La Tabla 1 [País] aparece en el cuadro de texto en lugar del rango A4: A8). Presione Tab para seleccionar el cuadro de texto de argumento Return_array, luego haga clic en la celda B4 y mantenga presionada la tecla Mayús mientras presiona la flecha Ctrl-abajo para seleccionar B4: B8 como el rango que contiene los valores que se devolverán en función de los resultados de la búsqueda (que aparece como Tabla1 [Ventas totales] en el cuadro de texto).

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

Creación de fórmulas XLOOKUP

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

Debido a que XLOOKUP funciona de derecha a izquierda, así como de izquierda a derecha, puede usar esta función también para devolver el país de esta tabla de ventas en función de una cifra de ventas 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 argumento lookup_value.

Además, ingrese -1 como argumento match_mode para anular el valor predeterminado de coincidencia exacta de la función, de modo que Excel devuelva el país con una coincidencia exacta al valor de ventas ingresado en la celda de búsqueda E4 o el que tenga las siguientes ventas totales más bajas (México con $ 10,000 en este caso ya que no hay 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 de $ 11,000 en esta tabla de ventas.

Fórmula XLOOKUP en D4

Debido a que la función XLOOKUP es igualmente cómoda al buscar horizontalmente por columna que al buscar verticalmente por fila, puede usarla para crear una fórmula que realice una búsqueda bidireccional (reemplazando la necesidad de crear una fórmula que combine las funciones INDEX y MATCH 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, le muestra cómo se hace esto.

funciones XLOOKUP anidadas

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 parte realizada en la celda llamada 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 LOOKUP original es en sí misma una segunda función XLOOKUP.

Esta segunda función XLOOKUP anidada busca en el rango de celdas $ B $ 2: $ J $ 2 horizontalmente por columna para una coincidencia exacta con la entrada de fecha realizada en la celda llamada 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 del 19 de junio (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 una coincidencia exacta con la entrada del número de parte realizada en la celda B11 (llamada 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 del 30 de junio, 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 valor único realizadas por las funciones VLOOKUP y HLOOKUP, sino también las búsquedas de valores bidireccionales combinadas Las funciones INDEX y MATCH también.

* 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 las 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 usan 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.