XLOOKUP con múltiples criterios en Excel

Qué aprenderás

• Cómo usar XLOOKUP con varios criterios en Excel.
• Dos enfoques prácticos: lógica booleana y concatenación.
• Ejemplos paso a paso, cuándo fallan las técnicas y alternativas útiles.

Importante: XLOOKUP está disponible en Excel 2021, Excel para Microsoft 365, Excel para la web y las apps móviles/tablet. Si usas versiones antiguas, algunas funciones podrían no estar disponibles.

Definición rápida

XLOOKUP es una función de búsqueda y referencia que reemplaza a VLOOKUP/HLOOKUP/LOOKUP. Devuelve valores desde una matriz de retorno basada en coincidencias en una matriz de búsqueda. Breve definición: XLOOKUP(buscar_valor, matriz_buscar, matriz_devolver, [si_no_encuentra], [modo_coincidencia], [modo_búsqueda]).

Sintaxis de XLOOKUP y un ejemplo con un solo criterio

La sintaxis general de XLOOKUP sigue este orden lógico:

=XLOOKUP(a,b,c,d,e,f)

• a (obligatorio): valor a buscar.
• b (obligatorio): matriz donde buscar.
• c (obligatorio): matriz desde la que devolver valores.
• d (opcional): texto o valor si no se encuentra coincidencia.
• e (opcional): modo de coincidencia (0 = coincidencia exacta por defecto; -1, 1, 2 para variaciones).
• f (opcional): modo de búsqueda (1 = de arriba a abajo por defecto; -1, 2, -2 para variaciones).

Ejemplo simple: elegir un ID desde validación de datos en E1 y devolver su puntuación en E2:

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)

En este caso se devuelve 51 porque E1 contiene el ID 1323 y la búsqueda exacta encuentra la fila correspondiente.

Por qué XLOOKUP cambia cuando hay varios criterios

XLOOKUP acepta un único valor de búsqueda en su argumento principal. Para buscar por múltiples condiciones, debemos transformar el conjunto de criterios en una sola matriz de comparación que XLOOKUP pueda evaluar. Hay dos enfoques comunes:

1. Lógica booleana: crear una matriz temporal de 1s y 0s usando comparaciones y operadores aritméticos.
2. Concatenación: unir los valores de criterio en cadenas y buscar la cadena combinada.

Ambos funcionan. La elección depende de necesidades como rendimiento, manejo de duplicados, operadores de comparación (>, <) y facilidad de mantenimiento.

Ejemplo 1: Varios criterios con lógica booleana

Escenario: tienes una tabla de bebidas vendidas en varios países. Cada combinación bebida+país tiene un manager y ventas. Deseas introducir Bebida y País y devolver Manager y Ventas.

Fórmula con lógica booleana (se muestra dividida para leer mejor):

