Watch the video extract as seen on YouTube, then read the full tutorial below.

Click the Full Screen button on the player to watch it in HD.

Excel VLOOKUP Exact Match Formulas Explained

VLOOKUP is my favourite Excel Formula! Perhaps because it was one of the first formulas I mastered that gave me an insight into the power of Excel and how it could help me in my job.

It enables you to get analysis and calculations done in minutes that would take hours manually. And once you master it you’ll find you have the occasion to use it all the time.

Interestingly there’s two ways you can use it but I find that most people know one way or the other, and only a few know both.

In this article we’re going take a look at the Exact Match version of the formula, but first let’s set the scene.

By the way, the other way is what I call the Sorted List VLOOKUP.

In the list below we want to calculate a commission in column F for each builder. But each builder has a specific commission rate they are entitled to. Thankfully we have this information in a table to the right, and this is where we give VLOOKUP the opportunity to strut its stuff.

Excel Vlookup Formula table

The Syntax is:

VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

And to translate it into English it would read:

VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)

Let’s make it even clearer by applying it to our example:

VLOOKUP(find the name Doug from cell B2, in the Commission Rates table H2:I9, return the value in column 2 of the tablebut only return a value if you find the exact name Doug in the Commission Rates table, otherwise give me an error)

calculate commission with Excel Vlookup Formula

Firstly let me clarify some points:

1)      ‘Return the value in column 2 of the table’ is referring to the column number in the table H2:I9, not the column number of the spreadsheet.  The information we want returned is the percentage rate, and it is in the second column of the Commission Rates table.

2)      ‘Only return a result if you can match the value exactly’ is telling Excel that we only want information returned if it matches our criteria exactly.  i.e. Find Doug in our Commission Rates Table, and if you can’t find Doug, give me an error.  The error displayed will be #N/A.

On the other hand, if we told Excel it was ok to not find an exact match, it would return the next best result.  i.e. If Doug wasn’t in our Table Excel would return the next best result.  In this example we wouldn’t want it to do that, but this option is handy in other situations which we’ll cover in another tutorial.

OK, now that’s clarified, in Excel our formula in column F for the above example would be:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)

  
Note: Where ‘FALSE’ is telling Excel we want it to find an Exact Match only.

Our Excel table would then look like this with the VLOOKUP formula in column F:

Excel Vlookup Formula result

You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table H2:I9.  This is called an absolute reference and it allows us to quickly copy the formula down column F without Excel dynamically updating the table range as we copy. 

How can we make this VLOOKUP formula even better?

Assuming the end result of our example exercise is to actually calculate the commission $ amount, we could make this formula even better by doing this in one step in column F.  Let’s say commission is calculated as Total $k x Commission %, our formula in cell F2 would read:

=VLOOKUP(B2,$H$2:$I$9,2,FALSE)*E2

And in seconds we can have hundreds of calculations done!

Excel VLOOKUP Formula

Rules, Common Mistakes and Troubleshooting!

1)      VLOOKUP formulas read from left to right.  You must have the information you are looking up (in our example Doug in the Commission Rates Table), in a column to the left of the information you want returned, in our example the ‘percentage rate’.  i.e. it has to go ‘Doug’, then ‘% rate’.  Excel wouldn’t be able to find it if it went ‘% Rate’ then ‘Doug’.

2)      You can have as many columns as you like in your Table, just so long as you follow the ‘left to right’ rule above.

3)      The ‘Table’ you are looking up can be in the same spreadsheet, or a different sheet in the same workbook, or in a different workbook altogether.

4)      The table doesn’t have to be sorted in any particular order, but you must not have duplicates, unless the information on each duplicate is exactly the same.  For example, if Doug appeared twice in our Commission Rates table with different percentage rates for each instance, Microsoft Excel would return the rate on the first instance of Doug.

5) The formula isn’t case sensitive, so ‘Doug’ could be ‘doug’ or ‘Doug’ in either column B or the table.

