• Excel Book Excerpt

Excel Beware of #N/A from VLOOKUP

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Beware of #N/A from VLOOKUP

Problem: A few of my VLOOKUPs are giving me the #N/A error.

LE10000427.jpg 

Figure 426 BG33-9 is a new item and isn’t in the lookup table.

Strategy: This is common when you are doing VLOOKUP. It tells you that the lookup value is not found in the first column of the table. When you encounter an #N/A error, add that item to the table (see the next topic).

Additional Details: To isolate the #N/A errors, sort your data descending (using the ZA icon). All of the #N/A errors will sort to the top.

Additional Details: If you don’t want to update the lookup table with new values, but prefer to have alternate text entered, use IFERROR:

=IFERROR(VLOOKUP(),"Item Not Found").

Gotcha: If you leave the #N/A errors in the data set and try to add up that column, the SUM will be #N/A. One single #N/A causes all downline formulas to calculate as #N/A.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: