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.
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.