Vlookup - N/A

C

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.
 
D

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(...)))
 
C

Canon

Canon said:
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.
Click to expand...

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.
 
D

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.
 
Top