VLOOKUP & MATCH - A Dynamic Duo | Excel Campus
51

VLOOKUP & MATCH – A Dynamic Duo

This post provides an explanation of how to use the VLOOKUP and MATCH functions to give you better control over how the column index number changes.  This is often referred to as a dynamic formula.  You will learn how this dynamic duo can help prevent errors and improve your VLOOKUP formulas.

Dynamic Duo VLOOKUP MATCH Excel

The Dynamic Duo

We are going to learn how the MATCH function can be used inside the VLOOKUP function.  This helps protect the VLOOKUP from returning errors when changes are made to the workbook.

Problems with VLOOKUPs

The VLOOKUP is a very useful function, but it doesn’t respond to change very well.  Have you ever noticed that if you add or delete columns in an area that the VLOOKUP refers to, the result can return an error or incorrect result?

VLOOKUP ERROR

This is usually due to the fact that we have specified the column number as a static number in the 3rd argument of the VLOOKUP argument.

VLOOKUP Static Column Number

The Starbucks Menu Example

We can use the Starbucks menu VLOOKUP example to help explain this issue.

Excel Vlookup Explained Column Index Number

In that example we wanted to return the price for the size Grande, which was in column 3 of the menu.  We put a “3” in the column index argument in the VLOOKUP formula to reference the Grande column (col C).

But what if Starbucks decided to add a new size to the menu?

Let’s say they decide to add a size “Short” to the menu, and put it to the left of the size Tall.

In our spreadsheet example, we would need to insert a column after column A for the new size.  This change means that the size Grande is now in column 4 (col D).

VLOOKUP Starbucks Menu Wrong Column Reference Add Col

However, our VLOOKUP still references column 3.  Excel does NOT update the formula when a column is inserted or deleted.

Therefore, the formula is now returning the wrong result.

This is a problem!  But fortunately for us, VLOOKUP has a side-kick named MATCH that will save the day.

MATCH to the Rescue

We first need to learn how the MATCH function works.

The MATCH function is very similar to the VLOOKUP.  It’s job is to look through a range of cells and find a match.

The difference is that it returns a row or column number.

Difference Between VLOOKUP and MATCH

So why the Batman and Robin reference?

I like to think of MATCH as VLOOKUP’s little brother, or side-kick.  They do very similar jobs, but MATCH packs a smaller punch.

Batman is the VLOOKUP and returns a big value in the form of a cell’s value.  This can be text or a number.

Robin is the MATCH and returns a smaller value in the form of a number.

Dynamic Duo Vlookup Value Match Number

Hopefully this will help you remember and distinguish the difference between the two.

The Match Function Components

The MATCH function’s arguments are similar to the VLOOKUP’s.  The following image shows the Excel definition of the VLOOKUP function, and then my simple definition.  This simple definition just makes it easier for me to remember the three arguments.

Match Simple Definition

I explain this simple definition below as we walk through an example of creating a VLOOKUP formula.

The MATCH Example

Let’s look at the Starbucks menu example again to learn MATCH.  In this example we want to use the MATCH function to return the column number for the size Grande.

I’ll explain why later, but for now we just want to answer the question:

“What is the column number for the size Grande?”

We will answer this question using the MATCH function.  You can download the file to follow along.

VLOOKUP MATCH Explained At Starbucks.xlsx (15.2 KB)

1. lookup_value – This is the what argument.

Match Explained Step 1 - What

In the first argument we tell the VLOOKUP what we are looking for.  In this example we are looking for “Grande” in row 1.  I have entered the text “Grande” in cell A14, so we can make a reference to cell A14 in the formula.

2. lookup_array – This is the where argument.

Match Explained Step 2 - Where

Here we need to tell MATCH where to look for the word “Grande”.  I selected the range $A$1:$D$1, which contains the column header names.  MATCH will look through row 1 from left-to-right until it finds a match.

You can also specify a column for this argument.  In that case MATCH would look down the column from top-to-bottom to find a match.

3. [match_type] – The match type tells MATCH how precise to be with the lookup.

Match Explained Step 3 - Match Type

Here we specify if the function should look for an exact MATCH, or a value that is less than or greater than the lookup_value.

In this example we will use “Exact match”, which is represented by putting a 0 (zero) in the third argument.  When your MATCH is looking up text you will generally want to look for an exact match.

