How to Perform a Two-Criteria Lookup in Excel Using INDEX and MATCH
Question details
Retrieve a specific financial value from a dataset by matching two distinct criteria: an account number in a row and a specific month in a column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Locating a specific data point at the intersection of a row and a column within a two-dimensional financial report or table.
- Observed behavior
- Looking for the correct formula structure to apply INDEX and MATCH functions to intersect a row and a column dynamically.
Ensure your dataset is organized in a clear grid format, with account numbers consistently in one column and months listed across a single header row without merged cells.
Use INDEX and MATCH for Two-Criteria Intersections
The most efficient way to look up a value at the intersection of a specific row and column is by nesting two MATCH functions inside an INDEX function.
The INDEX function returns the value of a cell in a specific row and column. By combining it with two MATCH functions, you can dynamically locate the correct row (e.g., account number) and the correct column (e.g., month) without manually counting rows or columns.
Click on the empty cell where you want the retrieved financial value to be displayed.
Type the core formula: =INDEX(data_range, MATCH(row_criteria, row_range, 0), MATCH(column_criteria, column_range, 0)).
For example, type =INDEX(Report!$B$2:$H$7, MATCH($E7, Report!$A$2:$A$7, 0), MATCH($F7, Report!$B$1:$H$1, 0)).
Press Enter to execute. The cell will now display the exact value intersecting the specified account number and month.
Alternative: Use VLOOKUP Combined with MATCH
If you are more comfortable using VLOOKUP, you can use the MATCH function to determine the column index number dynamically.
Easily Manage Advanced Financial Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, VLOOKUP, and XLOOKUP. It provides a highly intuitive environment to execute two-way lookups seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx financial report file.
- 2. Use the Formula tab: Click on your target cell and navigate to the Formulas tab to insert your lookup functions.
- 3. Follow the Formula Prompt: As you type =INDEX(, WPS will provide syntax tooltips to guide you through nesting the MATCH functions correctly.
Frequently Asked Questions
Why does my INDEX and MATCH formula return an #N/A error?
An #N/A error usually occurs when the MATCH function cannot find the exact lookup value. Check for hidden trailing spaces in your cells, mismatched data types (such as numbers formatted as text), and ensure the lookup value is actually present in the lookup array.
Does the order of the MATCH functions matter in the INDEX formula?
Yes. The syntax for the INDEX function is INDEX(array, row_num, column_num). Therefore, your first MATCH must be configured to find the row (e.g., the account number), and the second MATCH must find the column (e.g., the month).
Can I use XLOOKUP for a two-way lookup instead of INDEX and MATCH?
Yes. In supported spreadsheet software, you can nest one XLOOKUP inside another XLOOKUP to perform a two-way intersection lookup. The inner XLOOKUP returns the correct column array, and the outer XLOOKUP searches down the correct row array.
