Fixing Excel XLOOKUP #VALUE! Error When Return Array Has Multiple Columns

You write what looks like a clean XLOOKUP formula, point the return array at a multi-column range, and Excel fires back #VALUE!. Nothing obvious is wrong with the lookup value or the search array — the error is coming entirely from the return side. This is one of those cases where Excel's dynamic array engine is doing exactly what it was designed to do, and the fix is straightforward once you understand the rule.

What You'll Learn

• Why XLOOKUP raises #VALUE! specifically when the return array spans multiple columns
• How Excel's spill behavior interacts with multi-column return ranges
• Four concrete fixes: spill range, INDEX, CHOOSECOLS, and IFERROR wrapping
• Which fix to use depending on whether you need one column or several

Prerequisites

You need Excel 365 or Excel 2021 to use XLOOKUP at all — it is not available in earlier versions. The CHOOSECOLS fix in this article requires Excel 365 (it was added with the newer dynamic array functions). If you are on Excel 2021, the INDEX approach will cover you. Make sure your workbook is saved in .xlsx format, not .xls — legacy format disables dynamic array spilling entirely.

What's Actually Going Wrong

Before jumping to the fixes, it helps to know exactly what Excel is trying to do when it evaluates your formula. The error is not random.

How XLOOKUP Handles Return Arrays Internally

XLOOKUP locates the matched row in the search array and then returns the corresponding row (or column) from the return array. When the return array is a single column, the result is one cell value. When the return array is multiple columns wide, XLOOKUP wants to spill that entire row of values across several adjacent cells — one cell per column.

This spill behavior is powered by Excel's dynamic array engine, introduced alongside functions like FILTER, SORT, and UNIQUE. The engine reserves a rectangular block of cells at runtime to hold the result. If anything blocks that block, Excel raises a spill error instead.

Why Multiple Columns Break the Formula

The #VALUE! error in this specific scenario usually comes from one of three root causes:

1. The formula is inside a merged cell. Merged cells cannot participate in spill ranges. Excel cannot split the spill result across a merge boundary, so it errors immediately.
2. Another value is already sitting in a cell the spill range needs. Even a single space character in a neighboring cell will block the spill.
3. The return array dimensions conflict with where you placed the formula. If you put the formula in a cell with insufficient room to the right (for example, near the last column), Excel cannot allocate the spill range.

A subtler cause: in older XLOOKUP behavior (pre-2022 patches), returning a horizontally-oriented array when Excel expected a vertical one could also produce #VALUE!. Keeping your Office install updated resolves that variant automatically.

If you have run into a related problem where the lookup itself returns the wrong value due to merged cells in the search array, the article on XLOOKUP returning wrong values when the search array has merged cells covers that separately.

Fix 1: Let the Spill Range Breathe

The simplest fix when your formula is otherwise correct is to clear the cells the spill range needs. Click the formula cell, look at the blue spill preview that appears, and identify every cell Excel has outlined. Delete any content — including spaces and invisible characters — from every cell in that range.

To find hidden blockers quickly, use Go To Special: press Ctrl+G, click Special, choose Blanks, and inspect the highlighted cells. A cell that looks empty may contain a formula returning an empty string (""), which still blocks the spill.

=XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]:[Stock]])

If the formula above is placed in cell D2 and your return array is three columns wide, cells D2, E2, and F2 must all be clear. Check E2 and F2 even if they look empty.

Also check whether any of those cells are part of a merged region. Unmerge them via Home → Merge & Center → Unmerge Cells before re-entering the formula.

Fix 2: Use INDEX to Extract a Single Column

If you only need one specific column from the return range rather than all of them, wrapping XLOOKUP inside INDEX is a clean approach that eliminates the spill problem entirely. INDEX lets you specify which column of the returned array you want, and the result is a single value — no spill needed.

=INDEX(XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]:[Stock]]), 1, 2)

Here, the 1 refers to the first (and only) matched row, and 2 picks the second column of the returned array — in this example, the Price column. Adjust the column number to match whichever field you need.

This pattern also pairs well with MATCH if you want the column selection to be dynamic rather than hardcoded. The article on fixing INDEX MATCH #VALUE! errors with multi-column array formulas walks through similar column-selection patterns in depth.

