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:
- “Nine Essential Skills for Excel 2010” Video Training Resource
- “Nine Essential Skills for Excel 2007” Video Training Resource
Watch Video in High Definition
Follow this link to watch my Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels
How to Correct #N/A Error Messages for Excel's VLOOKUP Function [ 6:02 ] Play Now | Play in Popup | Download (7514)
