You wrote an XLOOKUP formula with multiple conditions using the ampersand operator, but instead of a result, you see a #SPILL! error. This error occurs because XLOOKUP returns an array that overlaps cells containing data, or the lookup array is structured incorrectly for multi-condition logic. This article explains why the spill error happens with XLOOKUP when combining conditions and provides three reliable methods to fix it.
Key Takeaways: Fix XLOOKUP #SPILL! Error With Multiple Conditions
- Helper column with concatenation: Combine condition columns into one helper column, then XLOOKUP against that single column to avoid array spill conflicts.
- XLOOKUP with boolean logic and double unary: Use
XLOOKUP(1, (range1=cond1)(range2=cond2), return_range)to force a scalar lookup without spilling. - INDEX/MATCH as an alternative: When XLOOKUP continues to spill, switch to INDEX and MATCH with array-entered criteria for stable multi-condition lookups.
Why XLOOKUP With Multiple Conditions Causes a #SPILL! Error
The #SPILL! error occurs when a formula returns multiple results and there is not enough empty space below and to the right of the formula cell. XLOOKUP normally returns a single value, but when you concatenate lookup arrays using & or use array operations in the lookup_array argument, Excel may interpret the result as a dynamic array that spills. For example, =XLOOKUP(A2&B2, Sheet2!A:A&Sheet2!B:B, Sheet2!C:C) creates two array arguments: the lookup value is a single concatenated text string, but the lookup array is an array of concatenated values from entire columns. Excel tries to return a spilled array because the lookup_array argument is an array expression, not a simple range reference. If any cell in the spill range is not empty, you get #SPILL!. The fix is to restructure the formula so that the lookup_array is a single range that can be matched without array expansion.
Method 1: Use a Helper Column to Concatenate Conditions
The simplest fix is to add a helper column in the lookup table that joins the condition columns into one column. Then XLOOKUP uses that single column as the lookup_array with no array operation.
- Insert a helper column in the lookup table
In the lookup table, insert a new column next to your data. For example, if conditions are in column A and column B, insert a new column C. In cell C2, enter=A2&B2and copy down. This creates a unique concatenated key for each row. - Write the XLOOKUP formula against the helper column
In your result cell, enter=XLOOKUP(A2&B2, Sheet2!C:C, Sheet2!D:D). Replace Sheet2!C:C with the helper column and Sheet2!D:D with the column containing the value you want to return. Because the lookup_array is now a single column range, no array expansion occurs and the #SPILL! error disappears. - Copy the formula down
Drag the formula down to apply it to additional rows. Each formula cell returns a single result without spilling.
Method 2: Use Boolean Logic With the Double Unary Operator
If you cannot add a helper column, use boolean multiplication inside XLOOKUP. This method forces a scalar lookup by multiplying condition arrays into a single array of 1s and 0s, then looking for the value 1.
- Build the boolean multiplication formula
In the formula cell, enter=XLOOKUP(1, (A2=Sheet2!A:A)(B2=Sheet2!B:B), Sheet2!C:C). The lookup_value is the number 1. The lookup_array is(A2=Sheet2!A:A)(B2=Sheet2!B:B), which returns an array of 1s and 0s. A 1 appears only where both conditions are true. - Ensure no spill obstruction
Check that the cells below and to the right of the formula cell are empty. If they contain data, clear them or move the formula to a location with enough empty space. The formula may still spill if the lookup_array is an array expression, but with the double unary method the spill range is only one cell because XLOOKUP returns a single match. - Test with a single row
If the error persists, test the formula on a single row by limiting the ranges to a small number of rows, for example=XLOOKUP(1, (A2=Sheet2!A1:A10)(B2=Sheet2!B1:B10), Sheet2!C1:C10). If it works, the issue is likely spill range obstruction. Extend the ranges to full columns only after confirming the formula returns a single value.
Method 3: Use INDEX and MATCH as an Alternative
When XLOOKUP continues to spill despite the above fixes, switch to the INDEX and MATCH combination. This classic formula handles multiple conditions without spilling because MATCH always returns a single position number.
- Write the INDEX/MATCH formula
Enter=INDEX(Sheet2!C:C, MATCH(1, (A2=Sheet2!A:A)(B2=Sheet2!B:B), 0)). Sheet2!C:C is the return column. The MATCH part uses the same boolean multiplication as Method 2. The third argument of MATCH is 0 for exact match. - Enter as an array formula in older Excel versions
If you are using Excel 2019 or earlier, press Ctrl+Shift+Enter to enter the formula as an array formula. Excel 365 and Excel 2021 accept it normally without array entry. - Copy the formula to other cells
Drag the formula down. Each cell returns a single result. No #SPILL! error occurs because INDEX and MATCH do not produce dynamic array spill behavior.
If the Spill Error Still Appears After Trying These Methods
XLOOKUP Returns #SPILL! Even With a Helper Column
If you still see #SPILL! after adding a helper column, check that the helper column contains no empty cells or errors. An empty cell in the helper column creates a blank concatenated value, which may cause multiple matches. Fill all helper column cells with a concatenation formula. Also verify that the spill range is not obstructed by merged cells or data in adjacent columns. Select the formula cell and press Ctrl+Shift+Down Arrow to see the intended spill range. Clear any data in that range.
XLOOKUP With Boolean Multiplication Returns Wrong Results
The boolean multiplication method may return incorrect values if the lookup columns contain empty cells. An empty cell compared to a condition returns FALSE, which multiplies to 0, so the row is skipped. Ensure all condition columns have data. If a condition cell is truly blank, use IF to treat blanks as a specific string, for example (IF(A2="","BLANK",A2)=Sheet2!A:A). This prevents false mismatches.
INDEX/MATCH Returns #N/A Error
A #N/A error from INDEX/MATCH means no row satisfies all conditions. Verify that the conditions in the formula match the data types in the lookup columns. For example, if column A contains numbers stored as text, the comparison fails. Use the TEXT function or convert data types. Also check for trailing spaces in lookup values or table cells. Use the TRIM function on both sides of the comparison: (TRIM(A2)=TRIM(Sheet2!A:A)).
XLOOKUP With Helper Column vs Boolean Logic: Comparison
| Item | Helper Column Method | Boolean Logic Method |
|---|---|---|
| Setup effort | Requires adding a new column to the source table | No source table changes needed |
| Formula readability | Simple and easy to audit | More complex due to array multiplication |
| Performance with large data | Fast because lookup is on a single column | Slower because full columns are multiplied in memory |
| Spill error risk | Low — lookup_array is a single range | Medium — lookup_array is an array expression |
| Compatibility with older Excel | Works in Excel 2019 and earlier | Requires Ctrl+Shift+Enter in Excel 2019 and earlier |
Now you can eliminate the #SPILL! error when using XLOOKUP with multiple conditions. Start by adding a helper column for the simplest fix. If you cannot modify the source data, use the boolean multiplication method. For maximum compatibility with older Excel versions, switch to INDEX and MATCH. As an advanced tip, combine XLOOKUP with the LET function to store the boolean array in a variable, which improves formula readability and performance: =LET(conds, (A2=Sheet2!A:A)(B2=Sheet2!B:B), XLOOKUP(1, conds, Sheet2!C:C)).