Fix 3: Use CHOOSECOLS to Pick Specific Columns

If you need more than one column but not all of them, CHOOSECOLS (Excel 365 only) is the most readable solution. You pass it the XLOOKUP result and a list of column numbers to keep.

=CHOOSECOLS(XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]:[Stock]:[Category]]), 1, 3)

This returns only columns 1 and 3 from the matched row — Name and Stock — skipping Price and Category. The spill range is now two cells wide instead of four, which is much less likely to collide with existing content.

You can also use negative column numbers to count from the right, which is useful when the return array width varies due to table column additions.

=CHOOSECOLS(XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]:[Stock]]), -1)

That returns only the last column of whatever XLOOKUP finds, regardless of how many columns the table gains over time.

Fix 4: Wrap in IFERROR with a Fallback

Sometimes you want the formula to silently return a placeholder rather than surface an error to end users — especially in dashboards or reports others will read. Wrapping the entire expression in IFERROR handles that, though it is important to understand that this masks the error rather than fixing its root cause.

=IFERROR(XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]:[Stock]]), "Not found")

Use this only after you have confirmed the formula is structurally correct. If the #VALUE! is caused by a blocked spill range, IFERROR will swallow the error today but may return stale or missing data silently when the blocking cell changes. Always pair IFERROR with the actual fixes above when deploying formulas for others.

For a broader look at how Excel's SUMPRODUCT function handles a similar #VALUE! pattern when ranges span multiple sheets, see the article on SUMPRODUCT returning #VALUE! across multiple sheets — the diagnostic approach is transferable.

Common Pitfalls When Returning Multi-Column Results

Even after applying the fixes above, a few edge cases catch people out repeatedly.

Table columns that shift during inserts

When your return array references a structured table range like Table1[[A]:[D]], inserting a new column inside that range changes the array width at runtime. If the spill area was previously cleared for a three-column result and now needs four, the new fourth column may be occupied. Use CHOOSECOLS to pin the columns you actually want rather than relying on a contiguous range.

Copying the formula across rows

If you copy a spilling XLOOKUP formula down a column, each copy creates its own independent spill range. Two adjacent spill ranges on the same row will collide. The solution is to use only one formula and reference its spill range with the # operator — for example, =D2# — to propagate the results, rather than copying the formula itself.

Confusing #VALUE! with #SPILL!

These are different errors. #SPILL! means the spill range is blocked. #VALUE! means a value in the formula itself is the wrong data type or an argument is invalid. If you see #SPILL!, the fix is always to clear the target cells. If you see #VALUE!, the issue is more likely a dimension mismatch, a non-numeric argument where a number is expected, or a malformed array reference. Check the formula bar carefully for extra spaces in range references or mismatched bracket counts in structured table references.

Using XLOOKUP in non-365 environments

If this workbook is shared with someone on Excel 2019 or earlier, XLOOKUP will not work at all for them — they will see #NAME?. For cross-version compatibility, you need INDEX/MATCH instead. The article on fixing INDEX MATCH #N/A errors with merged cells shows how to structure that fallback approach for tricky lookup scenarios.

Return array orientation mismatch

XLOOKUP expects the search array and return array to have the same number of rows (for a vertical lookup) or the same number of columns (for a horizontal lookup). If you accidentally pass a return array with a different number of rows than the search array, Excel will raise #VALUE! before the spill logic even runs. Double-check that Products[ProductID] and Products[[Name]:[Price]:[Stock]] are drawn from the same table and have the same row count.

Wrapping Up

The #VALUE! error from a multi-column XLOOKUP return array is almost always a spill problem or a dimension mismatch — both of which are fully fixable without rebuilding your data model. Here are the concrete next steps to take:

1. Click the formula cell and look at the blue spill preview. Identify every cell in the outlined range and delete any content, including formulas that return empty strings.
2. Unmerge any cells in the spill path. Merged cells and dynamic array spills cannot coexist.
3. Switch to CHOOSECOLS if you only need a subset of columns — it shrinks the spill footprint and makes your intent explicit.
4. Wrap with INDEX if you need exactly one column from the result and want to avoid spilling altogether.
5. Add IFERROR last, only after the formula is structurally sound, to handle genuine no-match cases gracefully for end users.