If you are looking up numbers with the MATCH function then the “Less than” or “Greater than” match types can be very useful.

The Result – Grande is in column 3.

Match Explained Step 4 - Result

The MATCH function was able to lookup the word “Grande” in row 1 and return the value of 3 in cell B14.

Now let’s see how the MATCH function can be a little more dynamic.  In the following screenshot I inserted a column to the left of column B with a new size, “Short”.

Match Explained Add New Column

You will notice that the result of the MATCH formula changed to “4”.  This is because the word “Grande” is now in the 4th column of the lookup_array (A1:E1).

You are probably starting to see how this could help our original problem of the VLOOKUP returning the wrong result.

VLOOKUP & MATCH – The Dynamic Duo

Now let’s see how we can combine these two to create a dynamic formula.

We can use the MATCH function inside the VLOOKUP function.  Instead of specifying the column number with a static number “3”, we will use the MATCH function in its place.

Since the MATCH returns a number, it is a perfect fit for the VLOOKUP’s col_index_num argument.

The following example illustrates this.  You can follow along in the ‘VLOOKUP & MATCH Example’ sheet in the example file.

Vlookup Match Dynamic Formula Example Starbucks Menu

The original formula looked like the following:

=VLOOKUP(A14,$A$2:$E$10,4,FALSE)

In the new formula I replace the “4” with the MATCH formula:

=VLOOKUP(A14,$A$2:$E$10,MATCH(B14,$A$1:$E$1,0),FALSE)

The MATCH formula references cell B14, which contains the word “Grande”.  The formula looks up the word Grande in row 1 and returns a 4 as the result because Grande is in the fourth column of the range A1:E1.

The VLOOKUP formula is now much more dynamic with MATCH included.  We can add or delete columns to the menu (table), and the VLOOKUP will still return the price for the size that is specified in cell B14.

You could also change either the item in cell A14 or the size in cell B14 to return different prices in cell C14.

This makes the formula very flexible, and easier to reuse in other places in the workbook.

Conclusion

I hope this post has helped you understand how the VLOOKUP and MATCH functions can work together to be a dynamic duo.  This team of functions will help prevent errors in your formulas.  It will also help you create financial models that are more flexible for data retrieval and scenario analysis.

Series: The Lookup Functions Explained

This is the 2nd post in a series about the most commonly used lookup functions in Excel.

  1. In the first post I explained the VLOOKUP function at Starbucks.
  2. In this second post I explained how to make lookup formulas more dynamic with the VLOOKUP & MATCH functions.
  3. In the third post I explain the INDEX function.

Please leave a comment below with any questions or comments.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 51 comments
Tina Gitova - July 10, 2017

The possibility of match being a horizontal array never occured to me. Thank you for this great post and amazing example!!!

Reply
varun katre - June 26, 2017

how to increment the sheet values in match formula without disturbing the range. I tried using indirect function, but it doesn’t work.

Reply
Rob - June 12, 2017

This Is killing me: I did “MIN Amount of Rate But when is 0% It needs to default to Max Rate?

Max Rate MinRate
3.05% 0.00%
2.45% 0.00%
3.45% 1.50%

Thank You so much!

Reply
Jitendra - April 12, 2017

Thanks Jon! for detailed explanation with example. It is nice to have people like you on this planet who are willing to share their knowledge with others without any expectations. Great work! Almighty bless you.

Reply
Khaleel - March 22, 2017

Your creative way of explaining the complex things with day to day life example is really good. Thank you very much for such a nice explanation. Good work. Please continue this work.

Reply
Nicole - March 22, 2017

Thank you!!!! This was clear, direct and so very helpful. I really appreciate you taking the time to publish this.

Reply
Rajnish - March 2, 2017

Dear Mr.Jon,

Your explanation of Vlookup and Match function is nice. Giving the functions a name “Dynamic Duo” makes an imprint in memory and hard to forget.The analogy you use to explain things is too good. I have not seen anyone, with so many of Excel tuitors available in web, using your methodology.
Thank you for your effort in making the the learners awesome.

Reply
Mauricio - February 20, 2017

Hello, from sunny Costa Rica. Thank you for taking the time to explain in detail the dynamic functions. It is always nice to learn something new, and Excel is not my strong application although I use it to add, subtract and do small calculations. Same MATCH function can be used to match “Caffe Mocha” down A column, right?

