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.
10000+ Copies Already Downloaded
No doubt, VLOOKUP is one of the most popular excel functions. It’s simple to use and easy to understand. It’s can help you to quickly lookup for a value in a column.
But, when you use it more and more you will realize that there are some problems with it. And, the biggest problem is it’s not dynamic.
In VLOOKUP, col_index_no is a static value which is the reason VLOOKUP doesn’t work like a dynamic function.
If you are working on a multiple column data, it’s a pain to change its reference because you have to do this manually.
The best way to solve this problem is to use MATCH Function in VLOOKUP for col_index_number.
So today, in this post, you will learn to combine VLOOKUP and MATCH.
Just look at the below data table, where you have 12-month sales for four different employees.
Now, let’s say you want to look up for the Feb month’s sale of “John”. The formula should be like this.
In this formula, you have mentioned 2 as the col_index_num because John’s sale is in the second column. But what will you do if your boss tells you to get the sales value for the “Peter”?
You need to change the value in col_index_num because it’s not dynamic.
Now think in a different way.
You have added a new column for a new employee just before John’s column. And here, John’s column number is 3 and your formula result is incorrect.
Again here, because col_index_num is a static value you need to change it manually from 2 to 3 and again if you need something else.
At this point, you are clear about one thing that you need to make col_index_num dynamic. And for this, the best way is to replace it with the MATCH function.
Before you combine VLOOKUP and MATCH, you need to understand match function and its working.
The basic use of MATCH function is to find the cell number of the lookup value from a range.
It has mainly three arguments, lookup value, a range to lookup for the value, and the match type to specify exact match or an approximate match.
For example, in the below data I am lookup for the name "John" with the match function from a heading row.
And, it has returned 2 in the result because the name is in the 2nd cell of the row.
Now, it’s time to put VLOOKUP and MATCH together. So, let’s continue with our previous example.
First of all, let’s create a formula by using both of the functions and then we’ll understand how these two work together.
Steps to create this combo formula:
In the above formula, you have used VLOOKUP to lookup for the MAY month and for the col_index_num argument you have used match function instead of a static value.
And, in match function, you have used “John” (employee name) for the lookup value.
Here match function has returned the cell number for the “John” from the above row. After that, VLOOKUP has used that cell number to return the value.
In simple words, match function tells VLOOKUP the column number to get the value from.
Above you have learned about two different problems which are because of static col_index_num. And, for this, you have combined VLOOKUP and MATCH.
Now, we need to check that those problems are solved or not.
You have referred employee name in match function to get the column number for VLOOKUP. So, now when you change employee name in the cell, match function will change the column number.
So, when you need to get the value for a different employee you just have to change the employee name in the cell. And, in this way, you have a dynamic col_index_number.
You don’t have to edit formula again and again.
Before adding a new column John's data was is in the 2nd column and match function return 2. And, after you have inserted a new column john's data is in the 3rd column and match returned 3.
When you add a new column for a new employee the value in the formula not changed because the match function updates its value.
In this way, you’ll always get the correct column number even when you insert/delete any column. Match function will return the right column number.
Using VLOOKUP and MATCH functions together not only makes VLOOKUP dynamic but also makes it more powerful in term of handling large data.
And, you can change col_index_num without actually editing it.
I hope you you found this formula tip useful.
So, what's do you think about this combination of VLOOKUP and MATCH? Please share with me in the comment section. I would love to hear from you.