VLookUp- Do Not Show N/A

Microsoft Excel 2003 (full product)
December 30, 2009 at 13:48:26
Specs: Windows XP
I currently have a vlookup function. However, when the value isn't in the "table array," it returns an N/A in the target cell. How can I write the formula so the N/A doesn't appear?

See More: VLookUp- Do Not Show N/A

Report •

December 30, 2009 at 13:54:22
To get a blank cell, try this:


To get a message, try this:

=IF(ISNA(VLOOKUP(B1,$C$1:$D$3,2,0)),B1 & " wasn't found.",(VLOOKUP(B1,$C$1:$D$3,2,0)))

(Both formulae are one long line)

Report •

December 30, 2009 at 13:55:29

The basic idea is to use the ISERROR() function to test if VLOOKUP() returns an error
Then use an IF() function to return empty "" if it is an error or re-run the VLOOKUP() if it isn't an error.
This gives you the structure:



Report •

December 30, 2009 at 14:12:24
Please note that while ISERROR will indeed "hide" the #N/A error, it will also hide any other errors that the formula might return.

For example, this formula, using ISNA, will return a blank cell if "Test" is not found and return a #REF error if "Test" is found because there is no Column 3 in the lookup_array.


This formula, using ISERROR, will return a blank cell whether Test is found or not.


I only mention this because ISERROR might lead the user to think that Test isn't in the list, when in reality the blank cell might mean that there is some other error lurking in the background.

Report •

Related Solutions

December 31, 2009 at 07:03:54
Thank you guys for your help. The first formula worked perfectly.

Report •

December 31, 2009 at 07:37:07
Glad to have been of assistance.

Report •

Ask Question