How to Use INDEX and XMATCH for a Two-Way Excel Lookup
Question details
Extracting a specific value from a 2D table by matching both a row criterion (such as a company name) and a column criterion (such as an expense type).
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Retrieving intersection data from a complex dataset where both the lookup row and column can change dynamically.
- Observed behavior
- The user wants a clean, efficient formula alternative to avoid long, hard-to-maintain nested IF formulas for returning intersecting table values.
Ensure you are using a spreadsheet version that supports the modern XMATCH function (such as Microsoft 365, Excel 2021, or the latest version of WPS Spreadsheet).
Use INDEX with two XMATCH functions
This is the most efficient method to perform a two-way lookup by dynamically finding the row and column intersection without relying on complex, nested IF statements.
The INDEX function returns a value from a specified data range based on row and column numbers. By wrapping two XMATCH functions inside INDEX, you can dynamically pass the correct row and column numbers based on your exact lookup criteria.
Determine the main block of data where the intersecting values are stored (e.g., Tab2!$B$2:$E$100). Exclude the row headers and column headers from this array.
Use XMATCH to find the row position. Type `XMATCH(A2, Tab2!$A$2:$A$100, 0)`, where A2 is the company name you are looking for, and Tab2!$A$2:$A$100 is the column containing all company names.
Use XMATCH to find the column position. Type `XMATCH(B2, Tab2!$B$1:$E$1, 0)`, where B2 is the expense type, and Tab2!$B$1:$E$1 is the row containing all expense headers.
Combine the elements into your final formula: `=INDEX(Tab2!$B$2:$E$100, XMATCH(A2, Tab2!$A$2:$A$100, 0), XMATCH(B2, Tab2!$B$1:$E$1, 0))`, and press Enter to return the intersecting value.
Perform Two-Way Lookups Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including INDEX and XMATCH. You can execute complex two-way lookups with an intuitive interface and color-coded formula syntax.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the data table.
- 2. Select the target output cell: Click on the cell where you want the lookup result to appear.
- 3. Enter the formula: Type your `=INDEX(..., XMATCH(...), XMATCH(...))` formula. Utilize the auto-suggest dropdown to select functions and highlight ranges with your mouse easily.
- 4. Press Enter to calculate: Hit Enter to instantly calculate the formula and view the dynamically fetched intersection value.
Frequently Asked Questions
What is the difference between MATCH and XMATCH in this formula?
XMATCH is the modern, upgraded version of the MATCH function. It defaults to an exact match, meaning you technically don't have to specify the match mode as '0' in most cases, making your formulas shorter. It also performs faster on larger datasets.
Why am I getting an #N/A error with my two-way lookup?
An #N/A error occurs if either the row value or the column value cannot be found in their respective lookup arrays. Double-check for typos, extra spaces (trailing spaces), or mismatched data types between your lookup value and the data range.
Can I use this formula across different worksheets?
Yes, you can easily reference ranges on different sheets. As shown in the solution, you simply prepend the sheet name followed by an exclamation mark before the range reference, such as 'Tab2!$A$2:$A$100'.
