How to write 2 Way Lookup Formulas in Excel?
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.
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.
valMonthrefer to the name of sales person & month we are looking for.
lstMonthrefer to the list of sales persons (first column) and list of months (first row).
tblDatahas 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
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
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.
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:
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
Go ahead and download the above file. It contains all the examples. Play with them to learn the 2 way lookup formulas better.
Special Thanks to
- 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
Leave a Reply
|Using Lookup Formulas with Excel Tables [Video]||Getting the 2nd matching value from a list using VLOOKUP formula|