6)      What does it mean when my VLOOKUP returns a #N/A? It means Excel can’t find the value you’re trying to look up in your table.  If you get this, but you can ‘plain as day’ see it’s there in the table.  Then it’s likely you’ve got one value formatted as Text and another formatted as General.  To check this go to each cell you’re referencing and look in the formula bar to see if one is prefixed by an apostrophe ‘.  You can only see the apostrophe from the formula bar.  See example below.

Basically Excel reads text prefixed with an apostrophe as different to text without, even though on the face of the spreadsheet they might look the same.  You need to make sure both the value you’re looking up, and the value in the table either both have the apostrophe, or both don’t.  The quickest way to get rid of the apostrophes is to do ‘Text to Columns’, or run it through the VALUE function, which converts numbers formatted as text to actual numbers.

Excel VLOOKUP Formula

7) This formula works the same in Microsoft Excel 2003, 2007, 2010 and 2013.

The VLOOKUP is a fairly basic formula, but its applications are vast, especially when you combine it with other formulas like IF statements, SUMIF and so on.

Download the Excel workbook used in this example so you can practice to make sure you really get it and can take advantage of its power. Note: this is a .xlsx workbook, please ensure your browser doesn’t change the file extension on download.

More VLOOKUP Examples

Check out this list of our best VLookup tutorials

If you found this useful please share it with your friends and colleagues using the Google+1, LinkedIn, Facebook and Twitter buttons.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 119 comments… read them below or add one }

Mel July 15, 2014 at 10:22 am

Hi Mynda, I am trying to do some analysis on a s/s. I have a worksheet which has data arranged in columns, such as instance, location, months. I have another s/s with new data for the current month. I am trying to compare data from the current month to the existing s/s and if data (instance and locations) match, then add this new data as a new column to the existing s/s. The instances column should have unique data, but if there are data in the new or existing s/s that do not match, then append them at the bottom of the existing s/s. What formula should I use? Thanks for your help.

Reply

Mynda Treacy July 15, 2014 at 12:49 pm

Hi Mel,

I hate to say this but I think your approach isn’t ideal. There is no easy way to do what you describe using formulas… or any other Excel tool.

Perhaps if you can send me your workbook I can better understand what you’re trying to do and give you some advice on how to acheive the same end result with some changes to your process.

You can send your file and description of what you want and where via our help desk – anything you send is kept confidential.

In the meantime you might find this tutorial on Tabular Data helpful.

Kind regards,

Mynda

Reply

rahul June 17, 2014 at 12:56 pm

got gd

Reply

Ranjha April 22, 2014 at 5:55 pm

Very useful material.

Reply

Mynda Treacy April 22, 2014 at 7:53 pm

Glad we could help, Ranjha :-)

Reply

murtaza begi January 23, 2014 at 12:49 am

hi can you please sent me a emaill and explean the why we use the VLOOKUP AND ALSO EXPLAN AS WELL THANKS A LOT PLEASE

Reply

Catalin Bombea January 23, 2014 at 2:44 am

Hi,
VLOOKUP is an Excel Function that is used within tables to help filter through large volumes of data and
select the appropriate data based on given conditions. The VLOOKUP formula would automatically look through the list of your Objects and pick out
the corresponding data.

The function is very well described in this tutorial , please take your time to understand the explanations, you can also download an example workbook, the link for download is at the end of the tutorial.
Catalin

Reply

Yogi August 11, 2013 at 11:58 pm

Wonderful, I need some more examples to master this formula. i would also like to learn about sub total.
Thanks

Reply

Mynda Treacy August 12, 2013 at 1:23 pm

Thanks, Yogi. If you mean the SUBTOTAL Function you can learn it here.

Otherwise, a tutorial on the Subtotal tool is here.

Cheers,

Mynda.

Reply

Harish January 13, 2014 at 4:04 pm

Nice information….

Reply

Mynda Treacy January 13, 2014 at 10:15 pm

Thank you, Harish :-)

Reply

P July 20, 2013 at 7:03 am

Loved it. Nice and simple and that “plain english” translation is just superb !!!!

Reply

Mynda Treacy July 20, 2013 at 2:44 pm

Thanks, P. Glad you found it useful :)

Reply

