Beware of #N/A from VLOOKUP

April 07, 2022 - by Bill Jelen

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

A VLOOKUP is looking for part BG33-9 and returns a #N/A error. This means that this item is missing from the table. — Figure 416. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Winkler on Unsplash