=XLOOKUP(
  1,
  (T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Cómo funciona, paso a paso:

1. Cada comparación devuelve una matriz de valores lógicos TRUE/FALSE. Por ejemplo, (T_Managers[Drink]=G1) devuelve TRUE donde la bebida coincide con G1.
2. Al multiplicar dos matrices lógicas, Excel convierte TRUE→1 y FALSE→0 y multiplica elemento a elemento.
3. El resultado es una matriz de 1s y 0s donde 1 indica filas que cumplen todas las condiciones.
4. XLOOKUP busca el primer 1 en esa matriz (valor de búsqueda = 1) y devuelve las columnas solicitadas.

Ejemplo intermedio: si la columna Drink produce {FALSE;FALSE;…;TRUE;TRUE;…} y Country produce {FALSE;…;TRUE;…;TRUE;…}, la multiplicación produce una matriz con ceros salvo en las filas donde ambos son TRUE.

Ventajas de la lógica booleana:

• Permite operadores de comparación (> , < , >=) y combinaciones complejas.
• Puedes usar suma (+) en lugar de multiplicación para obtener filas que cumplan al menos una condición (OR).
• Funciona bien con matrices dinámicas y tablas.

Desventajas:

• Puede ser menos legible para usuarios no técnicos.
• En tablas grandes, las operaciones elementales pueden afectar rendimiento si hay muchas celdas.

Variantes útiles con lógica booleana

• Buscar filas que cumplan al menos una condición: usar suma en lugar de multiplicación. Ejemplo: (A=val1)+(B=val2).
• Incluir comparaciones numéricas: (T[Score]>100)*(T[Region]=G1).
• Usar NOT: 1-(condición) para invertir una condición booleana.

Ejemplo 2: Varios criterios con concatenación

Concatenar todos los criterios en una sola cadena y concatenar las columnas de búsqueda. Fórmula:

=XLOOKUP(
  G1&G2,
  T_Managers[Drink]&T_Managers[Country],
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Cómo funciona:

1. G1&G2 genera la cadena “CoffeeSpain” si G1=Coffee y G2=Spain.
2. T_Managers[Drink]&T_Managers[Country] crea una matriz de cadenas del tipo “Apple juiceAustralia”, “CoffeeSpain”, etc.
3. XLOOKUP busca la cadena combinada y devuelve las columnas deseadas.

Ventajas de la concatenación:

• Sencilla de entender y escribir.
• Muy eficaz cuando cada combinación criterio aparece una sola vez.
• Legible para usuarios regulares y fácil de depurar.

Desventajas:

• Frágil si hay espacios/formatos inconsistentes en los datos (“Coffee “ vs “Coffee”).
• No permite directamente operadores como > o <.
• Si hay duplicados exactos de la cadena combinada, XLOOKUP devolverá la primera aparición según el modo de búsqueda.

Comparación práctica: cuándo elegir cada método

• Usa concatenación cuando:

  • Las claves combinadas son únicas.
  • Los datos son texto consistente y normalizado.
  • Buscas una solución rápida y fácil de mantener.

• Usa lógica booleana cuando:

  • Necesitas operadores numéricos o rangos.
  • Quieres combinar condiciones con AND/OR de forma explícita.
  • Trabajas con arrays donde la normalización de texto es un problema.

Ejemplos adicionales y variantes avanzadas

A continuación se presentan patrones y ejemplos que amplían las dos técnicas principales.

Buscar con más de dos criterios (lógica booleana)

Si necesitas 3 condiciones, simplemente multiplicas más expresiones:

=XLOOKUP(
  1,
  (T[ColA]=H1)*(T[ColB]=H2)*(T[ColC]=H3),
  T[[Resultado]],
  "No result"
)

Para OR entre condiciones, suma las matrices:

=XLOOKUP(1,(T[A]=X)+(T[B]=Y),T[Resultado],"No result")

Nota: usar OR mediante suma te devolverá la primera fila que cumpla alguna de las condiciones.

Evitar falsos positivos con concatenación

Si concatenas valores, considera incluir un separador único para evitar colisiones de texto. Por ejemplo, usa el carácter | o un símbolo que no aparezca en los datos:

=XLOOKUP(G1&"|"&G2, T[ColA]&"|"&T[ColB], T[Resultado], "No result")

Esto evita que la combinación “ab”+”c” choque con “a”+”bc”.

Recuperar múltiples columnas y trabajar con matríces dinámicas

XLOOKUP acepta una matriz de retorno que puede abarcar varias columnas, por ejemplo:

=XLOOKUP(valor_buscar, matriz_buscar, matriz_devolucion_multiple, "No result")

La función devolverá una matriz de valores; en versiones recientes de Excel la matriz dinámica colocará resultados en varias celdas automáticamente.

Cuándo fallan estas técnicas (contraejemplos y limitaciones)

• Datos no normalizados: espacios, mayúsculas o formatos distintos pueden romper coincidencias por concatenación.
• Duplicados: si hay varias filas que cumplen EXACTAMENTE las condiciones, XLOOKUP devolverá la primera según el orden de búsqueda.
• Rendimiento: en tablas de decenas de miles de filas, multiplicar matrices extensas o concatenar columnas muy largas puede ralentizar el libro.
• Versiones antiguas de Excel: XLOOKUP no existe en Excel 2016 o anterior; allí hay que usar combinaciones de INDEX/MATCH, SUMPRODUCT o columnas auxiliares.

Alternativas cuando XLOOKUP no es adecuada

• INDEX + MATCH con array: la combinación INDEX/MATCH puede replicar la lógica booleana, pero es menos directa.
• SUMPRODUCT: permite multiplicar matrices y devolver resultados numéricos; útil para sumas condicionales complejas.
• Columnas auxiliares: crear una columna con la concatenación o una clave compuesta y luego usar VLOOKUP o XLOOKUP sobre esa columna.
• FILTRAR (FILTER): para devolver todas las filas que cumplan criterios, no solo la primera. Por ejemplo:

=FILTER(T_Managers, (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), "No result")

FILTER devuelve todas las coincidencias y es útil si esperas múltiples resultados.

Buenas prácticas y heurísticas

• Normaliza datos: TRIM() para quitar espacios, UPPER()/LOWER() para unificar mayúsculas.
• Usa separadores únicos al concatenar.
• Prefiere lógica booleana si necesitas operadores numéricos.
• Añade control de errores: envolver con IFERROR o indicar explícitamente el texto de no coincidencia.
• Testea fórmulas en una copia de la hoja para verificar rendimiento en datasets grandes.

Mini-metodología para implementar búsquedas multi-criterio

1. Verifica versión de Excel y disponibilidad de XLOOKUP/FILTER.
2. Decide si los criterios son únicos por fila.
3. Normaliza los datos (TRIM, CASE).
4. Elige método (concatenación si llaves únicas; booleana si necesitas operadores).
5. Implementa y añade manejo de errores.
6. Prueba con datos extremos y verifica rendimiento.

Checklist por roles

• Analista de datos: normaliza datos, documenta columnas auxiliares y pruebas de rendimiento.
• Administrador de sistemas: confirma versión de Excel y limita los accesos si el rendimiento se degrada.
• Usuario final: verifica listas desplegables y formatos antes de usar las fórmulas.

Plantilla rápida (copiar/pegar)

Lógica booleana (n filas):

=XLOOKUP(1, (Table[Col1]=H1)*(Table[Col2]=H2)*(Table[Col3]=H3), Table[[ResultCol1]:[ResultCol2]], "No result")

Concatenación con separador:

=XLOOKUP(H1&"|"&H2, Table[ColA]&"|"&Table[ColB], Table[[Return1]:[Return2]], "No result")

Diagrama de decisión

flowchart TD
  A[Inicio: ¿Tienes XLOOKUP?] --> B{¿Cada combinación es única?}
  B -- Sí --> C[Concatenación con separador]
  B -- No --> D{¿Necesitas operadores '>,<'?}
  D -- Sí --> E[Lógica booleana]
  D -- No --> F[Considera FILTRAR para múltiples coincidencias]
  E --> G[Probar y optimizar rendimiento]
  C --> G
  F --> G
  G --> H[Despliegue y documentación]

Criterios de aceptación para una implementación correcta

• La fórmula devuelve el manager y las ventas correctas para combinaciones de prueba.
• Maneja casos sin coincidencia con un mensaje claro.
• No arroja falsos positivos por errores de formato.
• El rendimiento en la hoja no se degrada significativamente con datos reales.

Consejos de rendimiento

• Evita fórmulas volátiles innecesarias.
• Si la tabla es muy grande, considera columnas auxiliares para almacenar claves concatenadas y luego indexa esa columna.
• Usa RANGE en lugar de referencias estructuradas repetidas si detectas lentitud extrema.

Notas de compatibilidad y migración

• Excel 2021 y Microsoft 365: XLOOKUP y FILTER funcionan de forma nativa.
• Excel 2019/2016: XLOOKUP no está disponible; usa INDEX+MATCH o columnas auxiliares.
• Excel para la web: XLOOKUP disponible en la mayoría de entornos; comprueba la versión del navegador y actualizaciones.

Galería de casos límite

• Datos con espacios finales en una columna de texto → usar TRIM.
• Diferencias de mayúsculas → normalizar con UPPER/LOWER.
• Filas duplicadas por error → revisar con herramientas de duplicados o FILTRAR.

Pequeño glosario

• Matriz: rango de celdas que actúa como entrada o salida.
• Concatenar: unir dos o más cadenas.
• Lógica booleana: operaciones que producen TRUE/FALSE; en cálculos se convierten a 1/0.

Preguntas frecuentes

¿Puedo usar XLOOKUP para devolver varias filas coincidentes? Sí, pero XLOOKUP por sí solo devuelve la primera coincidencia. Para devolver todas, usa FILTER.

¿Qué método es más rápido, concatenar o multiplicar matrices? Depende del caso. En tablas pequeñas la diferencia es mínima. En tablas grandes, la concatenación con columna auxiliar suele ser más rápida que calcular multiplicaciones sobre matrices muy amplias en tiempo real.

Resumen: XLOOKUP es potente y flexible. Para múltiples criterios, usa lógica booleana si necesitas operadores y flexibilidad. Usa concatenación cuando las llaves combinadas sean únicas y los datos estén limpios. Documenta tu solución, normaliza datos y prueba rendimiento antes de desplegar.