sim May 18, 2013 at 4:25 am

by v look up the answer comes only up in the function arguments but in the cell only coming up v look up (example) but not the answer

Reply

Mynda Treacy May 18, 2013 at 6:40 pm

Sorry, Sim. I don’t understand. Can you please give me an example?

Cheers,

Mynda.

Reply

bns May 17, 2013 at 5:14 pm

Sir,
In my Laptop Excel sheet’s are viewing as for Rows 1,2,3,… are making visible and for coloums 1,2,3….. are visibling insted of A,B,C… how to chage it?

Reply

Mynda Treacy May 17, 2013 at 7:09 pm

Hi BNS,

You’ve got R1C1 reference style turned on. To turn it off you need to access the Options (Office button for 2007 or File tab for 2010) > Formulas category > uncheck ‘R1C1 reference style’.

Kind regards,

Mynda.

Reply

Charles Taylor May 3, 2013 at 7:40 am

Can solve this puzzle from a spread sheet from 1996 deals with gas processing Question on VLOOKUP Function This is part of a spread sheet
Amine Treater
Amine type (MEA, DEA, MDEA) DEA Typical Amine solution properties are shown below:

Many cells left out

Amine Properties Lookup Table
Amine Wt% Loading SG @ 120F Mole Wt BTU/Gal
MEA 15 0.33 0.99 61.08 1200
DGA 50 0.35 1.058 105.14 1300
DEA 30 0.35 1.02 105.14 1100
MDEA 50 0.35 1.03 119.16 1000

Intermediate Calculation Results
CO2 and H2S to be removed 60.84 lb-moles/hr
Solution specific gravity 1.04 VLOOKUP(UPPER($C$27),PROP,4,FALSE)
Amine molecular weight 105.14 =VLOOKUP(UPPER($C$27),PROP,5,FALSE) Do you know how cell DEA($C$27) at the top of the sheet is referenced to the two cells for Specific Gravity 1.04 & Molecular Weight 105.14 is nested or referenced?
Can’t find the chart UPPER on the spread sheet. PROP is Amine Look Up Table, immediately above the text.
Could send he spread sheet to you.
Regards….

Reply

Mynda Treacy May 3, 2013 at 1:23 pm

Hi Charles,

I think it’s best if you send me the Excel file so I can see what you’re talking about.

Cheers,

Mynda.

Reply

ateny April 30, 2013 at 5:16 am

It is very helpful website I have ever visited. I recommended already to all of my friends and the love it.

Reply

Mynda Treacy April 30, 2013 at 7:54 pm

Thanks, Ateny :)

Reply

David T April 25, 2013 at 6:44 am

Great!

I got a format from a resigned employee, but I don’t understand “,IF({1,0},…”, I cannot find information why there is {1,0} in the IF.

=VLOOKUP(B2&E2,IF({1,0},’PT New Sales’!$C$2:$C$200&’PT New Sales’!$F$2:$F$200,’PT New Sales’!$G$2:$G$200),2,FALSE)

Please help, Thanks

Reply

Mynda Treacy April 29, 2013 at 10:33 pm

Hi David T,

In the IF({1,0} the 1 = TRUE and the 0 = FALSE.

The formula is testing to see if the value in B2 is in the range C2:C200, and if the value in E2 is in the range F2:F200, if both match return the value in column G.

It’s an interesting formula. I have not seen it done this way before. I hope that helps.

Kind regards,

Mynda.

P.S. I have to thank Roberto Mensa for helping me clarify what this formula is doing :)

Reply

Barb Laing April 21, 2013 at 9:09 pm

You bring clarity to Excel. I am not just applying a formula, now I know what each component parts mean.

Thank you

Barb

Reply

Mynda Treacy April 22, 2013 at 9:24 pm

That’s my pleasure, Barb :)

Reply

Ramkumar April 21, 2013 at 2:24 pm

S..really it’s a great job.

Reply

Mynda Treacy April 21, 2013 at 7:20 pm

Cheers, Ramkumar :)

Reply

yash April 20, 2013 at 4:33 pm

