Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How To Perform Two Way Lookup In Excel

It happens sometimes when we need to lookup for a value from a table according to the row heading and column heading.

how to perform two way lookup in excel two dimension table

Let say, if you need to get the values from above table for a particular month and zone. 

Here in this situation you can use a two way lookup.

Performing a two-way lookup is all about getting a value from a two-dimensional table. That means you can get a value from any cell of a table.

And for this, you need to combine match function with vlookup. As you know, normal vlookup is only able to return a value by looking into a single column at a time.

The reason is, the col_index argument of vlookup is a static value. But if you combine match function with vlookup you can make it dynamic.

How to Use VLOOKUP + MATCH for a Two Way LOOKUP

Let's back to our example. We have a table with monthly sales quantity for four different zones of a company.

how to perform two way lookup in excel two dimension table

Now let’s say, your boss wants to check quantity for a zone for a particular month (For Ex: month Sep and zone North).

And, he doesn’t like to look out on the table for that.

So, here is the formula.

=VLOOKUP(month,table,MATCH(zone,heading_row,0),0)

how to perform two way lookup in excel formula in table

How does it work

As I already said, we need match function to make col_index dynamic. This formula works in two parts.

When you select zone, match function returns the position of the zone in the heading row.

how to perform two way lookup in excel match function for col index

And, then vlookup use that position to get value from that column.

how to perform two way lookup in excel how does it work

Every time, when you select month and zone it will return the value which is at the intersection of both row and column.

The thing is, you have a dynamic col_index which allows you get value from different columns with vlookup.

Samples File

Download this sample file from here to learn more.

Conclusion

Whenever you need to get value from a table [Matrix] you can simple use this combination of two functions to get that value.

It’s simple and easy to apply.

I hope you found this useful in your daily work. And, please don’t forget to share this tip with your friends.

Related Function

  1. VLOOKUP
  2. MATCH

Must Read Next

  1. Using INDEX and MATCH
  2. How to Combine VLOOKUP and MATCH in Excel 
  3. How to use VLOOKUP with Multiple Criteria in Excel
  4. How to use VLOOKUP Approximate Match in Excel
  5. Excel 3D Reference
  • ratanak

    Hi Puneet, awesome but prefer index and match

    • Puneet Gogia

      I got your point.

      But I found this method simple.

      I agree that INDEX-MATCH has multi use.