How to write 2 Way Lookup Formulas in Excel?

Situation

So far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way Lookups.

What is a 2 Way Lookup?

Lookup is when you find a value in one column and get the corresponding element from other columns. 2 Way Lookup is when you lookup value at the intersection corresponding to a given row & column values.

For example, assuming you have data like below, and you want to findout how much sales Joseph made in month of March, you are essentially doing a 2 way lookup.

Data:

Solution

While the problem may seem complicated, the solutions to two way lookups are surprisingly simple. In this post, I will review 4 different ways to write 2 way lookup formulas.

Keep this in mind:

• I use various named ranges in the below examples. valSalesPerson and valMonth refer to the name of sales person & month we are looking for. lstSalesPerson and lstMonth refer to the list of sales persons (first column) and list of months (first row). tblData has the sales figures for everyone.

Technique 1 – Using INDEX & MATCH Formulas

If you know the row number and column number in a given table, you can use INDEX formula to get the element at the intersection. And we can use MATCH formula to find the position of an value in a list. Combining both,

=INDEX(tblData,MATCH(valSalesPerson,lstSalesPerson,0),MATCH(valMonth,lstMonth,0)) is the formula we use to get the sales amount of valSalesPerson for valMonth.

Technique 2 – Using Named Ranges & Intersection (SPACE) Operator

Do you know that you can write =range1 range2 to get the value(s) at the intersection of range1 & range2? That is right, excel has an intersection operator. I will write more about this some other time. In the meanwhile, watch this short video to understand how you can use named ranges & intersection operator to perform 2 way lookups.

[Watch the video on Youtube]

However, you need to create named ranges for your data all the time. A simpler alternative is to use Excel 2007 Tables feature so the names are created for you automatically.

Technique 3 – Using SUMPRODUCT Formula

This is an absolute beauty. Thanks to Vipul for teaching me this superb trick.

You can use SUMPRODUCT to get the value at intersection like this: =SUMPRODUCT((lstSalesPerson=valSalesPerson)*(lstMonth=valMonth),tblData)

How does it work? Simple, When you write (lstSalesPerson=valSalesPerson)*(lstMonth=valMonth), SUMPRODUCT generates a lot of zeros and a one at the intersection. When you use tblData as second argument, the result is value at intersection.

Technique 4 – Using VLOOKUP with MATCH Formula

A much simpler and easy to remember alternative. You can simply write =VLOOKUP(valSalesPerson,$B$5:$N$17,MATCH(valMonth,lstMonth,0)+1,FALSE) to fetch the value for a corresponding month.

Review of 2 Way Lookup Techniques

Sample File

Download Example File – 2 Way Lookup Formulas in Excel

Go ahead and download the above file. It contains all the examples. Play with them to learn the 2 way lookup formulas better.

PS: Also download this beautiful example file that Matias has kindly shared with me. It shows how to use INDIRECT formula along with Excel Tables to do 2way lookups.

Special Thanks to

Vipul, Spotpuff, judgepax, Bryan for the tip. (Click on the name to see their tip)

Similar Tips

• Excel SUMPRODUCT Formula – What is it, how to use it & examples
• How to Lookup Values to the Left – Using INDEX + MATCH Formulas
• Range lookup – finding the corresponding range for a value