It is very useful,thanks for that,but i want to ask,if Commissioning rates(As per example) is another excel sheet,so can we use vlookup,I tried but is showing error?

Reply

Carlo Estopia April 22, 2013 at 6:52 pm

Hi Yash,

Please send your file here HELP DESK.\

I just want to see why it’s an error and how you did it.

Cheers,

CarloE

Reply

Penelope April 12, 2013 at 4:03 pm

Firstly thanks for an awesome site. I have intermediate excel skills, but you’re explanations have made learning new formulas really easy!

In relation to VLOOKUP – I’m using it in a training register to confirm who has completed which training on what date. The column with the formula is formatted for dates as dd/mm/yyyy. Some people haven’t completed the training yet, and rather than leaving the cell blank it gives the result 0/01/1900. This is the formula as I’ve put it in the sheet =VLOOKUP($A5,Induction!$A:$C,3,FALSE). Is there any thing I can do to make it leave the cell blank if the reference is blank or perhaps a different formula I could use

Reply

Carlo Estopia April 12, 2013 at 5:32 pm

Hi Penelope,

All you need to do is put it inside an IF function like this:

=IF(VLOOKUP(A2,Induction!A1:C11,3,FALSE)=0/1/1900,"",VLOOKUP(A2,Induction!A1:C11,3,FALSE))

More IFs

Cheers,

CarloE

Reply

Penelope April 15, 2013 at 8:38 am

Thanks heaps for that CarloE :)

Reply

Carlo Estopia April 15, 2013 at 12:03 pm

Hi Penelope,

My pleasure.

Cheers,

CarloE

Reply

Pero Peric April 6, 2013 at 12:56 am

Dear,
I need a help with Excel formula, probably it’s easy but I can’t get it!
I have a table of one month and in 2right columns 2 figures, the form is used for account.
I made a box with Now() and want to make a formula to take data from table for present day. So first have to confirm same date as today from table and then to take data from 2right columns.
Thank You Very Much in Advance
Pero

Reply

Carlo Estopia April 7, 2013 at 11:39 pm

Hi Pero,

Please send your file via help desk. and we can look at this for you.

Cheers,

Carlo

Reply

arvind March 20, 2013 at 12:46 am

I downloaded the Excel workbook excercises (http://www.myonlinetraininghub.com/excel-2007-%e2%80%93-vlookup-formulas-explained) but they would not open in Excel. It would be appreciated if you can help me to overcome this problem.

Thank you for your help.

Arvind

Reply

Carlo Estopia March 20, 2013 at 9:44 am

Hi Arvind,

I would say either your browser has changed the file extension of the workbook from .xlsx to something else, or you are using Excel version pre-2007?

You can try again and make sure the file extension is .xlsx of the saved file.

If you have Excel 2003 or earlier let us know and we’ll make a pre-2007 version available.

Cheers.

CarloE

Reply

HASSAN KARIM March 10, 2013 at 7:43 am

hi mynda,
can u help me in vlookup formula.i think it is scarry for how use this formula in very easy.i have two difrent sheet in two difrent file .tell me how can i handle this…………plzzzzzzzzzzzzz

Reply

Carlo Estopia March 10, 2013 at 2:41 pm

Hi Hassan,

Here’s an example. I want to lookup Aquino, Greg’s position in Sheet2

The Formula:

=VLOOKUP(A1,'[Workbook2.xlsx]Sheet1'!$A$8:$F$10,2,FALSE)

Data:
Workbook1:Sheet1

      A                   B
1 Aquino, Greg	[VLOOKUP FORMULA HERE]Pitcher

Workbook2: Sheet1

7  Name	         Position	Salary	 Pay Rise 2004 	Pay Rise 2005	Pay Rise 2006
8  Aquino, Greg	 Pitcher	325,000	 29,250 	 28,340 	 22,955 


9  Bruney, Brian Pitcher	322,500	 29,025 	 28,122 	 22,779 
10 Choate, Randy Pitcher	550,000	 49,500 	 47,960 	 38,848 

Read More: VLOOKUP

Cheers.

CarloE

Reply

Manoj Yadav February 19, 2013 at 6:32 pm

If some time invest the amt 12000 in medicine and after the selling received total amt 18000 , how many % income

Reply

Carlo Estopia February 19, 2013 at 11:37 pm

Hi Manoj,

It’s a simple formula.
Data:

  A       B         C   
12000	18000	0.333333333

Formula:

=(B1-A1)/B1

I hope it helps.

Cheers.

CarloE

Reply

Proficient February 17, 2013 at 6:11 pm

Hi Mynda!

We can use Vlookup either side…

=VLOOKUP(E2,CHOOSE({1,2},$B$2:$B$13,$A$2:$A$13),2,FALSE)

=VLOOKUP(CRITERIA,CHOOSE({1,2},CRITERIA RANGE,LOOKUP RANGE),COLUMN NUMBER,FALSE)

Reply

Carlo Estopia February 18, 2013 at 11:10 am

Hi Proficient,

Thanks for sharing.

You may also read our similar post here: VLOOKUP and CHOOSE

Cheers.

CarloE

Reply

Jasim February 15, 2013 at 12:39 am

Many Thanks..

Reply

Mynda Treacy February 15, 2013 at 9:02 am

Cheers, Jasim. You’re welcome :)

Reply

mohammed adil February 9, 2013 at 2:59 pm

Hi
I have two tables one with colum name as “login id” and another with “computer name” and same colum in other sheet. i just want to compair login id and copy respective computer name to it. i tryed following funtion
=VLOOKUP(A2,sheet2!A:b,2,0) result is #N/A

Reply

Carlo Estopia February 9, 2013 at 6:48 pm

Hi Mohammed Adil,

Your VLookup should look like this:

=VLOOKUP(A2,Sheet2!A2:B4,2,0)

Your Table Array part don’t have the row arguments. It must have the numbers in other words i.e. Shee2!A2:B4.

Read more on VLOOKUP BASICS

Sincerely,

CarloE

Reply

Lynn February 8, 2013 at 2:02 am

I have used this lookup formula for years with complete confidence.
=if(vlookup(cell,range,1)=cell,vlookup(cell,range,Column # to be returned),” “). This returns an exact match if found and a blank cell if not.

But I have run into a problem, my formula is not returning anything on some newly added items in the lookup range. The item are still in sorted order and still in the lookup range. The format of the information is a match. Have you come across this?

Reply

Carlo Estopia February 8, 2013 at 3:15 pm

Hi Lynn,

Please try to send your file through HELP DESK so we can have a good look at your problem.

Anyways, my diagnosis is that you did not have absolute references to your table_array part
of your VLOOKUP.

For example

 =IF(VLOOKUP(G5,$C$5:$D$9,1)=G5,VLOOKUP(G5,$C$5:$D$9,1),"") 

You can do this by highlighting the ranges -only i.e. C5:D9- and press F4; or
You can simply put Dollar($) sign manually.

Cheers.

CarloE

Reply

6tel January 13, 2013 at 1:03 pm

Hi Mynda. The best for you in 2013… As usual, finding the best answers here… Excellent job, really.

Mynda, I’m having a trouble. Your explanation was great on the Vlookup formula syntaxis, but I was just wondering if the “col_index_num” requirement would look into rows instead of columns…. How would achieve that? I guess this function isn’t going to work for me, since I need to return a value that’s five rows under the respective “lookup_value” reference, and not to the side…

Is there an equivalent to this formula, but reading from the top of a table to its bottom?

=VLOOKUP(lookup_value, table_array, col_index_num ,range_lookup)

Reply

6tel January 13, 2013 at 2:12 pm

Hi Mynda. I guess I found it and did it, using the HLOOKUP function that you also explained here in the blog (sorry):

=HLOOKUP(AOR35,AK4:BG9,6,FALSE)

Thank you very much for your lessons.

Reply

Mynda Treacy January 15, 2013 at 1:43 pm

Hi 6tel,

Happy New Year to you too :) Glad you found the HLOOKUP solution.

Kind regards,

Mynda.

Reply

santhosh January 5, 2013 at 1:48 am

is very yousfull my life in my care

Reply

SREEDHARA December 25, 2012 at 12:32 pm

NICE WAY OF PRESENTING THAT’S TO EXLPNATION IN ENGLISH I.E -
And to translate it into English it would read:

VLOOKUP(find this value, in that table, return the value in column x of the table, but only return a result if you can match the value exactly)
VERY SUPERB
TNK U

Reply

Mynda Treacy December 26, 2012 at 6:45 pm

Thanks, Sreedhara :)