The formula as you explained it but referring to A5(Caffe Mocha) and C1 (Grande) is (in Spanish Excel): =BUSCARV($A5,$A$2:$D$10,COINCIDIR(C$1,$A$1:$D$1,0),FALSO) works fine. If I replace $A5 with a MATCH function : BUSCARV(COINCIDIR($A5,$A$1:$A$10,0),$A$2:$D$10,COINCIDIR(C$1,$A$1:$D$1,0),FALSO), doesn’t work. What am I doing wrong?

Reply
Debbie - February 10, 2017

Hi Jon,
Your explanation is very clear, thank you so much. Now that i understand the match and index functions, I am not sure which combination to use for my workbook.
I have a vlookup but I don’t think I am using it correctly and I also want to change it so I’m not so limited.

Here is the current formula: =VLOOKUP(A2,DUTY_TABLE,2,FALSE)*(E2), where A2 is a word, such as Cement, that will determine the % of duty from the duty table.Then multiply this % by the $ in cell E2.
A B
Cement 9.0%
Synthetic 6.5%
Seamless 20.5%

Now i need to make cell E2 the sum of 2 columns with the first column being the price to assess duty and the 2nd column a commission that is 7% of the first column. EX. E2=$14.98, 1st new column is $14.00, 2nd new column is $.98

Can you give me some guidance. I am in over my head and determined to learn how to do this.
Thank you so much, Debbiejj

Reply
    Jon Acampora - February 15, 2017

    Hi Debbie,
    I’m not sure I fully understand your question. Your VLOOKUP formula looks good. You can multiple the results of 2 vlookup formulas together if that is what you are trying to do. You might also need a SUMIF or SUMIFS formula to sum a column based on some criteria. I hope that helps.

    Reply
Debbie - February 10, 2017

Hi Jon,
Your explanation is very clear, thank you so much. Now that i understand the match and index functions, I am not sure which combination to use for my workbook.
I have a vlookup but I don’t think I am using it correctly and I also want to change it so I’m not so limited.

Here is the current formula: =VLOOKUP(A2,DUTY_TABLE,2,FALSE)*(E2), where A2 is a word that will determine the % of duty from the duty table. the duty table is

Cement 9.0%
Wos 10.5%
Synthetic 6.5%
Seamless 20.5%

Reply
Jay - February 4, 2017

Hi Jon,

Your are excellent. 🙂

Thanks for sharing such a valuable information with us.

Can I have your what’s up number ?

Regards,
Jay

Reply
Michael - November 25, 2016

Hi, Jon.
I’m having a similar issue, but with HLOOKUP. I add a column to the sheet with the formula and it returns a nonsensical value (i.e. not even in the lookup_array, much less on the sheet). I made this array static and since it’s HLOOKUP I’m looking for a row value (can’t understand why adding a column would affect the calculation!!).

Formula in CJ2:
=(CH2-HLOOKUP(CONCATENATE(LEFT(CC2,6),” “,RIGHT(CC2,2)),’Program Summary’!$D$201:$DO$243,6))^2

Strangely, when I place this formula in another sheet (and add a column), the return value stays the same and is the value I am hoping to return.

If it’s of any help, the value in CC2 is part of a PowerQuery Table. The value in CH2 is a return value from a VLOOKUP in a table on the same sheet.

Thanks for any ideas!

Reply
    Jon Acampora - December 1, 2016

    Hi Michael,
    It’s hard to diagnose without seeing the workbook. I would evaluate the formula piece by piece to see where the error is. Remove everything but the HLOOKUP and make sure it is returning the correct value before building the rest of the formula.

    Reply
      Michael Moore - December 2, 2016

      Hi, Jon.

      I appreciate the help. Strangely enough, after opening the workbook again the next day, the glitch stopped. Crossing my fingers it’s a one time thing. 🙂

      Thanks again!

      Reply
Bijay - November 24, 2016

WoW!!!!!!Great Explan.. I clearly understand the aboves as step by step as your funny method.. Its really awasome & think it help to others to understand smoothly as you.

Reply
Daniel - November 13, 2016

