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.

## 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?

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.

## The Starbucks Menu Example

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

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).

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.

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.

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.

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.

**1. lookup_value** – This is the **what **argument.

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.

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.

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.**

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”.

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.

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.

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

Please leave a comment below with any questions or comments.

## Take the VLOOKUP Challenge

I've put together 5-part video training series that will help you learn more about lookup formulas and test your skills.

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!

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.

Hi Jon

Very useful and amazing. Thank you for the precious information

Carlos

Thanks Carlos! I am really glad you found it useful.

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.

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! 🙂

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.

Awesome! Thanks Don!

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

Now i can have more dynamic Vlookup function

Great job Jon.

Thank you Dritan!

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.. 🙂

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!

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

Thank you Hiltonel! I’m glad it helped you. 🙂

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.

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

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.

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!

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

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.

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

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!

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

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

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

Superb and the best explanation.

Thank You Jon!

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

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.

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

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.

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!

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.

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.

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.

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!!

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!

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.

Thank you Bijay! 🙂

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!

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.

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!

Hi Jon,

Your are excellent. 🙂

Thanks for sharing such a valuable information with us.

Can I have your what’s up number ?

Regards,

Jay

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%

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

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.

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?

Hi Mauricio,

You will want to use the INDEX function instead of VLOOKUP for this. I also have a free 3-part video series on the lookup functions that might help.

Thanks!

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.

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

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.

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.

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!

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

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

Hi Jon

I was looking for an excel formula that can help me complete the date column below:

NUMBER DATE First alphabet second alphabet

3G4E21688 May-16 E=2014 E= May

6G4C14888 Jun-16 F=2015 F=June

5G4K22433 Jul-16 G=2016 G=July

5F4H02924 Aug-16 H=2017 H=August

6F4G94168

6F4H02799

6F4E71998

0H4B62627

6G4A43916

0F4L21124

4E4H99399

4E4H99399

Really Good demo of how to use this. Great explanation – a complicated task made simple. Thank You !

This was amazing Jon! Thanks for this simple way of explaining such a complex concept descriptively in a way anyone can understand. Great job! Please continue this awesome work to share your knowledge!

Sir Can you suggest me how to lookup image use excel formula in excel….

Wouldn’t it be easier and better to do a Match/Index formula?

Sir ur duo formula is really helpful. Thanks a ton.

Thank you so much. Your tutorial was the best place on the Internet so solve this riddle for me.

Thank you! Very helpful!

Thank you so much for your help

I really really appreciated this function!

If you properly referenced the table would you not just make reference to the title on the column in the formula?

Jon,

What you failed to show in this example is how you’d use this combination formula to find the price of a Caffe Mocha Grande price with 1 formula without adding cells that contain just the flavor and size. In your workbook example, you added that in cells A14 & B14.

Can that be done?

Can it be done for 2 different sheets, find a value from sheet 1 to sheet 2?

I like the fun you add to learning.

Thank you John. I really appreciate your lessons. I’ll come to your courses as soon as I can.

Hello,

This is exactly what I needed as I used a nested IF formula…

=iferror(if(J2=”CA”,vlookup(K2,’Product Data’!$A$2:$E,3,0),if(J2=”KC”,vlookup(K2,’Product Data’!$A$2:$E,4,0),vlookup(K2,’Product Data’!$A$2:$E,5,0))))

…to pull values. Now my formula looks like this…

=iferror(vlookup(K2,’Product Data’!$A$2:$E,match(J2,’Product Data’!$A$1:$E$1,0),0))

Thanks so much for this!

Thank youuuuuuuuuuuuuu

Hi Jon, Need help with the excel formula, I have an excel file where there are two tabs “master” and “data” in a master tab I have tried with formula lookup and match, however, it is not working as expected.

This is wonderfully helpful! Will keep following! Thanks!

WOW! I just tried it with my class because they were having difficulty understanding the concept. The coffee shop example was one they could identify with even if we do not have Starbucks in our part of the world. Thanks Jon.