how to perform two way lookup in excelPerforming 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.

Let me show you an examples.

Example: Two Way Lookup

Here you 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 Jan and zone north).

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

All you have to do, create a two-way lookup for the table.

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 told you that 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 inexcel 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 & column.

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

Samples File

download sample file to learn how to perform tow way lookup in excel

Last Words

I found this method easy and simple.

Even I always use the same method to prepare data for my dynamic charts.

Over To You

So, what do you think?

Have you ever used this two-way lookup method before?

And, if you have any other for two-way lookup in excel, please share with me in the comment box.



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