Function Problems

How to Use INDEX-MATCH or XLOOKUP Across a Full Excel Range

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to retrieve a specific data point from a two-dimensional Excel range by matching both row and column criteria simultaneously.

Product: Excel
Device & OS: not provided
Scenario: Performing a two-way lookup across a full data matrix to find an intersecting value, such as a specific sales figure for a specific quarter.
Observed behavior: To successfully return the correct value at the intersection, the formulas must accurately map both vertical and horizontal criteria rather than executing a standard single-column lookup.

Before you start

Ensure your dataset is organized with clearly defined row and column headers, and verify that the lookup arrays have the exact same dimensions as your data array to prevent structural errors.

Solution 1Recommended

Use INDEX and MATCH for a Two-Way Lookup

The most robust and universally compatible method to retrieve a value at the exact intersection of a specific row and column.

The INDEX function can return a value from a 2D range by specifying both the row and column coordinates. By nesting two MATCH functions inside INDEX, you can dynamically locate the correct row number and column number based on your lookup criteria.

Define your data array

Identify the full data block containing the values you want to retrieve, excluding headers (for example, C2:Z23). This will be the first argument in your INDEX function.

Find the row index

Use a MATCH function for the row criteria: MATCH(row_lookup_value, row_headers_range, 0). Ensure the matching type is 0 for an exact match.

Find the column index

Use a second MATCH function for the column criteria: MATCH(column_lookup_value, column_headers_range, 0).

Combine into the final formula

Assemble the pieces into the complete syntax: =INDEX(data_array, MATCH(row_lookup_value, row_headers_range, 0), MATCH(column_lookup_value, column_headers_range, 0)).

Check Array Dimensions: If you receive a #REF! error, double-check that your header ranges encompass the exact same number of rows and columns as your data array.

Solution 2

Use Nested XLOOKUP for a Two-Way Lookup

A modern and streamlined alternative to INDEX-MATCH for users with newer versions of Excel.

Powerful Spreadsheet Functions

Perform Advanced Lookups Seamlessly in WPS Office

WPS Office fully supports advanced spreadsheet functions, including INDEX, MATCH, and XLOOKUP, allowing you to perform complex data analysis and dynamic two-way lookups effortlessly.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheets and open the .xlsx document containing your 2D data array.
2. Select the target cell: Click on the cell where you want the two-way lookup result to be displayed.
3. Input your lookup formula: Type out your =INDEX(...) or nested =XLOOKUP(...) formula exactly as you would use it in Excel.
4. Execute and verify: Press Enter. WPS Spreadsheets will instantly process the matrix logic and return the correct intersecting data point.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Lightning-fast calculation processing even for large 2D data arrays.Intuitive formula builder and syntax highlighting to prevent nesting errors.Free alternative to Microsoft Office with a comprehensive professional toolset.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my two-way lookup returning an #N/A error?

An #N/A error means that one or both of the MATCH or XLOOKUP criteria could not find an exact match in the header ranges. Check for trailing spaces, spelling discrepancies, or mismatched data types between your lookup value and the headers.

Can I perform a two-way lookup with VLOOKUP?

Yes, you can combine VLOOKUP with a MATCH function to achieve this. The MATCH function replaces the static column index number in the VLOOKUP formula (e.g., =VLOOKUP(row_lookup_value, entire_table_range, MATCH(column_lookup_value, column_headers_range, 0), FALSE)). However, INDEX-MATCH and XLOOKUP are generally considered superior and less prone to breaking if columns are inserted.

How can I share my complex formula issue for better troubleshooting?

Screenshots are often insufficient for diagnosing complex 2D lookup failures. It is best to upload a sample workbook to a cloud service like OneDrive or Google Drive, post the shareable link, and explicitly state your expected results and matching logic.

Excel & Data Problems