Lookup Formulas Training Series – Lookup Formula Errors (2 of 3)

The Lookup Formulas Training Series Part 2:

How to Prevent & Handle Lookup Formula Errors

Intro to Pivot Tables and Dashboards – Part 3.zipDownload

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

273 comments

1 2 3 4 5

Your email address will not be published. Required fields are marked *

  • Good noon. How I will come arrive thru excel formula for the closest number for example for 18258 the answer is 18333?

    Reply

    • Hi Jefferson,
      Try the CEILIING function. It takes a number and significance argument. Set the significance argument to 18333. It will round any numbers below 18333 up to that number.

      If you have multiple ranges of numbers that you want to round to, then you can use a VLOOKUP formula for that. You would set the last argument to TRUE for closest match. Here is an article on how to use VLOOKUP for closest match.

      Reply

  • great job Jon, now i can understand what is going on when i surf across functions and data sheets 🙂

    Reply

      • Jon i have two questions please:
        1- I learned from you how to paste same value over a visible list of cells using alt+;
        but, how can i paste a full LIST over an empty list of visible list that hide some cells?
        2- if i have a list of 1000 names in 1000 cells but in real it may be only 20 names, how can i short list the salespersons names from this list 1000 without pivote tabel?

        Reply

  • By far the most common errors are #N/A. This video clearly explained the ins and outs of errors. Great job and well presented. Thank you Jon.

    Reply

  • Thanks for the advise as always.
    How does this differ from iserror?

    Also, when dealing with a large data file (200K Rows by 100 Columns) how can you prevent the calculation to occur every time you make a change or update the file////It takes a long time to process (3-5 minutes each time)

    Reply

    • Hi Ross,
      Great questions! The IFERROR function was introduced in Excel 2007, and is easier to write than ISERROR for most cases. With ISERROR we have to repeat the vlookup formula twice. We use ISERROR to check if the formula returns an error, and then use the vlookup again if the formula does NOT return an error. We also need to wrap the entire formula in the IF function.

      Here is what the vlookup formula from the video would look like using ISERROR.

      =IF(ISERROR(VLOOKUP(A4,$A$12:$C$19,2,FALSE)),"Error",VLOOKUP(A4,$A$12:$C$19,2,FALSE))

      This basically says that IF the VLOOKUP returns and error (ISERROR), then return a value (“Error”), otherwise perform the VLOOKUP.

      The IFERROR function is a lot shorter and more efficient. 

      =IFERROR(VLOOKUP(A4,$A$12:$C$19,2,FALSE),"Error")

      The logic is similar, but we don’t have to specify the vlookup formula twice. IFERROR does this for us. If the value argument (1st argument) does not return an error, then IFERROR will perform the vlookup.

      Unless you have users that are still on Excel 2003, I would recommend using IFERROR all the time. It is much faster and easier to write.

      You might still use ISERROR in this situation if you wanted to return a different value or use a different vlookup if the vlookup you are testing does NOT return an error. Those situations are pretty rare though.

      I cover this in more detail in my Ultimate Lookup Formulas Course.

      In regards to calculation time, you can change the calculation to Manual to prevent the workbook from calculating every time a change is made. On the Formulas tab in the ribbon, click the Calculation Options button on the right side and choose Manual from the list. You do have to remember to set the calculation back to automatic though. Otherwise your other workbooks will not calculate when you open them. This is an application level setting that applies to all open workbooks.

      I hope that helps. Let me know if you have any questions.

      Reply

  • Hi Jon,

    Thank you for taking the time to create this well needed tutorial on Excel V-Lookup errors.

    It was simple, clear and to the point. I’m new at this, therefore, you’re teaching methods
    made it easy for me to understand and apply your teaching.

    Reply

    • Thank you so much Julia! I am happy to hear you found it useful. Preventing and handling errors can save a lot of time and frustration. Have a great day! 🙂

      Reply

  • Excited to know solution to all types of errors.The most common being #N/A leading and trailing spaces. Thank you Jon for this.I hope this problem is now solved

    Reply

  • Thanks, I now know what causes most of the error i find with my spreadsheet.

    errors i normaly find are #N/A; #REF, #Name

    I did not know what was causing them and how to avoid them

    Thanks

    Reply

  • HI Jon #N/A is the one I encounter most often due to trailing spaces. Was just wondering if I can use the Trim function within the Vlookup function so that we get rid of the trailing space in the lookup value itself. I tried it and it din’t work, I got an error.

    Can the trim function be used only on 1 cell at a time or can it be done on an aray aswell.

    Reply

    • Hi Kalpana,

      Great question! The TRIM function can be used in the lookup_value argument in the formula to trim the lookup value only. The TRIM function can also be used with the table_array range. However, this will be an array formula and you will have to hit Ctrl+Shift+Enter after inputting the formula.

      I try to avoid array formulas whenever possible because most other users of your file will not understand them. Therefore, it is probably best to add a helper column that contains the trimmed values.

      I hope that helps. Let me know if you have any questions.

      Reply

      • THanks Jon

        Do trailing spaces happen only in text format and not in number? What are Array formulas. request you to send a sample of the same. Thanks again. I am really loving your videos they are really good

        Reply

      • Hi Jon,

        Sounds interesting about Table_array and array formula CTRL+SHF+ENTER.

        Could you please show us a sample of the same.

        Thanks in advance !

        Reply

      • Hi Jon,

        Sounds interesting about Table_array and array formula CTRL+SHF+ENTER.

        Could you please show us an example of the same.

        Thanks.

        Reply

  • Thanks Jon for explaining the common lookup errors. Now I should be get through the #N/A error which often I used to come across.

    Reply

  • This video was great for me and explained a lot of errors I was running into on my spreadsheets but didn’t know how to resolve.
    I wasn’t familiar with using the “IF Error to remove the unwanted NA’s that would appear because the data wasn’t on the spreadsheet. I would filter on them and manually remove them.

    So excited about your videos and am learning so much.
    We use tons of spread sheets with a lot of data and this is really helping me.

    Thanks for keeping it simple and easy to grasp.

    Kind regards

    Reply

    • Thank you Cheryl! I’m so excited to hear that the videos are helping you, and that you are learning more about Excel. Awesome! 🙂

      Reply

  • The video was Awesome Jon!! Iferror function seems exciting. The most common error that I came across is the #N/A.. Now I exactly know how to deal with it.. Thanks, that was super!!

    Reply

  • Having done virtually all my VBA development before the release of 2007, this is the first I’ve heard of IFERROR. How much effort that would have saved me! I was often forced to use named VLOOKUP and INDEX-MATCH formulas to keep multi-LOOKUP, multi-IF formulas to manageable lengths – and, going way back, to fit the formulas within the 256 character limit of still older versions of Excel. Aside from that, you also managed to show an experienced hand additional fine points of these oh-so-familiar functions. Great clarity as usual!

    Reply

    • Thank you Richard! I really appreciate the great feedback. And yes, IFERROR definitely saves a lot of time over the old IF(ISERROR) methods. Thanks again! 🙂

      Reply

  • Nice review of the reasons why vlookup returns errors. I had heard that using vlookup can cause a spreadsheet to run slow. We have workbooks that have an external data range that we pull in and update using refresh, so we have our vlookup formulas go well beyond the range we think our data refresh will go. I am wondering if there is a better way to do this so we don’t have excess formulas to slow down our workbook.

    Reply

  • Great video. Honestly I am very great for the IFERROR video you sent to us because it tell you what to do when we come across such problem.

    Reply

  • I did not know the exact reason for errors. I am aware of that. Keep being addicted 🙂 from Mongolia. Thank you so much again. Keep up the good work.

    Reply

  • Hi Jon,

    Thank you for uploading this short informative video.
    On the cheat sheet I noticed a spelling error.
    For the #Value error, as third cause, you mention “Error in references to other worbooks (file path)”
    Shouldn’t that be: … other workbooks?

    I hope you also see the humour.

    Reply

  • Real estate … I want to be able to sort or find groups of 4 or more Houses in a subdivision with the same zip code in a spreadsheet of 60,000 or more.

    ZipCode StreetName State DaysOnMarket SqFtBldg YearBuilt ListPrice SalesPrice Subdivision
    77056 5130 Bayou Timber Ln Tx. 2 3816 1973 1550000 1700000 Bayou Timbers
    77066 11710 BOURGEOIS FOREST DRIVE Tx. 17 6171 1982 395000 380000 Bourgeois Forest
    77074 8802 Birdwood Tx. 37 1961 139000 139000 Braeburn Gardens
    77489 15874 Alger Tx. 2 1981 65000 55000 Briargate
    77016 5313 Pate Tx. 7 1950 30000 30000 Briston Place
    77059 16018 Brook Forest Dr Tx. 11 5417 1980 499500 479000 Brook Forest
    77024 512 Knipp Rd Tx. 13 3802 1976 1875000 1799000 Bunker Hill
    77024 11739 Bayhurst Drive Tx. 16 4009 1973 1645000 1590000 Bunker Hill/Camden Place
    77018 1350 Bethlehem St Tx. 16 5024 1964 1275000 1200000 Candlelight Estates
    77379 16203 Champion Dr Tx. 39 3964 1978 365000 350000 Champion Forest Sec 01
    77551 2619 Christopher Sq Tx. 6 4241 1965 458500 448500 Christopher Square
    77028 8009 LYNETTE ST. Tx. 3 1950 76500 76500 Clairmont Place
    77573 2208 PONTCHARTRAIN Tx. 0 4035 1982 324900 313000 Clear Creek Village Sec 6
    77079 1034 Daria Dr Tx. 4 3711 1980 445000 445000 Fleetwood
    77079 702 Crossroads Dr Tx. 38 5076 1972 750000 720000 Fleetwood Sec 01
    77079 15703 Walkwood Dr Tx. 40 3824 1982 524600 500600 Fleetwood Sec 03 R/P
    77079 811 Walkwood Ct Tx. 32 3773 1977 599000 587500 Fleetwood Sec 03 Rep
    77071 7611 Vickijohn Dr Tx. 20 3988 1975 300000 285000 Fondren Southwest Northfield
    77071 10715 Villa Lea Ln Tx. 12 3542 1975 224999 225000 Fondren Sw Northfield
    77071 10810 Vickijohn Cir Tx. 12 5866 1975 348000 343000 Fondren Sw Northfield Sec 03
    77345 4803 Big Falls Dr Tx. 54 4768 1982 569900 550000 FOSTERS MILL
    77345 2210 Pleasant Creek Dr Tx. 23 3829 1983 489900 480000 Fosters Mill
    77034 310 Arvana Tx. 37 1957 95000 95000 Freeway Manor
    77581 2707 Green Tee Dr Tx. 16 3934 1978 415000 390000 Green Tee Terrace

    Reply

  • Hello Jon,

    Thanks very much for the thorough explanation. I appreciate your method of teaching, whereby you explain the “What, where, why, when and how”. Thank makes learning quiet easy. The most common error I encounter is the #Ref. Now I know how to fix errors quickly and look forward to the 3rd video.

    Sincerely,
    Meg.

    Reply

  • Hi Jon, great video – I love your course!!!! Very well done and easy friendly.

    The errors that I mostly see, is the #REF.

    Magda

    Reply

  • Hmmm… I got formatting problems with VLOOKUP often. Those error warning message boxes, which I never paid much attention to might be a big help. Thanks Jon!

    Reply

  • Many Thanks for the explanations and insights Jon.
    The most common error for me is the #N/A… This vid will certain help me.

    Reply

  • Thank you Jon
    I have sort of given up trying to fix problem of # NA. Now I got your idea. Your lesson is very helpful to solve common problem & learn new methods. Great job. well done !!!
    Mandira

    Reply

  • Thanks again Jon. I would say that the error I encountered the most was the NAME one, only because I kept forgetting to type the = before starting. I never really viewed the #NA as an error but more of a helper to determine if indeed items were missing in the data. I do like the IFERROR function, so thank you for the clarification up in the comments between the IF… and the IS…

    Carry on…

    Reply

  • N/a is the most common one I come across and usually because I am importing data from multiple sources and it is not in the correct format which has to be cleaned up for the vlookup to work.

    Reply

  • Jon,

    I get the NA error most often and have been able to work through it. It is really great to now understand what the errors are, whay they are caused and how to fix them.

    Reply

  • Hi Jon!

    A great video as always and great explanations, for the various errors. Probably my most frequent error, was the #VALUE! Error. In my last job, I was pulling loads of data down from Access and various number fields would be pulled down as TEXT.

    Thanks for the advice on correcting these. I’ve pulled it it on things to look for when they occur.

    Reply

  • Great video. Most common error is #ref. I already use the if error function to correct the error, especially with pivots. Looking forward to Video #3.

    Reply

  • Hi Jon,

    Great Job. Good learning experience how to handle the different errors. Jon, I want to know how can I use a sheet as table_array range in Vlookup formula.

    Thanks

    Reply

  • I get my fair share of #VALUE errors. Being more careful would eliminate the majority or remembering to click on f(x) as a way to “adjust” a bad memory and see what is actually needed by the function might help!

    Knowing the general meaning of each error response will be a big great time saver. Thank you Jon!

    Reply

  • I’m brand new at this but thanks to you, I’m not afraid of it..! I’ll be reviewing these videos over and over again to make sure I understand them correctly. Thanks sooo much Jon..! 🙂

    Reply

    • Thanks so much Eddie! I’m happy to hear you are gaining confidence with Excel and the lookup formulas. They are not easy at first, but taking the time to practice writing the formulas will really help.

      Thanks again and have a nice day! 🙂

      Reply