Reply

Reeta Khetrapal December 23, 2012 at 1:49 am

How to search multiple entries by using Vlookup

Reply

Mynda Treacy December 23, 2012 at 8:34 pm

Hi Reeta,

Here is a tutorial on VLOOKUP multiple values.

If that’s not what you want there is a list of different VLOOKUP tutorials here, including multiple criteria, multiple columns and returning multiple values.

Kind regards,

Mynda.

Reply

Viswanathan December 22, 2012 at 6:59 pm

Excellent way of explaining. Easy to understand

Reply

Mynda Treacy December 23, 2012 at 8:28 pm

Thanks, Viswanathan :)

Reply

Devikarani December 20, 2012 at 5:26 pm

Great help for searchers like me. Thanks a ton..

Reply

Mynda Treacy December 21, 2012 at 10:46 am

You’re welcome, Devikarani :)

Reply

Martin Williamson December 12, 2012 at 10:38 pm

I have a challenging excel vlookup problem I can’t solve and I can relate it to your example.

In my problem there is an additional column in the table of rates called “effective date”.

Insert a new column between Column G:H, and add title “Effective Date” Then give all those in your rates table an effective date of 01 Jan 08.

However, Dave gets a raise on 01 Mar 08 to 6% commission. (Well done Dave!)

Insert a new entry below Dave’s 01 Jan 08 entry and add:
01 Mar 08 Dave 6%

However, with your current formula, it does not find Dave’s new rate for sales after 01 Mar 08.

I have tried index match formula’s but also to no avail.

I really, really, hope you’re able to help as this has been bugging me for months.
Many thanks for your time. Regards, Martin

Reply

Mynda Treacy December 13, 2012 at 9:35 pm

Hi Martin,

If you know you are looking for the date on 01 Mar 08 you could use SUMIFS:

=SUMIFS($I$5:$I$6,$G$5:$G$6,"Dave",$H$5:$H$6,"01/03/2008")

or if you’re using Excel 2003 use SUMPRODUCT:

=SUMPRODUCT((I5:I6)*(G5:G6="Dave")*(H5:H6=DATEVALUE("01/03/2008")))

However, if you’re just looking for the last record for Dave (your data would need to be sorted in ascending order) then you can use:

=VLOOKUP("Dave",$G$5:$I$6,3,TRUE)

or

=LOOKUP("Dave",$G$5:$G$6,$I$5:$I$6)

Where column G contains your names, H your dates and I contains your commission rates.

Kind regards,

Mynda.

Reply

Martin Williamson December 13, 2012 at 10:11 pm

Thanks for the prompt reponse and the numerous solutions.

I’ll try those out and see if I can get them to fit my situation.
Again, many thanks, your prompt response has been much appreciated. :)

Reply

gshephard December 1, 2012 at 12:34 am

Finally found a clear explanation in plain english on how to use this function. Wish i had found this site 1 hour ago! Thanks!

Reply

Mynda Treacy December 1, 2012 at 7:47 am

:) Thanks, Garth.

Reply

mutalib November 21, 2012 at 10:20 pm

i want to know about pivot table in Excel 2007

Reply

Philip Treacy November 22, 2012 at 4:35 pm

Here’s a link to some tutorials on Pivot Tables http://www.myonlinetraininghub.com/pivot-table

Phil

Reply

Prakash November 20, 2012 at 4:56 pm

Here we’re doing VLOOKUP in same worksheet,
can you give me VLOOKUP formula using different worksheet.

Reply

Mynda Treacy November 20, 2012 at 6:59 pm

Hi Prakash,

