How to use VLOOKUP MATCH Combination in Excel

HomeExcel FormulasHow to use VLOOKUP MATCH Combination in Excel

The Combo of VLOOKUP and MATCH is like a superpower. Well, as we all know VLOOKUP is one of the most popular functions.

Right?

It can help you to quickly lookup 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 as a dynamic function. And this is where you need to combine VLOOKUP with MATCH. 

If you are working on multiple column data, it’s a pain to change its reference you have to do (change column number) this manually.

The best way to solve this problem is to use the MATCH function in VLOOKUP for col_index_number. Today in this post, I going to explain all the stuff you need to know to use this combo formula.

Problems With VLOOKUP

I have found the two biggest reasons to create a combination of these two functions.

1. Static Reference

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 the Feb month’s sale of “John”. The formula should be like this.

=VLOOKUP(“May”,A1:E13,2,0)

And 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 “Peter”?

You need to change the value in col_index_num because it’s not dynamic.

2. Add or Delete Columns

Now think in a different way. You need to add 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.

Why Match Function

Before you combine VLOOKUP and MATCH, you need to understand the match function and its work.

The basic use of MATCH is to find the cell number of the lookup value from a range.

Syntax: MATCH(lookup_value,lookup_array,[match_type])

It has mainly three arguments, lookup value, a range to lookup for the value, and the match type to specify an 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.

VLOOKUP and MATCH Together

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:

  • First of all, in a cell enter the month’s name, and in another cell enter the employee’s name.
  • After that, enter the below formula in the third cell.

=VLOOKUP(C15,A1:E13,MATCH(C16,A1:E1,0),0)

In the above formula, you have used VLOOKUP to lookup for the MAY month, and for the col_index_num argument, you have used the match function instead of a static value.

And in the 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 used that cell number to return the value.

In simple words, the MATCH function tells VLOOKUP the column number to get the value from.

Problems Solved?

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 whether those problems are solved or not.

1. Static Reference

You have referred employee name in the match function to get the column number for VLOOKUP.

So now, when you change the employee name in the cell, the match function will change the column number. And when you need to get the value for a different employee you just have to change the employee name in the cell.

This way, you have a dynamic col_index_number.

Finally, you don’t have to edit the formula again and again.

2. Add or Delete Columns

Before adding a new column John’s data was is in the 2nd column and the match function returned 2. And after you have inserted a new column john’s data is in the 3rd column and the match returned 3.

When you add a new column for a new employee the value in the formula is 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.

sample-file

Conclusion

Using VLOOKUP and MATCH functions together not only makes VLOOKUP dynamic but also makes it more powerful in terms of handling large data and, you can change col_index_num without actually editing it.

I hope you found this formula tip useful, and now, tell me one thing.

What do you think about this combination of VLOOKUP and MATCH?

Please share your views with me in the comment section, I’d love to hear from you, and make sure to share this tip with your friends, I’m sure they will appreciate it.

🔙 Excel Formulas

31 thoughts on “How to use VLOOKUP MATCH Combination in Excel”

  1. Thanks You. I have certainly visited your site teaching Excel more than a few numbers of occasions to learn many useful things.
    Maybe I missed something you have already written. But when you Add/Del columns, doesn’t the lookup_array parameter of VLOOKUP also need to be changed? Its of cause possible to have the range of lookup_array(reference to) to dynamically change as well. But then again, I would probably choose to use something more flexible than VLOOKUP.
    I hope that makes sense.

    Reply
  2. Hi Puneet,
    Thank you so much for your very good tutorial, but I can’t get it to work. I can from your sample file. Played around with that, including opening a second sheet, everything worked fine. But;- When I try to use on my own sheets, I can not get it to work, have tried for 2 days !
    Is there any chance of getting any help? really would appreciate it.
    Thank you.

    Reply
  3. Is there a reason why this formula does not work if the data is split between two sheets?

    I have tried your formula several times and still only returns the name and not the value

    =VLOOKUP(D16,’BASE SHEET’!D12:DK52,MATCH(‘Sheet2′!M8,’BASE SHEET Week 53’!L8:DK8,0),0)
    Sheet 2
    D16 Name
    Sheet 2 M8 = 30-09-2021

    Base Sheet
    Data range for Sales £ D12:DK52
    Date L8: DK8

    Reply
  4. In the 3rd sentence of the #1. Static Reference section you announce that you want to search for the Feb data but in the actual formula you typed May.

    Reply
  5. Thank you Puneet. I think I’m a pro user of Excel but I read your new tips every day, there is always something to learn from your great explanations.

    Reply
  6. This is a brilliant formula combination. I have prepared a worksheet in which I added drop-down lists in the lookup reference area; added a formatted results box with a dynamic header; included a formula display where I built the formula using named range and table references. I would upload a png image but I don’t see a way to do that.

    Reply
  7. Hi punnet,

    Thanks for your knowledge sharing. It’s really helpful… suppose if we have multiple columns which needs to be vlookup from other file to current file based on one column information, in such case we do manually change the no. of columns in each row. So is there any standard formula while copy n paste the formula from one cell to another cell (no. of columns should auto update)..

    Reply
  8. Hi Puneet,
    Question: In this lookup formula can lookup column be from right to left like Index-Match formula?

    Statement: The articles are simple and easy to understand.

    Reply
  9. Puneet,
    Thank you for your continuous support. Your methods of explanation is simple and effective.
    I regularly get your mails and they are/will be adding a lot value to my profession.

    Thanks again 🙂

    Reply
  10. Thanks a lot Puneet, your tips & tricks are always userful & efficient
    Note should be made that the same result can be obtained with the combination of Index() & Match() formulas (I think it’s one of your previous tips)…
    In fact when your lookup_array is dynamic the index + match are much better especially since you can apply them for a whole column and it does work beautifully
    ==
    See the latest I’ve actually written today: =INDEX(Month!$E:$E,MATCH($G2,Month!$A:$A,0)) where “month” is a sheet containing the lookup table this is as flexible as can be…

    Reply
  11. Hi,

    I want excel formula for remove duplicate and get unique values without any reference. Please suggest me…

    Thank you

    Reply
    • There’s no need for formula, you can either use conditional formatting for duplicate values or the DATA->Remove Duplicates from the main ribbon

      Reply
  12. These Tips and Explanations are simply Excellent and am Elated to join abd subscribe and love downloading the materials. Thankyou so much.
    Time to drive and upgrade my Excel Skills.
    ?
    With Regards,
    Manali

    Reply
  13. This post is written in a very easy to understand format. It is a great approach especially when you have to insert/delete columns in your lookup table.

    Reply

Leave a Comment