How to Use INDEX and MATCH for Two-Way Lookup in Excel
Question details
The user needs to create an Excel formula using INDEX and MATCH to retrieve a value from a two-dimensional table based on multiple criteria.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating the required number of overnight stays from a calculator table by cross-referencing input values for working hours and a specific zone.
- Observed behavior
- The intended formula dynamically identifies the correct row and column headers and accurately outputs the intersecting numerical value.
Verify that your lookup table is formatted with clear, unique row and column headers, and locate the specific cells containing the input criteria you want to match.
Use standard INDEX and MATCH for a two-way lookup
Combine one INDEX function with two MATCH functions to locate and return the exact intersection of a row and a column.
The MATCH function finds the relative position of your row and column criteria within headers.
The INDEX function then retrieves the value at that specific visual coordinate within your data array.
Locate your lookup table range (e.g., H2:L1000), your row headers (e.g., G2:G1000), and column headers (e.g., H1:L1).
Click on the cell where you want the resulting calculation (the number of stays) to be displayed.
Type the formula: =INDEX(H2:L1000, MATCH(B4, G2:G1000, 0), MATCH(B5, H1:L1, 0)). Replace B4 with your hours input cell and B5 with your zone input cell.
Press Enter to execute the formula and return the value.
Use an array formula for evaluating conditional ranges
Use this advanced MATCH configuration if your row criterion falls within lower and upper numerical limits instead of being an exact match.
Easily Construct Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports robust lookup formulas like INDEX and MATCH, empowering you to build dynamic calculators effortlessly. You get a familiar formula interface with excellent compatibility.
- 1. Open your workbook: Launch WPS Office and open your .xlsx file in WPS Spreadsheet.
- 2. Select the destination cell: Click the cell designated for your calculation result.
- 3. Insert the function: Navigate to the Formulas tab, select Insert Function, and search for INDEX, or directly type your =INDEX(...) formula into the formula bar.
- 4. Press Enter: Hit Enter to instantly cross-reference your table and retrieve the accurate value.
Frequently Asked Questions
What does the 0 mean in the MATCH function?
The 0 instructs the MATCH function to find an exact match for the lookup value. If an exact match does not exist in the specified range, it returns an #N/A error.
Why is my INDEX MATCH formula returning a #REF! error?
A #REF! error usually occurs if your MATCH function returns a row or column number that sits outside the defined boundaries of your INDEX array. Always check that your header ranges perfectly align with the dimensions of your data body.
Can I use XLOOKUP instead of INDEX and MATCH for this?
Yes. In modern spreadsheet software updates, you can nest one XLOOKUP function inside another to perform a two-way lookup, which often results in simpler, more readable syntax compared to INDEX and MATCH.
