How to use two-way lookup formula in Excel?
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.
Reading Layout（cross highlight a cell )
Enable you to do smarter, faster and better
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.