It happens sometimes...
...when we need to lookup for a value from a table...
...according to the row heading and...
It happens sometimes when we need to lookup for a value from a table according to the row heading and column heading.
Let say, if 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 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 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 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 and here is the formula:
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.
And, then vlookup use that position to get value from that column.
Must Read Next
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.