Vlookup - N/A

[Canon]

Excel 2007
I am using the following formula;
=VLOOKUP(B3,Info!$A$1:$B$2,2,FALSE)
If B3 remains blank, or has something other than what is in the look up
table, I want to get rid of the #N/A that shows up in the cell so the cell
remains blank.

 

[Dave Peterson]

=if(b3="","",iferror(vlookup(...),""))

=iferror() was added in xl2007. If you have to share with others who use older
versions:

=if(b3="","",if(isna(vlookup(...)),"",vlookup(...)))

 

[Canon]

Excel 2007
I am using the following formula;
=VLOOKUP(B3,Info!$A$1:$B$2,2,FALSE)
If B3 remains blank, or has something other than what is in the look up
table, I want to get rid of the #N/A that shows up in the cell so the cell
remains blank.

Update;
This formula works;
=IF(ISNA(VLOOKUP(B5,Info!$A$1:$B$2,2,FALSE)),"",VLOOKUP(B5,Info!$A$1:$B$2,2,FALSE))
But again, If B3 remains blank, or has something other than what is in the
look up table, I need the cell to remain blank - with no formula.

 

[Gord Dibben]

A formula cannot erase itself.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If you really want the cell to be empty (no formula, no value (including
errors)), then let the formula evaluate and if it returns an error, clear the
contents of that cell.

Since it's empty -- there won't be a formula to be re-evaluated, though.