Cómo utilizar la función XLOOKUP de Excel -

Tabla de contenido

Resumen

La función Excel XLOOKUP es un reemplazo moderno y flexible para funciones más antiguas como BUSCARV, BUSCARH y BUSCAR. XLOOKUP admite coincidencias aproximadas y exactas, comodines (*?) Para coincidencias parciales y búsquedas en rangos verticales u horizontales.

Propósito

Buscar valores en rango o matriz

Valor devuelto

Valores coincidentes de la matriz de retorno

Sintaxis

= XLOOKUP (búsqueda, búsqueda_array, return_array, (not_found), (match_mode), (search_mode))

Argumentos

  • lookup : el valor de búsqueda.
  • lookup_array : la matriz o rango para buscar.
  • return_array : la matriz o rango que se devolverá.
  • not_found : valor (opcional) que se devolverá si no se encuentra ninguna coincidencia.
  • match_mode - (opcional) 0 = coincidencia exacta (predeterminado), -1 = coincidencia exacta o siguiente menor, 1 = coincidencia exacta o siguiente mayor, 2 = coincidencia comodín.
  • modo_búsqueda - (opcional) 1 = búsqueda desde el principio (predeterminado), -1 = búsqueda desde el último, 2 = búsqueda binaria ascendente, -2 = búsqueda binaria descendente.

Versión

Excel 365

Notas de uso

XLOOKUP es un reemplazo moderno de la función VLOOKUP. Es una función flexible y versátil que se puede utilizar en una amplia variedad de situaciones.

XLOOKUP puede encontrar valores en rangos verticales u horizontales, puede realizar coincidencias aproximadas y exactas y admite comodines (*?) Para coincidencias parciales. Además, XLOOKUP puede buscar datos a partir del primer valor o el último valor (consulte los detalles del tipo de coincidencia y del modo de búsqueda a continuación). En comparación con funciones más antiguas como BUSCARV, BUSCARH y BUSCAR, BUSCAR X ofrece varias ventajas clave.

Mensaje no encontrado

Cuando XLOOKUP no puede encontrar una coincidencia, devuelve el error # N / A, como otras funciones de coincidencia en Excel. A diferencia de las otras funciones de coincidencia, XLOOKUP admite un argumento opcional llamado not_found que se puede usar para anular el error # N / A cuando de otro modo aparecería. Los valores típicos para not_found pueden ser "No encontrado", "No coincide", "Sin resultado", etc. Cuando proporcione un valor para not_found, encierre el texto entre comillas dobles ("").

Nota: tenga cuidado si proporciona una cadena vacía ("") para not_found. Si no se encuentra ninguna coincidencia, XLOOKUP no mostrará nada en lugar de # N / A. Si desea ver el error # N / A cuando no se encuentra una coincidencia, omita el argumento por completo.

Tipo de concordancia

De forma predeterminada, XLOOKUP realizará una coincidencia exacta. El comportamiento de coincidencia está controlado por un argumento opcional llamado match_type, que tiene las siguientes opciones:

Tipo de concordancia Comportamiento
0 (predeterminado) Coincidencia exacta. Devolverá # N / A si no coincide.
-1 Coincidencia exacta o siguiente elemento más pequeño.
1 Coincidencia exacta o siguiente elemento más grande.
2 Coincidencia de comodines (*,?, ~)

Modo de búsqueda

De forma predeterminada, XLOOKUP comenzará a coincidir desde el primer valor de datos. El comportamiento de búsqueda está controlado por un argumento opcional llamado modo_búsqueda , que proporciona las siguientes opciones:

Modo de búsqueda Comportamiento
1 (predeterminado) Buscar desde el primer valor
-1 Buscar desde el último valor (inverso)
2 Valores de búsqueda binaria ordenados en orden ascendente
-2 Valores de búsqueda binaria ordenados en orden descendente

Las búsquedas binarias son muy rápidas, pero los datos deben ordenarse según sea necesario. Si los datos no se ordenan correctamente, una búsqueda binaria puede devolver resultados no válidos que parecen perfectamente normales.

Ejemplo n. ° 1: coincidencia exacta básica

De forma predeterminada, XLOOKUP realizará una coincidencia exacta. En el siguiente ejemplo, XLOOKUP se utiliza para recuperar las ventas en función de una coincidencia exacta en la película. La fórmula en H5 es:

=XLOOKUP(H4,B5:B9,E5:E9)

Explicación más detallada aquí.

Ejemplo n. ° 2: coincidencia aproximada básica

