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.

img1

 

  • 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)

 

img2

 

  • To find the column number (third argument of Index function)
  • In cell B13, the formula is =MATCH(B10,B3:E3,0)

img3

 

  • Now we can easily use the index formula using formulas in cell B12 & B13
  • The formula would be =INDEX(B4:E7,B12,B13)

 

img4

 

  • The long index formula would be =INDEX(B4:E7,MATCH(B9,A4:A7,0),MATCH(B10,B3:E3,0))

img5

 

  • 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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube