How to Perform a Two-Way Lookup in Excel?
Two-Way Lookup in Excel is a Excel function that retrieves a value from a specified range based on matching row and column criteria.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Struggling to find values at the intersection of rows and columns? This guide will show you how to use Excel's powerful lookup functions.
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 any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.