Escribiste una fórmula XLOOKUP con múltiples condiciones usando el operador ampersand, pero en lugar de un resultado, ves un error #SPILL!. Este error ocurre porque XLOOKUP devuelve un array que se superpone con celdas que contienen datos, o porque el array de búsqueda está estructurado incorrectamente para la lógica de múltiples condiciones. Este artículo explica por qué ocurre el error de desbordamiento con XLOOKUP al combinar condiciones y proporciona tres métodos fiables para solucionarlo.
Puntos clave: Solucionar el error #SPILL! de XLOOKUP con múltiples condiciones
- Columna auxiliar con concatenación: Combina las columnas de condiciones en una sola columna auxiliar y luego usa XLOOKUP contra esa columna única para evitar conflictos de desbordamiento de array.
- XLOOKUP con lógica booleana y doble unario: Usa
XLOOKUP(1, (range1=cond1)(range2=cond2), return_range)para forzar una búsqueda escalar sin desbordamiento. - INDEX/MATCH como alternativa: Cuando XLOOKUP continúe desbordándose, cambia a INDEX y MATCH con criterios ingresados como array para búsquedas estables de múltiples condiciones.
Por qué XLOOKUP con múltiples condiciones causa un error #SPILL!
El error #SPILL! ocurre cuando una fórmula devuelve múltiples resultados y no hay suficiente espacio vacío debajo y a la derecha de la celda de la fórmula. XLOOKUP normalmente devuelve un solo valor, pero cuando concatenas arrays de búsqueda usando & o usas operaciones de array en el argumento lookup_array, Excel puede interpretar el resultado como un array dinámico que se desborda. Por ejemplo, =XLOOKUP(A2&B2, Sheet2!A:A&Sheet2!B:B, Sheet2!C:C) crea dos argumentos de array: el valor de búsqueda es una sola cadena de texto concatenada, pero el array de búsqueda es un array de valores concatenados de columnas enteras. Excel intenta devolver un array desbordado porque el argumento lookup_array es una expresión de array, no una referencia de rango simple. Si alguna celda en el rango de desbordamiento no está vacía, obtienes #SPILL!. La solución es reestructurar la fórmula para que lookup_array sea un rango único que pueda coincidir sin expansión de array.
Método 1: Usar una columna auxiliar para concatenar condiciones
La solución más simple es agregar una columna auxiliar en la tabla de búsqueda que una las columnas de condiciones en una sola columna. Luego, XLOOKUP usa esa columna única como lookup_array sin operación de array.
- Insertar una columna auxiliar en la tabla de búsqueda
En la tabla de búsqueda, inserta una nueva columna junto a tus datos. Por ejemplo, si las condiciones están en la columna A y la columna B, inserta una nueva columna C. En la celda C2, ingresa=A2&B2y copia hacia abajo. Esto crea una clave concatenada única para cada fila. - Escribir la fórmula XLOOKUP contra la columna auxiliar
En tu celda de resultado, ingresa=XLOOKUP(A2&B2, Sheet2!C:C, Sheet2!D:D). Reemplaza Sheet2!C:C con la columna auxiliar y Sheet2!D:D con la columna que contiene el valor que deseas devolver. Debido a que lookup_array ahora es un rango de una sola columna, no ocurre expansión de array y el error #SPILL! desaparece. - Copiar la fórmula hacia abajo
Arrastra la fórmula hacia abajo para aplicarla a filas adicionales. Cada celda de fórmula devuelve un solo resultado sin desbordamiento.
Método 2: Usar lógica booleana con el operador doble unario
Si no puedes agregar una columna auxiliar, usa multiplicación booleana dentro de XLOOKUP. Este método fuerza una búsqueda escalar multiplicando arrays de condiciones en un solo array de 1s y 0s, y luego busca el valor 1.
- Construir la fórmula de multiplicación booleana
En la celda de la fórmula, ingresa=XLOOKUP(1, (A2=Sheet2!A:A)(B2=Sheet2!B:B), Sheet2!C:C). El valor de búsqueda es el número 1. El lookup_array es(A2=Sheet2!A:A)(B2=Sheet2!B:B), que devuelve un array de 1s y 0s. Un 1 aparece solo donde ambas condiciones son verdaderas. - Asegurar que no haya obstrucción de desbordamiento
Verifica que las celdas debajo y a la derecha de la celda de la fórmula estén vacías. Si contienen datos, límpialas o mueve la fórmula a una ubicación con suficiente espacio vacío. La fórmula puede desbordarse si lookup_array es una expresión de array, pero con el método de doble unario el rango de desbordamiento es solo una celda porque XLOOKUP devuelve una coincidencia única. - Probar con una sola fila
Si el error persiste, prueba la fórmula en una sola fila limitando los rangos a un pequeño número de filas, por ejemplo=XLOOKUP(1, (A2=Sheet2!A1:A10)(B2=Sheet2!B1:B10), Sheet2!C1:C10). Si funciona, el problema es probablemente la obstrucción del rango de desbordamiento. Extiende los rangos a columnas completas solo después de confirmar que la fórmula devuelve un solo valor.
Método 3: Usar INDEX y MATCH como alternativa
Cuando XLOOKUP continúa desbordándose a pesar de las soluciones anteriores, cambia a la combinación INDEX y MATCH. Esta fórmula clásica maneja múltiples condiciones sin desbordamiento porque MATCH siempre devuelve un número de posición único.
- Escribir la fórmula INDEX/MATCH
Ingresa=INDEX(Sheet2!C:C, MATCH(1, (A2=Sheet2!A:A)(B2=Sheet2!B:B), 0)). Sheet2!C:C es la columna de retorno. La parte de MATCH usa la misma multiplicación booleana que el Método 2. El tercer argumento de MATCH es 0 para coincidencia exacta. - Ingresar como fórmula de array en versiones antiguas de Excel
Si usas Excel 2019 o anterior, presiona Ctrl+Mayús+Entrar para ingresar la fórmula como fórmula de array. Excel 365 y Excel 2021 la aceptan normalmente sin entrada de array. - Copiar la fórmula a otras celdas
Arrastra la fórmula hacia abajo. Cada celda devuelve un solo resultado. No ocurre el error #SPILL! porque INDEX y MATCH no producen comportamiento de desbordamiento de array dinámico.
Si el error de desbordamiento aún aparece después de probar estos métodos
XLOOKUP devuelve #SPILL! incluso con una columna auxiliar
Si aún ves #SPILL! después de agregar una columna auxiliar, verifica que la columna auxiliar no contenga celdas vacías o errores. Una celda vacía en la columna auxiliar crea un valor concatenado en blanco, lo que puede causar múltiples coincidencias. Llena todas las celdas de la columna auxiliar con una fórmula de concatenación. También verifica que el rango de desbordamiento no esté obstruido por celdas combinadas o datos en columnas adyacentes. Selecciona la celda de la fórmula y presiona Ctrl+Mayús+Flecha Abajo para ver el rango de desbordamiento previsto. Limpia cualquier dato en ese rango.
XLOOKUP con multiplicación booleana devuelve resultados incorrectos
El método de multiplicación booleana puede devolver valores incorrectos si las columnas de búsqueda contienen celdas vacías. Una celda vacía comparada con una condición devuelve FALSO, que se multiplica a 0, por lo que la fila se omite. Asegúrate de que todas las columnas de condiciones tengan datos. Si una celda de condición está realmente en blanco, usa IF para tratar los espacios en blanco como una cadena específica, por ejemplo (IF(A2="","BLANK",A2)=Sheet2!A:A). Esto evita falsos desajustes.
INDEX/MATCH devuelve error #N/A
Un error #N/A de INDEX/MATCH significa que ninguna fila satisface todas las condiciones. Verifica que las condiciones en la fórmula coincidan con los tipos de datos en las columnas de búsqueda. Por ejemplo, si la columna A contiene números almacenados como texto, la comparación falla. Usa la función TEXTO o convierte los tipos de datos. También verifica si hay espacios finales en los valores de búsqueda o en las celdas de la tabla. Usa la función ESPACIOS en ambos lados de la comparación: (TRIM(A2)=TRIM(Sheet2!A:A)).
XLOOKUP con columna auxiliar vs lógica booleana: Comparación
| Elemento | Método de columna auxiliar | Método de lógica booleana |
|---|---|---|
| Esfuerzo de configuración | Requiere agregar una nueva columna a la tabla de origen | No se necesitan cambios en la tabla de origen |
| Legibilidad de la fórmula | Simple y fácil de auditar | Más compleja debido a la multiplicación de arrays |
| Rendimiento con datos grandes | Rápido porque la búsqueda es en una sola columna | Más lento porque se multiplican columnas completas en memoria |
| Riesgo de error de desbordamiento | Bajo — lookup_array es un rango único | Medio — lookup_array es una expresión de array |
| Compatibilidad con Excel antiguo | Funciona en Excel 2019 y anteriores | Requiere Ctrl+Mayús+Entrar en Excel 2019 y anteriores |
Ahora puedes eliminar el error #SPILL! al usar XLOOKUP con múltiples condiciones. Comienza agregando una columna auxiliar para la solución más simple. Si no puedes modificar los datos de origen, usa el método de multiplicación booleana. Para máxima compatibilidad con versiones antiguas de Excel, cambia a INDEX y MATCH. Como consejo avanzado, combina XLOOKUP con la función LET para almacenar el array booleano en una variable, lo que mejora la legibilidad y el rendimiento de la fórmula: =LET(conds, (A2=Sheet2!A:A)(B2=Sheet2!B:B), XLOOKUP(1, conds, Sheet2!C:C)).