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.

=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 wrote:

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

"Canon" wrote:

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)),"",VL OOKUP(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.

A formula cannot erase itself.


Gord Dibben MS Excel MVP

On Mon, 1 Mar 2010 08:06:01 -0800, Canon
wrote:



"Canon" wrote:

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)),"",V LOOKUP(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.

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.

Canon wrote:

"Canon" wrote:

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)),"",VL OOKUP(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.

--

Dave Peterson