Correct #N/A Error Messages when using VLOOKUP Function

How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

Essential Functions – VLOOKUP()
Text Functions – LEN() and TRIM()
Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about: