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?

To get a blank cell, try this: =IF(ISNA(VLOOKUP(B1,$C$1:$D$3,2,0)),"",(VLOOKUP(B1,$C$1:$D$3,2,0)))

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)

Hi, 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:=IF(ISERROR(VLOOKUP("Test",$G$1:$H$3,2,FALSE)),"",VLOOKUP("Test",$G$1:$H$3,2,FALSE))Regards

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"

isfound because there is no Column 3 in thelookup_array.=IF(ISNA(VLOOKUP("Test",$G$1:$H$3,3,FALSE)),"",

VLOOKUP("Test",$G$1:$H$3,3,FALSE))This formula, using ISERROR, will return a blank cell whether Test is found or not.

=IF(ISERROR(VLOOKUP("Test",$G$1:$H$3,3,FALSE)),"",

VLOOKUP("Test",$G$1:$H$3,3,FALSE))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.

Thank you guys for your help. The first formula worked perfectly.

Glad to have been of assistance.

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History