 # How to Do Two Way Lookup In Microsoft Excel

In this article, you will learn how to do two way lookup.

We will use a combination of INDEX & MATCH functions.

Let us understand with an example:

• We have Car Sales Report for four States & four Sales Persons. • We want to find out the sales done by Clark in Connecticut State.
• Here, we will use Index function.
• Let us find out the row number (second argument of Index function) using Match function
• In cell B9 & B10; State & Sales Person are manually entered.
• In cell B12, the formula is =MATCH(B9,A4:A7,0) • To find the column number (third argument of Index function)
• In cell B13, the formula is =MATCH(B10,B3:E3,0) • Now we can easily use the index formula using formulas in cell B12 & B13
• The formula would be =INDEX(B4:E7,B12,B13) • The long index formula would be =INDEX(B4:E7,MATCH(B9,A4:A7,0),MATCH(B10,B3:E3,0)) • If you are new to Excel then, you can adopt step by step approach & later on you can go for long formulas.

In this way, you can learn two way lookup using Index & Match formulas.

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.