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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube