
Log in


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.
doc two way lookup 1

Two-way lookup with formulas

Reading Layout（cross highlight a cell )

doc layout

Enable you to do smarter, faster and better

Kutools - the best office productivity tool brings 300 advanced features to Excel. Details... Download...

Office Tab - brings tabbed interface to Excel and Word, make your work much easier. Details...Download...

arrow blue right bubble Two-way lookup with formulas

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:
doc two way lookup 2

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.
doc two way lookup 3

3. Press Enter key, the intersection corresponding of the specific column and row has been found.

doc download 1

Excel Productivity Tools

Kutools for Excel - The Best Office Productivity Tool

  • Brings 300 powerful advanced features to Excel, enable you to do smarter, faster and better.

Office Tab - brings tabbed interface to Office, make your work much easier.

Say something here...
symbols left.
You are guest ( Sign Up? )

Be the first to comment.