In this video we’ll look how to avoid and handle that situation. Here we have a VLOOKUP example we’ve looked at previously which uses VLOOKUP to retrieve employee information based on an ID. When you’re using VLOOKUP this way you’ll probably run into situations where VLOOKUP can’t find the value you’re trying to look up. The first thing to check is your lookup table. Make sure the table is properly defined and the lookup value is in the left-most column. Most often, when VLOOKUP can’t find something in the lookup table, it’s because the value doesn’t exist in the lookup table. For example, if I type in id “E999,” which doesn’t exist in the employee table, we’ll see N/A errors for all the VLOOKUP formulas. That’s because there is no employee with id E999. Sometimes, though, the problem is more subtle. For example, the code E785 is valid, and VLOOKUP correctly retrieves the information for Harold. However, if I add a space to the end of the code, the lookup will fail with an N/A error. Likewise, if I add a space to the code that appears in the table, the lookup will also fail. In both cases it looks like VLOOKUP should work, but it doesn’t, due to a typo that’s hard to see. So, take care that your data and lookup values are clean and don’t contain extra characters. Often, though, a lookup fails because a value really doesn’t exist in the lookup table. In that case, you may want to display an error message that’s more friendly than “N/A.” One easy way to do this is to wrap the VLOOKUP function inside a function called IFERROR. You can use IFERROR to trap errors and return a more friendly value. Simply add VLOOKUP as the first argument of the function, and for the second argument, add the message you’d like to display when VLOOKUP returns “N/A.” In this case, I’ll use “Not found.” Now when a value can’t be found, VLOOKUP will throw the N/A error and IFERROR will catch that error and display “Not found.” Be careful if you use this technique because IFERROR will catch any error that’s thrown by VLOOKUP. For example, if I misspell the VLOOKUP function, Excel throws the #NAME error, but all you see in the worksheet is the “Not Found” message, which is misleading.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