Hi Jon,
I was looking into excel to complete a project I must complete. Although I have it manually all filled out, I decided that I would present it digitally for a presentation “shock”.
I have a list a services the customer can choose from a drop down list. (That’s done). Afterwards, the customer must choose how many days he wants that specific service from another drop down list (that’s done). The rest of formulas are done, such as taxes, fees, subtotal, total etc. The part where I’m stuck is that IF the customer selects a specific service, with a specific date range, that equals a price. I have 4 columns at the moment. First column has the the list of services. Second has the specific prices related to the services but for a date range of 1-4 days. The third column has the price for the a date range of 5-30days. The fourth, prices for 31-100days. This means the customer can choose i.e. 16 days, regardless it must recognize the prices per service per date selection range. That number must go in a cell, which the rest of taxes etc automatically updates. I do have a fifth column which I defined as (“DAYS”) for drop down list purposes, that column contains the numbers 1-100. So, the customer will choose a specific number from the “days” list and depending on the service requested, excel must look it up and show.
Much appreciated, thanks!!

Reply
    Jon Acampora - November 21, 2016

    Hi Daniel,
    It sounds like your spreadsheet is becoming pretty interactive. That is awesome. We can use these formulas to make our spreadsheets much easier for other users. Thanks for sharing!

    Reply
Mark Christian - October 25, 2016

Hi Jon,

Just wondering if you have a formula that could display the size of the drink using the price and the flavor as the input.

Reply
    Jon Acampora - November 1, 2016

    Hi Mark,
    Great question! There are probably quite a few ways to solve this using a array formula. Here is one simple approach that uses Index, Match, and Offset.

    This assumes the flavor is in A21 and the price is in A22.

    =INDEX($B$1:$D$1,MATCH(A22,OFFSET($A$1,MATCH(A21,$A$2:$A$10,0),1,1,3),0))

    This uses the OFFSET function to return the entire row of values for the matching flavor. It then uses an Index/Match formula to find the matching price in that row. Here is an article on the INDEX function that might help. Let me know if you have any questions.

    Reply
Ashish Sharma - June 23, 2016

Hi Jon,

If POTUS has to be elected on the basis of “ease of explanation of VLOOKUP”, you will be the undisputed president of USA.

One of the best explanations (Starbucks and Batman/ Robin)of Vlookup & Vlookup/Match combo.

You blog is on my bookmark as a “go to” place to refine my Excel skills.

Many Thanks.

Reply
    Jon Acampora - June 30, 2016

    Thank you so much Ashish! I really appreciate your kind words. Although I don’t think I could handle the stress of that job. 🙂

    I’m happy to hear you are learning so much about Excel. Have a great day!

    Reply
Shruti Joshi - February 1, 2016

Hey Jon.. You have really simplified the use of excel. I recently went through your website and found it awesome. Excel really scared me but now its almost like a piece of cake. Hope to learn more from you.

Reply
    Jon Acampora - February 2, 2016

    Thank you Shruti! I really appreciate the great feedback and I am happy to hear you are learning Excel. Have a great day! 🙂

    Reply
Vimal Singh - January 23, 2016

WHAT >> WHERE >> CLOSEST MATCH…!!!

What is this Jon? You are making Excel a baby game.

Superb and the best explanation.

Thank You Jon!

Reply
    Jon Acampora - January 25, 2016

    Haha! Thanks Vimal! I try to make the examples fun and memorable when possible. I appreciate the great feedback. Have a good one! 🙂

    Reply
Augustin - January 6, 2016

Dear Jon,
I’m a seasoned Excel veteran, and I never bother to write comments on computer blogs.

However I feel compelled today to write a word of thanks, for the brilliant solution and simple explanation of a perplexing issue. You offer to the rest of us the means to exponentially increase the power of Excel to solve real-world problems.

Thank you very much!

Reply
    Jon Acampora - January 22, 2016

    Hi Augustin,
    My apologies for not responding sooner. Thank you very much for the nice comment! I really appreciate it, and am so happy to hear this helped you. Have a great weekend! 🙂
    Jon

    Reply
How to use vlookup formula in Excel | My Spreadsheet Lab - December 12, 2015

[…] Jon Acampora compares vlookup with index/match […]

Reply
Angelica - May 21, 2015

Hi Jon

It’s awesome how you explained it, thanks. Do you have another article where can list many results? It´s like a Master-Detail sell.

Thanks a lot.

Angélica.

Reply
Steve - February 16, 2015

Hi Jon

This is awesome! Do you think you could work out one more challenging example?