When you build your VLOOKUP formula you can use your mouse to select the cells on another worksheet. Excel will automatically put in the cell references for you.

Kind regards,

Mynda.

Reply

Trudi November 9, 2012 at 3:53 pm

Great tutorials – thankyou for sharing your knowledge. Greatly helped me in my work.

Reply

Mynda Treacy November 9, 2012 at 5:57 pm

You’re welcome, Trudi :)

Reply

Steven Pofcher (@spofcher) November 4, 2012 at 12:49 am

Nice. I especially like the extending of the VLOOKUP to do calculating.

Reply

Mynda Treacy November 4, 2012 at 12:07 pm

Cheers, Steven :)

Reply

Ana da Silva November 1, 2012 at 3:56 pm

Finally. I have found someone that can translate “computer lingo” into English.

I have been lost in the land of technology, and then……. I have found my little slice of heaven. This website.

From one bean counter to another. THANK YOU.

Reply

Mynda Treacy November 1, 2012 at 8:27 pm

:) Wow, thanks Ana. I’m glad you can relate to my English translations!

Reply

Lynn Ashworth November 1, 2012 at 6:55 am

This is excellent. I have been trying to teach myself vlookup today using the Help option and it didn’t help! What a difference it makes to actually see the data and have it so clearly explained. You have de-mystified vlookup for me and I’m now looking forward to getting to work tomorrow to try it out on my spreadsheet! Thank you very much.

Reply

Mynda Treacy November 1, 2012 at 7:11 am

:) Glad I could help, Lynn.

Reply

AV September 27, 2012 at 5:50 am

Awesome breakdown of the VLOOKUP formula. This saved my day at work. I came here because it seemed intimidating but after seeing the tutorial, I now scoff at its fear-factor, lol. You also have a hot voice. Extra brownie points for you! : )

Reply

Mynda Treacy September 27, 2012 at 3:19 pm

Hi AV,

I’m glad I took the ‘scary’ out of Excel for you :)

Kind regards,

Mynda.

Reply

Sadaqat Hussain September 26, 2012 at 4:56 pm

it is powerful and wonderful but I d’nt now how to utilize it

Reply

Chris K September 21, 2012 at 7:54 am

Mynda,
Is there any way to have a vlookup formula present where if the fields are left blank you can have a return of 0 instead of N/A? I tried including a blank line item in my chart with a 0 value but it still comes back as N/A and is killing my totals.
I can show you my work so far but it is getting messy!

Reply

Mynda Treacy September 21, 2012 at 8:22 am

Hi Chris,

You can use IFERROR with your VLOOKUP to return any value you’d like if the result is not found in the table.

Click the link above to see an example.

Kind regards,

Mynda.

Reply

Manjinder Mavi December 11, 2012 at 9:26 am

Hi Mynda,

Your “plain english” style of explaining things motivated me to learn more.

Related to Chris K’s query above, VLOOKUP puts a value 0 if the corresponding value cell (column index cell) is empty. It messes up my other calculations. Is there any way to get VLOOKUP with help of other function to return a particular value (say “Empty”) if the cell is empty.

I understand we can use IFERROR with VLOOKUP the lookup value can’t be found. But I am interested when the lookup value is found but the corresponding column index cell for that lookup value is empty.

Thanks.

Best regards,
Manny

Reply

Mynda Treacy December 11, 2012 at 4:18 pm

Hi Manny,

You can wrap the VLOOKUP formula in an IF function like this:

=IF(VLOOKUP(A1,B1:C10,2,FALSE)=0,”Empty”,VLOOKUP(A1,B1:C10,2,FALSE))

Kind regards,

Mynda.

Reply

Imran September 12, 2012 at 6:53 am

Dear Mynda,

I like your videos , especially your style of narrating complex problems in a simple and effective manner.

Keep up the good work and Allah bless you.

Regards
Imran

Reply

Mynda Treacy September 12, 2012 at 8:41 am

:) Thank you for your kind words, Imran.

Reply

Cheryl Blalock August 30, 2012 at 4:10 am

