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.

VLOOKUP MATCH Explained At Starbucks.xlsx (15.2 KB)

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

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?

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

Thank you so much for your help

I really really appreciated this function!

Thank you! Very helpful!

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

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

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

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

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

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

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

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

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!

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.

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.

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

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.

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!

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.

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 are excellent. ๐

Thanks for sharing such a valuable information with us.

Can I have your what’s up number ?

Regards,

Jay

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!

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

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,

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!

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

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

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

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

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.

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

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!

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.

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

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!

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

Now i can have more dynamic Vlookup function

Great job Jon.

Thank you Dritan!

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!

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

Hi Jon

Very useful and amazing. Thank you for the precious information

Carlos

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

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.