It happens sometimes when we need to lookup up a value from a table according to the row heading and column heading.
Let’s say you need to get the values from the 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 the match function with VLOOKUP.
As you know, a normal VLOOKUP is only able to return a value by looking into a single column at a time.
The reason is, that the col_index argument of VLOOKUP is a static value. But if you combine the match function with VLOOKUP you can make it dynamic.
How to Use VLOOKUP + MATCH for a Two-Way LOOKUP
Let’s come back to our example. We have a table with monthly sales quantity for four different zones of a company.
Now let’s say, your boss wants to check the quantity for a zone for a particular month (For Ex: the month Sep and zone North). And, he doesn’t like to look out on the table for that, and here is the formula:
=VLOOKUP(month,table,MATCH(zone,heading_row,0),0)
How it Works
As I already said, we need a match function to make col_index dynamic. This formula works in two parts. When you select the zone, the match function returns the position of the zone in the heading row.
And, then VLOOKUP uses that position to get value from that column.
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 to get values from different columns with VLOOKUP.
lot of things to learn in excel
thanks for your sincere efforts
I need a solution. Please help.
I have list of priorities. P1 P2 P3 P4.
Resolution time is
P1 – 2 hours
P2 – 4 hours
P3 – 1 Business day
P4 – 5 Business day
P4 Priority
Start time is
22-03-2022 10:50:00
Resolved time is
24-03-2022 11:30:00
Help me calculate SLA met or not met.
why not use unpivot in power query?
Hi Puneet, awesome but prefer index and match
I got your point.
But I found this method simple.
I agree that INDEX-MATCH has multi use.
This combination is great and will be extremely useful. Thank you for explaining and sharing all your guides.