Excel: Beware of #N/A from VLOOKUP

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

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

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