Para habilitar una coincidencia aproximada, proporcione un valor para el argumento "match_mode". En el siguiente ejemplo, XLOOKUP se usa para calcular un descuento basado en la cantidad, que requiere una coincidencia aproximada. La fórmula en F5 proporciona -1 para match_mode para permitir la coincidencia aproximada con el comportamiento de "coincidencia exacta o siguiente más pequeño":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Explicación más detallada aquí.

Ejemplo # 3 - valores múltiples

XLOOKUP puede devolver más de un valor al mismo tiempo para la misma coincidencia. El siguiente ejemplo muestra cómo XLOOKUP se puede configurar para devolver tres valores coincidentes con una única fórmula. La fórmula en C5 es:

=XLOOKUP(B5,B8:B15,C8:E15)

Observe que la matriz de retorno (C8: E15) incluye 3 columnas: Primero, Último, Departamento. Los tres valores se devuelven y se derraman en el rango C5: E5.

Ejemplo n. ° 4: búsqueda bidireccional

XLOOKUP se puede utilizar para realizar una búsqueda bidireccional, anidando un XLOOKUP dentro de otro. En el siguiente ejemplo, la XLOOKUP "interna" recupera una fila completa (todos los valores para Glass), que se transfiere a la XLOOKUP "externa" como la matriz de retorno. El XLOOKUP externo encuentra el grupo apropiado (B) y devuelve el valor correspondiente (17.25) como resultado final.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Más detalles aquí.

Ejemplo n. ° 5: mensaje no encontrado

Al igual que otras funciones de búsqueda, si XLOOKUP no encuentra un valor, devuelve el error # N / A. Para mostrar un mensaje personalizado en lugar de # N / A, proporcione un valor para el argumento opcional "no encontrado", entre comillas dobles (""). Por ejemplo, para mostrar "No encontrado" cuando no se encuentra una película que coincida, según la hoja de trabajo a continuación, use:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Puede personalizar este mensaje como desee: "No coincide", "Película no encontrada", etc.

Ejemplo n. ° 6: criterios complejos

Con la capacidad de manejar matrices de forma nativa, XLOOKUP se puede utilizar con criterios complejos. En el siguiente ejemplo, XLOOKUP coincide con el primer registro donde: la cuenta comienza con "x" y la región es "este" y el mes no es abril:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Detalles: (1) ejemplo simple, (2) ejemplo más complejo.

Beneficios de XLOOKUP

XLOOKUP ofrece varias ventajas importantes, especialmente en comparación con VLOOKUP:

  • XLOOKUP puede buscar datos a la derecha o izquierda de los valores de búsqueda
  • XLOOKUP puede devolver múltiples resultados (ejemplo # 3 arriba)
  • XLOOKUP por defecto es una coincidencia exacta (VLOOKUP por defecto es aproximado)
  • XLOOKUP puede trabajar con datos verticales y horizontales
  • XLOOKUP puede realizar una búsqueda inversa (del último al primero)
  • XLOOKUP puede devolver filas o columnas completas, no solo un valor
  • XLOOKUP puede trabajar con matrices de forma nativa para aplicar criterios complejos

Notas

  1. XLOOKUP puede funcionar con matrices verticales y horizontales.
  2. XLOOKUP devolverá # N / A si no se encuentra el valor de búsqueda.
  3. El lookup_array debe tener una dimensión compatible con el argumento return_array ; de lo contrario, XLOOKUP devolverá #VALUE!
  4. Si XLOOKUP se usa entre libros de trabajo, ambos libros de trabajo deben estar abiertos; de lo contrario, XLOOKUP devolverá #REF !.
  5. Como la función INDICE, XLOOKUP devuelve una referencia como resultado.

Videos relacionados

Ejemplo básico de XLOOKUP En este video, configuraremos la función XLOOKUP con un ejemplo básico. Al hacer coincidir el nombre de la ciudad, recuperaremos el país y la población. Coincidencia aproximada básica de XLOOKUP En este video, configuraremos la función XLOOKUP para realizar una coincidencia aproximada con el fin de calcular un descuento basado en la cantidad. XLOOKUP con lógica booleana En este video veremos cómo usar la función XLOOKUP con lógica booleana para aplicar múltiples criterios. XLOOKUP con múltiples valores de búsqueda En este video, configuraremos XLOOKUP para devolver múltiples valores en una matriz dinámica, proporcionando un rango de valores de búsqueda en lugar de un único valor de búsqueda.

Articulos interesantes...