Formula Errors

How to Fix XLOOKUP Multiple Columns #VALUE Error in Excel

Phi Hung Vo Oct 12, 2026 871 views

Question details

The user is encountering a #VALUE! error when attempting to return multiple columns using the XLOOKUP function.

How to Fix XLOOKUP Multiple Columns #VALUE Error in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using an XLOOKUP formula to retrieve data across multiple columns simultaneously.
Observed behavior: The formula returns a #VALUE! error instead of outputting the results into adjacent columns, often due to version incompatibility or misconfigured output ranges.

Before you start

Ensure that the cells adjacent to your target formula cell are completely empty, and verify whether your version of Excel supports dynamic array spilling.

Solution 1Recommended

Clear the Spill Range for Dynamic Arrays

Use this method if you have Microsoft 365 or Excel 2021 where XLOOKUP natively supports dynamic array spilling.

In modern versions of Excel, functions that return multiple values will automatically spill those results into adjacent blank cells. If XLOOKUP is returning an error instead of multiple columns, it is likely that the spill range is blocked or the formula was entered incorrectly.

Select the target cell

Click on the single cell where you want the first column of your XLOOKUP result to appear.

Enter the XLOOKUP formula

Type your formula referencing the lookup value, lookup array, and a multi-column return array (e.g., =XLOOKUP(A3,I2:I10,O2:P10)).

Verify adjacent cells

Make sure all cells to the right of your formula cell (where the additional columns will populate) are completely empty.

Execute the formula

Press Enter. The results will automatically fill the adjacent columns.

Clear the Spill Range for Dynamic Arrays

Empty Cells Requirement: Any hidden characters, spaces, or merged formatting in the adjacent cells will block the array from spilling, which can result in a #SPILL! or #VALUE! error.

Solution 2

Use a Legacy Array Formula (Ctrl+Shift+Enter)

Apply this solution if you are using an older version of Excel that supports XLOOKUP but does not natively support dynamic array spilling.

Solution 3

Return Each Column Separately

Use this approach if you want to avoid array formulas entirely or if you need to place the output columns in non-adjacent locations.

Advanced Spreadsheet Tool

Easily Use XLOOKUP and Dynamic Arrays with WPS Office

WPS Spreadsheet fully supports modern array functions like XLOOKUP, automatically spilling results across multiple columns without generating complex #VALUE! errors.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data.
2. Enter the multi-column XLOOKUP formula: Select the target cell and type your XLOOKUP formula with a multi-column return array.
3. Press Enter to spill results: Simply press Enter. WPS Spreadsheet will automatically recognize the dynamic array and spill the results into the adjacent columns perfectly.

100% compatible with Microsoft Excel formulas and functionsNatively supports modern dynamic arrays including XLOOKUP, FILTER, and UNIQUELightweight application that runs smoothly on all hardwareFree to use with an intuitive, tabbed interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #NAME? error instead of #VALUE! with XLOOKUP?

A #NAME? error generally means your version of Excel does not support the XLOOKUP function at all (such as Excel 2016 or 2019). You will need to upgrade your software, switch to a compatible alternative like WPS Office, or use a combination of INDEX and MATCH instead.

Why does my XLOOKUP formula return a #SPILL! error?

The #SPILL! error indicates that the formula successfully calculated multiple columns, but there is existing data, text, or merged formatting in the adjacent output range blocking the results from displaying.

Can XLOOKUP return multiple rows instead of columns?

Yes, XLOOKUP can return multiple rows if you set your return array to cover multiple rows. The dynamic array behavior and spill rules remain exactly the same as returning multiple columns.

Excel & Data Problems