How to Return a Value from an Excel Table Using Two Criteria
Question details
The user needs to retrieve a specific data point from a two-dimensional table by matching a row criteria and a column criteria simultaneously.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up a value at the intersection of a specific row (e.g., Frequency) and a specific column (e.g., Consequence) within a matrix dataset.
- Observed behavior
- To successfully extract the intersection value using appropriate lookup functions without manually searching through the table.
Ensure your two-dimensional table has distinct, unmerged row headers and column headers, and verify that the lookup criteria exactly match the text or values in those headers.
Use INDEX and MATCH for a Two-Way Lookup
This is the most standard, backward-compatible method that works perfectly across all versions of Excel to perform a two-dimensional lookup.
The INDEX function retrieves a value from a given range, while MATCH finds the relative position of your criteria. By combining one INDEX with two MATCH functions, you can pinpoint the exact row and column intersection.
Click on the empty cell where you want the final lookup value to appear.
Start your formula by typing =INDEX(B2:F6, which defines the core data range containing the values you want to return (excluding headers).
Add the row position by typing MATCH(B9, A2:A6, 0), where B9 is your row lookup value and A2:A6 is the column containing your row headers.
Add the column position by typing MATCH(B10, B7:F7, 0), where B10 is your column lookup value and B7:F7 is the row containing your column headers.
Close the parentheses to finish the formula: =INDEX(B2:F6, MATCH(B9, A2:A6, 0), MATCH(B10, B7:F7, 0)). Press Enter to retrieve the matching value.
Use Nested XLOOKUP Functions
A modernized approach available in newer versions of Excel, making the syntax easier to read and removing the need to specify match types.
Use Nested FILTER Functions
An alternative dynamic array formula approach that filters the grid row-by-row and then column-by-column.
Perform Advanced Two-Way Lookups Easily in WPS Office
WPS Spreadsheet fully supports complex array formulas, including INDEX/MATCH, XLOOKUP, and FILTER. It's a highly capable tool for analyzing two-dimensional tables seamlessly without changing your usual Excel habits.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your two-dimensional table.
- 2. Select a cell for your result: Click the cell where you want the lookup value to be generated.
- 3. Insert the lookup formula: Navigate to the Formulas tab, select Insert Function, and choose INDEX (or simply type the INDEX/MATCH formula directly into the formula bar).
- 4. Confirm the result: Press Enter to execute the formula and instantly retrieve the targeted intersection value.
Frequently Asked Questions
Why is my INDEX and MATCH formula returning an #N/A error?
An #N/A error usually occurs when the MATCH function cannot find the lookup criteria in the header ranges. Check for hidden trailing spaces in your cells, ensure you are using exact match (0 as the third argument in MATCH), and confirm that the dimensions of your lookup arrays match the dimensions of your INDEX array.
Can I use VLOOKUP instead of INDEX and MATCH for a two-way lookup?
Yes. You can use a combination of VLOOKUP and MATCH. The VLOOKUP function searches for the row criteria, and the MATCH function replaces the static column index number to dynamically find the correct column. The formula syntax would look like: =VLOOKUP(row_criteria, full_table_range, MATCH(column_criteria, header_row_range, 0), FALSE).
Are XLOOKUP and FILTER functions available in older versions of Excel?
No. XLOOKUP and FILTER are Dynamic Array functions introduced in newer versions (such as Microsoft 365 and Office 2021). If you are sharing your workbook with users who have older versions (like Excel 2016 or 2019), it is highly recommended to use the INDEX and MATCH method to ensure compatibility.
Does it matter which criteria I match first in the INDEX formula?
Yes. The syntax for the INDEX function is INDEX(array, row_num, [column_num]). The first MATCH function you insert must evaluate the row position, and the second MATCH function must evaluate the column position. Reversing them will return an incorrect value or an error.
