Function Problems

How to Use Excel XLOOKUP for a Two-Way Approximate Match

Ayan Masood Oct 9, 2026 873 views

Question details

The user needs to perform a two-way lookup in Excel to find a value at the intersection of a row and column while selecting the nearest smaller match using XLOOKUP.

How to Use Excel XLOOKUP for a Two-Way Approximate Match

Product: Excel
Device & OS: not provided
Scenario: Setting up a two-way lookup formula with approximate matching for financial or data analysis tables.
Observed behavior: The formula aims to return the nearest smaller match, but incorrect setup can result in #SPILL! errors, or the formula displays as plain text if the cell is incorrectly formatted.

Before you start

Ensure your lookup arrays are sorted in ascending order, as this is required for approximate matching to function correctly when using the -1 match mode in XLOOKUP.

Solution 1Recommended

Use Nested XLOOKUP for a Two-Way Approximate Match

Combine two XLOOKUP functions to find the exact intersection of a row and column, using the -1 argument to return the nearest smaller item.

A two-way lookup requires an outer XLOOKUP to define the row array and an inner XLOOKUP to define the column array. Using the -1 match mode argument ensures the formula matches the exact value or the next smaller item.

Prepare the data arrays

Organize your data into a clear table and verify that both the row headers and column headers are sorted in ascending numerical or alphabetical order.

Write the inner XLOOKUP

Select the cell for your result and type the inner XLOOKUP to return the column array. Syntax: XLOOKUP(column_lookup_value, column_header_range, data_range, "", -1).

Nest inside the outer XLOOKUP

Wrap the inner formula with the outer XLOOKUP to target the row. Syntax: XLOOKUP(row_lookup_value, row_header_range, [Inner_XLOOKUP_Formula], "", -1).

Execute the calculation

Press Enter to calculate the formula. The result will display the exact intersecting value matching the closest smaller criteria.

Use Nested XLOOKUP for a Two-Way Approximate Match

Avoid #SPILL! Errors: Make sure there is enough blank space around your formula cell and verify that the lookup arrays are correctly oriented (rows vs. columns) to prevent #SPILL! errors.

Solution 2

Troubleshoot Formulas Displaying as Plain Text

Resolve issues where the XLOOKUP formula appears as text instead of executing the calculation.

Easily Perform Advanced Lookups in WPS Spreadsheet

WPS Spreadsheet fully supports advanced functions like XLOOKUP for two-way approximate matching, offering a seamless, efficient, and highly compatible data analysis experience.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the lookup tables.
2. Enter the nested XLOOKUP: Select your target cell and input the nested XLOOKUP formula using the -1 argument to perform the approximate match.
3. Ensure correct cell formatting: Verify the cell format is set to General from the Home tab so the formula calculates and displays the result properly.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in support for XLOOKUP, MATCH, and XMATCH functions for exact and approximate matchingLightweight application with a familiar, easy-to-navigate interface

Frequently Asked Questions

Why is my nested XLOOKUP formula returning a #SPILL! error?

A #SPILL! error occurs when the formula produces multiple results but the adjacent cells are not empty. It can also happen if the row and column lookup arrays are incorrectly oriented or if the array sizes do not match.

What does the -1 argument do in an XLOOKUP function?

The -1 argument specifies the match mode. It tells the XLOOKUP function to search for an exact match, and if one is not found, to return the next smaller item. Your lookup arrays must be sorted in ascending order for this feature to work correctly.

Why is my Excel formula showing as text instead of the calculated result?

This typically happens if the cell is formatted as 'Text' before entering the formula, or if the 'Show Formulas' option is enabled in the ribbon. Change the cell format to 'General', double-click the cell, and press Enter to trigger the calculation.

Can I use MATCH instead of XLOOKUP for approximate matches?

Yes, the traditional MATCH function can perform approximate matches. By default, if the third argument is omitted, MATCH behaves similarly to XMATCH or XLOOKUP with a -1 match mode, finding the largest value that is less than or equal to the lookup value.

Excel & Data Problems