The Lookup Formulas Training Series Part 2:
How to Prevent & Handle Lookup Formula Errors
Lookup Formulas Training Series
Video 1
Intro to VLOOKUP:The 5 Must Know Essentials
Video 2
Lookup Formula Errors:How to Prevent Critical Errors
Video 3
INDEX & MATCH:An Alternative to VLOOKUP
Which Error Do You Get Most Often?
Please leave a comment below with your answer, and any questions. Thanks!
Thanks for watching the video! Please leave a comment here letting us know what formula error you encounter the most, and how you fix it. This is a great way for everyone to learn some good tips and tricks.
Thanks again and have a great day! 🙂
Love your Excel tips and tricks. One question: you demonstrated exact matches; what about finding the closest match?
Thank you Rosey! Great question! I have a few articles on how to find the closest match with vlookup. The first explains how VLOOKUP works to find the closest match (last argument is TRUE).
I have another article that explains how to calculate commissions with VLOOKUP. This technique also uses VLOOKUP to find the closest match.
I also have videos on these techniques in my upcoming Ultimate Lookup Formulas course. Please let me know if you have any questions. Thanks!
Very good…or excellent
Hi Jon,
nice video, but personally, to trap the errors, I prefer to use IFNA, available since Excel 2013, or the combination IF(ISNA(VLOOKUP()),VLOOKUP(),””).
In this way only the #N/A error is trapped, because it can happpen, anlyzing long lists of data, that some data are not available. All the other errors are shown, in order that the user, or me, is aware that there is some problems with the formula.
Thanks for the suggestions Fran! Those are two good alternatives to target the N/A errors.
I start with IFERROR because it is a simple all purpose error handler. It also works well for divide by zero errors (#DIV/0). But there are definitely a lot of other functions that can help with error handling.
For anyone interested in seeing more of these functions, you can type =IS in a cell and see a list of a lot of different functions that can help handle different types of errors and values.
Thanks again Fran!
Another great video. I most often get the #N/A error because of the nature of the data I am working with. Often I will be updating pricing from one data table into my working worksheet. If a model number has had a change, usually a generational change, I do not want it to match so I know what needs changing. Sometimes even using the IFERROR function to wrap the VLOOKUP I get the #N/A anyway. Still trying to figure that one out.
Thanks.
Thanks Phil! That is interesting. Feel free to send me your file if you get the #N/A in the IFERROR again. I’d be happy to take a look at it.
N/A is the most error message I get!
It’s because of spelling or not checking the figures to make sure it’s a 6 not a 9 and so on.
Another good video!
Good video. The error that I get most often is #N/A which is due to downloading numbers as text.
Jon,
A very nice video. As others have mentioned, I learn other things watching your videos that are also marvelously helpful. On the first one, it was the keyboard shortcut ALT + = is autosumm. On this one, you demonstrated F2 and changing text to numbers. I didn’t know you could highlight a bunch at once. I’ve been doing them one at a time! When there’re too many, I’ve been using a macro to convert them.
My most common lookup error is NA.
Thanks for everything,
David
I often do data cleanup because of entry errors, especially extra spaces. Thanks so much for covering the TRIM function (and the CLEAN function on the spreadsheet). You’ve saved me tons of time and irritation.
Thanks Denise! Those blank spaces can be annoying. I’m really happy to hear that this will save you some time. 🙂
Thanks again for the video. I usually face the #N/A but now I know how to solve it. Truly apreciated!
Thanks again I am looking forward to the Index & Match functions. The most common error that I encounter is #N/A
Hi Jon, as before great Video. It help’s me a lot. Thank you very much
Nice explanation of errors and their causes. #N/A is the most common error that I have faced.
This is very educational and will improve my VLU skills a lot, especially with errors.
Gerhard Barnrd
Hi Jon
Great video, I will certainly use my new knowledge when dealing with errors.
I just want to add my “Thank You”. I enjoy your lessons and they are well paced. I also enjoy your showing/explaining the keystroke shortcuts you are using during the video.
the most common error I run into is the N/A error
I am finding watching these videos easier than trying to read a book.
things I am using you are making it easier to understand what I am doing.
Thank you
Great explanations thanks Jon :-). My most common error of #N/A, now I totally understand.
Hello Jon,
Another succinct lesson. As stated by other participants, N/A is the most common error. (I use TRIM and CLEAN – both very effective – but will be exploring IFERROR further.)
With regards.
Great videos, I am learning so much and also showing off at work! Never imagined that.
NA is the most common error I encounter but I have mastered the IFERROR by now. Thanks again!
Hi Jon
An informative and useful session. I have come across all of these errors at different times, but most commonly I get the #N/A or #VALUE errors. The #N/A errors don’t usually bother me. I know why I am getting them so I usually just delete then using Go To Special/Blanks, then pressing the delete button. I often get #VALUE errors when comparing data from two different databases. Previously I fixed these by adding a supplementary column and using the VALUE function to convert text to numbers, then copying the result back into the problem column, but your solution is much more elegant. Thanks for that.
Awesome! Thanks James!
A great Work. Your teaching style is excelent
The most common error I see is the N/A error. It happens usually because there is no data match in my lookup list. This tip will save me lots of edit time. Thanks!
I really enjoy your teaching style!! You move through the information quickly, but for anything not clear we can just rewind and review. Thanks, so much, for publishing these mini-videos.
Thanks Margie! I really appreciate the feedback and am happy to hear you are enjoying the videos. 🙂
#N/A is what I have to deal with most when looking for results that do not match lookups, where I require to retrieve a subsequent value instead to aid my calculations on the data.
Excellent video and easily digested.
I also liked Franz’s option of IF(ISNA….), and may yet use that instead of IFERROR.
Thank you.
Great video . . . well thought out!
The error I most often encounter is failing to “anchor” the table array, as I often “vlookup” for a series of values (needing to copy the VLOOKUP down through cells is my Achilles heel). As a matter of standard practice I always “absolute reference” the data area. If I have an error I always look there first.
Thanks Michael! That is a great habit to get into. I am definitely guilty of making that mistake as well.
Most common error I get is #N/A because the data I’m looking for is not in the original sheet. I didnt know about nesting the vlookup in the IFERROR function. Thanks! Great tip!
I get N/A, a lot in the spreadsheets I run and use iferror. Great information on the other errors, did not know the why’s of the others. Great video.
Thanks Paula!
Thank you for the excellent tutorial! #N/A is the most common, but I often find other errors crop up when I rearrange the data and forget to anchor the lookup to fixed columns or rows.
Very Good video. Your method of teaching Excel skills is excellent!
The most common error I experience with VLOOKUP is #N/A.
Thank you. I am waiting for the third video.
Excellent videos – very clear, concise, easy to understand.
My most common error is the #N/A usually dealing with numbers stored as text and vice versa. I deal with diferent data from different systems and they do not always match up so this error usually pops up.
I think most of the problems I have with vlookup are probably related to looking for numbers in text fields. I don’t get errors, the values that I see are there are not found w/ vlookup. Nice explanation as to why.
Video #2 was very helpful. I like that you keep the flow of the course short and direct. Learned some new tricks. Thank you
Awesome! Thanks Vanessa! 🙂
I meet with “#N/A” error the most. It’s really a pain in a bad place. I like how you resolve it, and I’ve learnt from the video that errors are slowing down the calculations, which is really bad, and now I’ll definitely pay more attention to them.
What you might also want to note is that errors can not be searched for as easily as a specific result, like when calculating ROI, or that errors also produce more errors, such as a formula depending on VLOOKUP expecting a number value, and getting text, and so on.
Hi Jon
I encounter the #N/A error mostly.
Your method of teaching is easy to understand in bite size modules
Great Video!! Wish I’d watched this 3 years ago; that’s how long I’ve been trying to “figure out” why I was getting #N/A and #Name errors. So glad you’ve made these videos available to us – Thank you!!
Jon, great review of the common errors, really straight forward. I really appreciated the IFERROR portion, as this will be an immediate improvement on my dashboard vlookup statements, from having to filter and manually remove the #N/A errors when data is not found.
Awesome! Thanks Dillon!
Hi Jon,
The most common error I see with Vlookup is the #N/A and #ref error. This video helps alot with explaining how to correct those errors.
Thank you so much!
Thanks Melissa! I’m happy to hear you found it useful. 🙂
So nice to have that explained… I’ve often had such difficulty with these errors in that I wasn’t able to identify why I was getting them, it is so simple I wonder why Microsoft couldn’t simply inform people in their help section of the possibilities! Oh well, thanks!
Thanks DID! I agree about the errors, and hope they improve the warning messages in the future.
Nicely done video!
Thanks Bill!
Thank you Jon
very valuable and clear video , it is a great reference for learning Vlookup
Thank you so much for explaining these! I actually had to do a quickie Help search yesterday before taking my spreadsheet into a meeting, and I discovered an IFERROR function suggestion that “solved” my problem. Trouble was, while it worked, I didn’t know *why* it worked. (The #VALUE! error wasn’t generated from VLOOKUP – I was trying to AVERAGE a series in which several cells didn’t contain values yet.)
Still much to learn to make sure my data is dead-on, and am so grateful to those like you, who generously share what you know. Thank you again!
Thank you Brenda! I am glad that you were able to use the IFERROR function on another formula. It is a great technique that can be applied to a lot of different formulas. Have a great day! 🙂
Hi Jon! Video #2 is very helpful!! My common error is the #N/A; our data tends to have trailing spaces, so I am making it one of my Best Practices to TRIM first! Thx for another great video 🙂
Thanks Sandy! The trailing spaces can be silent killers (of time). 🙂 Glad you enjoyed it.
Thank you Jon.
Thanks Khanh!
Very well explained, fixing errors is a process of elimination for me. Your explanation as to WHY & HOW the errors come about save me a lot of time. Thanks
Awesome! Thanks Mabel!
Very nice. thanks jon.
easy to understand and follow. Thank you
Thanks Carlo!