How to Fix XLOOKUP #VALUE! Error for Two-Way Lookups in Excel
Question details
The user needs to perform a two-way lookup across a two-dimensional table without encountering a #VALUE! error.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Matching both a row criteria (like a block number) and a column header across a two-dimensional inspection table to find the intersecting value.
- Observed behavior
- A nested XLOOKUP formula returns a #VALUE! error instead of the expected cell value.
Verify that your lookup values exactly match the text or numbers in your row and column headers, and ensure that all defined data ranges have identical dimensions.
Replace XLOOKUP with INDEX and MATCH
Using INDEX and MATCH is widely considered the most reliable method for two-way (matrix) lookups, avoiding common array errors associated with complex nested functions.
The INDEX function returns a value from a specific row and column within a defined matrix. By using the MATCH function twice—once to find the correct row and once to find the correct column—you can seamlessly pinpoint the intersecting cell.
This method avoids the #VALUE! error because it evaluates the row and column coordinates independently before passing them to the INDEX function.
Click on the cell where you want the final lookup result to appear.
Start your formula by defining the core data matrix (excluding headers): =INDEX('Inspection Result'!$B$3:$Y$20,
Use MATCH to locate the row position based on your first criteria: MATCH(A2,'Inspection Result'!$A$3:$A$20,0),
Use a second MATCH to locate the column position based on your second criteria: MATCH(B1,'Inspection Result'!$B$1:$Y$1,0))
Press Enter to execute. The complete formula should look like: =INDEX('Inspection Result'!$B$3:$Y$20,MATCH(A2,'Inspection Result'!$A$3:$A$20,0),MATCH(B1,'Inspection Result'!$B$1:$Y$1,0))
Properly Structure a Nested XLOOKUP Formula
If you prefer using XLOOKUP, you must carefully nest one XLOOKUP inside another so that the inner formula returns an entire row or column array for the outer formula to process.
Effortlessly Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, INDEX, and MATCH, allowing you to perform complex two-way lookups with ease. Enjoy a familiar interface and robust error-checking tools.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing your two-dimensional data.
- 2. Select the output cell: Click the specific cell where you want to calculate the two-way lookup result.
- 3. Input the INDEX and MATCH formula: Type your combined =INDEX(..., MATCH(...), MATCH(...)) formula as outlined in the solution.
- 4. View the result: Press Enter to instantly retrieve your data without any #VALUE! errors.
Frequently Asked Questions
Why does my nested XLOOKUP return a #VALUE! error?
The #VALUE! error typically occurs when the lookup arrays (like your row or column headers) do not match the dimensions of your return array (the data matrix). If the inner XLOOKUP fails to output a contiguous array of the correct size, the outer XLOOKUP cannot process it.
Can I use XLOOKUP for multiple criteria in a single column?
Yes. If you have multiple criteria for a single dimension (like finding a specific First Name and Last Name in two separate columns), you can use the ampersand (&) to concatenate the lookup values and the lookup arrays: =XLOOKUP(val1&val2, array1&array2, return_array).
Is INDEX and MATCH better than XLOOKUP for matrix tables?
While XLOOKUP is highly versatile for single-dimension lookups, INDEX and MATCH is fundamentally designed for two-dimensional coordinate mapping. It is often considered more intuitive and less prone to dimension-mismatch errors when performing a two-way matrix lookup.
