Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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:

=INDEX(A1:I8,MATCH(L1,A1:A8,0),MATCH(L2,A1:I1,0))

=SUMPRODUCT((A1:A8=L1)*(A1:I1=L2),A1:I8)

=VLOOKUP(L1,$A$1:$I$8,MATCH(L2,B1:I1,0)+1,FALSE)

Tips:

(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.
  • No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
  • Become an Excel expert in 3 minutes, Complicated and repeated operations can be done in seconds, 
  • Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
  • 110,000 highly effective people and 300+ world-renowned companies' choice.
  • 60-day full features free trial. 60-day money back guarantees. 2 years of free upgrade and support.

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

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.