Two-way lookup will be used when you look up a value at the intersection corresponding to given row and column values. For instance, you have a table with multiple rows and column headers, and which value you want to lookup is the intersection at column 5-Jan and row AA-3 as below screenshot shown. This article will tell you how to use this two-way lookup formula in Excel.
To lookup the intersection of a given row and column, you need to follow below steps:
1. List the column and row headers you want to look up at, see screenshot:
2. In the cell you want to place the lookup value, select one formula from below:
(1) A1:A8 is the column range, L1 is the row header you want to look up in the column range, A1:I1 is the row range, L2 is the column header you want to find in the row range, A1:I8 is the whole range of the data, you can change as you need)
(2) The formula =SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8) will return 0 if the lookup value is text.
3. Press Enter key, the intersection corresponding of the specific column and row has been found.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.