I’m currently using vlookup in my work. Now I have an problem to solve. I have two reports that I need to work with. One FY12 customer sales with part numbers. The other FY13July customer sales with part numbers. I want to put the FY13July sales number in a column on the FY12 File and add to each month end so we have a running total. The problem is the look up value is not unique. Many customer buy the same part #. Is there a way to use 2 cells as the look up value? (acct # & part #) Thanks for your help.

Reply

Mynda Treacy August 30, 2012 at 6:58 am

Hi Cheryl,

You need to make a column of values by joining the acc# and Part# together using CONCATENATE. Perhaps do this in your FY13 data. Then use this technique to look up the FY12 data: VLOOKUP looking up multiple values.

I hope that helps.

Kind regards,

Mynda.

Reply

kashee August 24, 2012 at 6:40 pm

Great job !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Reply

Mynda Treacy August 24, 2012 at 8:05 pm

Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :)

Reply

Vaidehi Raval August 14, 2012 at 1:50 pm

Excellent! cheers :) .

Reply

Mynda Treacy August 14, 2012 at 10:02 pm

Thank you!

Reply

Andi July 14, 2012 at 3:11 am

Thanks so much! Starting a new job and had to refresh my vlookup skills! Great website. I’ll be back.

Reply

Mynda Treacy July 15, 2012 at 11:10 am

Cheers, Andi :) All the best in your new job.

Reply

Janette Goodson June 1, 2012 at 11:25 pm

I find these exercises very informative and fun to do.

Reply

Mynda Treacy June 2, 2012 at 10:46 am

Thanks, Janette. Glad you like them :)

Reply

Ramamoorthi May 25, 2012 at 9:32 pm

The article was simple and easy to understand. It cleared the doubts I had about VLOOKUp function. I want more such articles to improve my working with Excel. Thanks a lot

Reply

Mynda Treacy May 26, 2012 at 10:12 pm

Thanks Ramamoorthi! You can find an index of Excel tutorials here.

Kind regards,

Mynda.

Reply

Janell April 18, 2012 at 1:04 am

Thank you

Reply

guru March 30, 2012 at 11:48 pm

cool…

Reply

anish kumar February 23, 2012 at 10:43 am

Brilliant web site, Carry on the wonderful work

Reply

Mynda Treacy February 23, 2012 at 10:08 pm

Thanks, Anish :)

Reply

shipra February 22, 2012 at 10:08 pm

It is definitely very helpful !!

Reply

KARTHIK January 30, 2012 at 10:10 pm

GOOD

Reply

Philip January 17, 2012 at 6:59 am

Thanks heaps

Reply

SrSr September 14, 2011 at 4:52 pm

Good One…!!!

Reply

Mynda Treacy September 14, 2011 at 8:34 pm

Thanks SrSr! Glad you liked it.

Reply

ola August 30, 2011 at 3:47 am

Awesome

Reply

Mynda Treacy August 31, 2011 at 9:23 am

Thanks Ola.

Reply

Jamie Iomo December 8, 2010 at 4:41 am

how are you?

Looking forward to your next post

Reply

Joe December 3, 2010 at 3:44 pm

Wow, thanks for the great info I will definately link you on my blog.

Reply

JasonybRider December 2, 2010 at 3:51 am

Awesome blog thank you! You should checkout my site

Reply

AnarhieS November 25, 2010 at 5:27 pm

VLOOKUPS are very powerful and under used in my opinion. I guess alot of people don’t knwo how to use them

Reply

Liz November 15, 2010 at 3:02 pm

damn apostrophes have caught me out too with text

Reply

Shaun September 30, 2010 at 6:57 am

Brilliant web site, Carry on the wonderful work!

Reply

Mynda September 29, 2010 at 8:31 pm

@RHC – nope, passionate as ever!

@Liz – great. Glad we can help.

Reply

Liz September 28, 2010 at 4:43 pm

yeah my dad will like this

Reply

RHC September 27, 2010 at 5:20 pm

Awesome post tim, it’s been a while since I’ve been on here. I see that nobody has lost their passion. Good to be back.

Reply

Previous post:

Next post: