Excel Magic Trick 990: VLOOKUP returns Zero and #N/A, What To Do?

See formulas to deal with the VLOOKUP function returning a zero or an #N/A error:
1. When cell is empty in VLOOKUP table (table_array argument), VLOOKUP returns a zero. See IF function formula to return text instead.
2. When items that you are looking up is not in VLOOKUP table (table_array argument), VLOOKUP returns an #N/A error. See the Excel 2007 or later function IFERROR and see an Excel 2003 or earlier IF and ISNA functions formula.
3. Learn how to fill in the empty cells in a VLOOKUP table (table_array argument) to shorten the formula.
4. Learn about Go To, Special, Blanks, to quickly highlight all empty cells in the selected range.

[KewalinAbigail]

I spent whole days searching for this so I can submit my report to my boss.
It works! Thank you so much. Keep doing this kind of video! Cheers.

[excelisfun]

There are still some large percentage that have Excel 2003. I don't know what the % is. I hope it is less than 50% by now...

There are so many awesome things that are worth the price of learning the new ribbon tabs.

You are right, it is a hassle to have to worry about these differences...

[elisatsun]

I spent hours trying to figure out how to do this today - thank you SO much! Amazingly useful video.

[SHANKSY]

Outstanding.!!
You save me from going stir crazy trying figure out how to get rid of the N/A from my Timesheet & Expenses workshook that I am giving a facelift. Thanks to your videos, Excel has becoming way more enjoyable. :)

[DiscoverYourGreatestSelf]

Thank you so much!!!

I've literally been struggling to search for an option that fixes my dropdown menu selection when there is nothing selected or when it shows an error. I have a Nutrition Food Diary and Calculator that I have created for clients on Sheets, and this does the trick.

Appreciate all the help. Keep it up, you have kudos from me!

[jardinosaur]

Answered my questions on the #N/A perfectly!

[excelisfun]

Cool - glad that the video can refresh the mind!

[excelisfun]

F4 makes the cell references absolute. Here is a title of a video all about Cell References:

"Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation "

[sachavez]

Always learn something good from you!

[marygigot]

Thank you. I have been struggling for hours & I appreciate the step by step explanation

[blubtube]

Thank you for all your work in these videos... very helpful

[sujan.v]

Thank you sir, I've been struggling for hours.

[joanhyla]

You've got to be kidding me :)))
How on earth do you get the time to make so many videos including epic ones, make worksheets to follow along, organize all the videos in playlists and even reply to all individual comments on YouTube??? I am in total shock! Guess you need to teach us time management too! Keep the good work! I will definitely buy your book. Let me know how else I can support you. You are the best!

[excelisfun]

Lookup left video:

INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)

13:07 minute mark.

[pgBand]

Thank you so very much. I needed to use this today.

[jdavilae]

thank you, its always a great resource and an easy way to keep learning...

[krn]

Thanks Mike, how much longer do we need to refer to 2003 solutions? :) you should conduct a poll and see how many users are still using 2003. I know some people I work with still rely on 2003 and it is frustrating to have to have 2 versions of spreadsheets for each group. Thanks again, great tips as usual.

[jonp]

I am really learning a lot from these videos and it is making my job a lot easier. Quick question can you input for the iferror to reference to another column? Thanks

[maracuyapassionfruit]

Hi Mate! thank you for the formula.
What would happen if instead of ID I have values, and some of the cells have a zero value and some others have a blank space, and I want to have zeros and blanks respectively. So far, when I do a VLOOKUP I get a zero instead of the blanks. Do you know any way to have zeros AND blanks respectively?

[minzhang]

Thanks a lot for sharing so many of your videos. I can't imagine how much time you have been spending to make these videos to help others. It benefits me a lot at work. At beginning, I tried different videos on Youtube, but yours are the easiest to follow. I'd like to buy your book. Could you please let me know the complete name and where I can buy it? Again thanks for your time. You are the BEST!!!