How to Perform a Two-Way Lookup in Excel?
Struggling to find values at the intersection of rows and columns? This guide will show you how to use Excel's powerful lookup functions.
Formula Genius can generate a draft from a plain-English prompt. Supported formulas can be checked with sample cases; review the coverage on the result.
The formula
"Look up a value at the intersection of a matching row and column, like a two-dimensional table lookup"
=INDEX(A1:D10, MATCH(row_value, A1:A10, 0), MATCH(column_value, A1:D1, 0))This formula retrieves a value from a specified range based on matching row and column criteria.
Step-by-step breakdown
- Use MATCH to find the position of the row value in the row header.
- Use MATCH to find the position of the column value in the column header.
- Use INDEX to return the value at the intersection of the found row and column positions.
- Ensure that the ranges for row and column headers are correctly defined.
Edge cases & warnings
- The row or column value does not exist in the headers.
- The data range includes blank cells which may affect the lookup.
- Using non-unique values in headers can lead to incorrect results.
Examples
"Row header: 'Product A', Column header: 'Q1'"
$200"Row header: 'Product B', Column header: 'Q2'"
$300Frequently asked questions
Can I use this formula with non-contiguous ranges?
No, the ranges must be contiguous for the formula to work.
What if my headers are not unique?
Non-unique headers can lead to incorrect lookups; ensure headers are unique.
Is there a simpler way to do this in newer Excel versions?
Yes, you can use the XLOOKUP function for a more straightforward approach.
Can't find what you need?
Describe a formula in plain English. Formula Genius drafts and explains it, and can check supported formulas on sample cases.