I have a situation where none of the three vlookup values are static and I was hoping to use the MATCH statement example above to sort this out. Part 1 is the value to lookup. Normally one would start with VLookup(A3…Although the “3” is easy to lookup because it is the current row number, the “A” is challenging because this could be in any column.

The second statement is the lookup array. I can get around this by making the entire worksheet the range but would there be a way to use the match function to specify the range?

I look forward to your reply and fantastic job!

Cheers
Steve

Reply
Mom McK - January 27, 2015

OMG – This is rockin’ awesome…and so very clearly explained. Excellent example. Thank you! I read several other blogs that I just couldn’t follow (and I pretty much knew what I was looking for and I still couldn’t follow). Kudos. I will use this ALL THE TIME!!! And this will be my new “go to” spot.

Reply
    Jon Acampora - January 29, 2015

    Wow thank you so much for the nice comment “Mom”! My real Mom would be proud. haha! 🙂 Please let me know if you have any questions. Thanks again!

    Reply
Narendra Singh - December 18, 2014

Now i am clear with vlookup.
Previously I was a little confused but the way give example, it clear the doubt and I hope that you will teach Index and Match in the same way.
Thanks a lot.

Reply
    Jon Acampora - December 31, 2014

    Thanks Narendra! I’m glad to hear the explanation helped you. The Index/Match article will be coming soon.

    Reply
Hiltonel - December 9, 2014

Dear Jon,
I deeply appreciate. This is excellent explanation! I never thought of the Match &Vlookup functions like this before. This will greatly help me.

Thanks

Reply
Regan Rodrigo - September 22, 2014

Nice and creative way to explain. Though these functions are simple – but the way you have explained will get into mind for years and year and it won’t go off easily. You nailed it. thanks.. 🙂

Reply
    Jon Acampora - September 28, 2014

    Wow thank you Regan! I’m happy to hear that this will help you remember the functions and hopefully help you explain to others as well. Have a good one!

    Reply
Dritan - September 9, 2014

Very good Idea, to include the match function in Vlookup.

Now i can have more dynamic Vlookup function

Great job Jon.

Reply
don - May 29, 2014

this is very helpful Jon. I understood the principle behind MATCH function but now understand the detail of how it works.

keep up the good work.

Reply
Dan Franceski - May 29, 2014

This is OK, but it’s not necessary if you were using INDEX and MATCH instead of VLOOKUP.
I learned from another Execl guru a few years ago this simple rule: Never Ever use VLOOKUP or HLOOKUP (pretend that they don’t exist); Always Always use INDEX & MATCH. I’ve found that he was right.
INDEX and MATCH are much more flexible and more powerful; you can look up a value in ANY column or row (or BOTH for a 2-dimensional lookup) and find the corresponding value in ANY cell, regardless of which is to the left or right or top or bottom.
INDEX & MATCH are great (they are automatically Dynamic). VLOOKUP is limited and confusing.

Reply
    Jon Acampora - May 29, 2014

    You make a great point about INDEX Dan. INDEX and MATCH probably are the true dynamic duo, and I am planning on writing a third post in this series that explains INDEX. I agree that the INDEX & MATCH combo really are the ultimate in flexibility. They can also cause confusion for those that have not used INDEX yet.

    Even though you technically could completely rely on INDEX and MATCH instead of the LOOKUP functions, I still believe the VLOOKUP is a good one to keep in your toolbox. Mostly because it is one of the most commonly used functions, and there is a higher chance that future users of your model will understand it. The VLOOKUP is also much faster to write in some cases, because you only have to write one function instead of two.

    With that said, you still make an excellent point. In the next post about INDEX I will add a section that lists the pros & cons of using VLOOKUP vs INDEX & MATCH. Thank you for the comment and inspiring an idea! 🙂

    Reply
Carlos - May 29, 2014

Hi Jon
Very useful and amazing. Thank you for the precious information
Carlos

Reply
John Michaloudis - May 29, 2014

WOW, I´ve never thought about that combo before, why? Great tip!

All formulas should be non static in my mind as you never know who will end up messing around with your workbook.

A little planning goes a long way.

Cheers Jon!

Reply
    Jon Acampora - May 29, 2014

    Thanks John! I agree that all formulas should be non static. You have to be very disciplined to live by that rule, but it’s a good one to follow. I am definitely guilty of writing a quick static VLOOKUP from time-to-time. But like you say, it pays to plan ahead.

    Reply

Leave a Reply:

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x