#N/A to blank

[Karen Smith]

I'm using VLOOKUP and its working great, however, when there's nothing in the
cells the formula is looking up, the cell contains #N/A. I'd like not to see
that, rather see a blank cell. Is there any way to do it?

The formula I'm using is
=VLOOKUP(A8 & "-" & G8,'Stibo
Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8

 

[John Bundy]

iserror will do it
=If(iserror(VLOOKUP(A8 & "-" & G8,'Stibo
Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8),"",VLOOKUP(A8 & "-" &
G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8)

 

[T. Valko]

Try this:

=IF(ISNA(VLOOKUP(A8 & "-" & G8,'Stibo
Rates'!$C$38:$D$53,2,0)),"",VLOOKUP(A8 & "-" & G8,'Stibo
Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8)

 

[PCLIVE]

Maybe one way:

=IF(ISNA(VLOOKUP(A8 & "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)),"",VLOOKUP(A8
& "-" & G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8)

HTH,
Paul

 

[Peo Sjoblom]

It's better to use ISNA to trap N/A errors otherwise there might be another
error that gets trapped that might have been of interest to know about.


--


Regards,


Peo Sjoblom

 

[rothfussm]

Excel 2007 has the IFERROR function for this purpose:
=IFERROR(value, value_if_error)

Prior versions must use the ISERROR/ISNA methods described above or a
user-defined function that implements IFERROR. The problem with
IFERROR/ISNA is that you are executing the same functions twice.
This can lead to worksheet calculation slowdowns or outright errors
when you don't update both versions of the formula.

To help usability/readability it may be useful to put the formula into
a named range like:
LookupResult = VLOOKUP(A8 & "-" & G8,'StiboRates'!$C$38:$D
$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8

Then in your worksheet use the following:
=if(iserror(LookupResult),"",LookupResult)

This method is likely slower yet due to the named ranges but now you
only have to edit one copy of the formula.

 

[rothfussm]

Another method would be to use a dummy range.

For instance, if your vlookup results are in column B, then in column
C use the formula =if(iserror(B1),"",B1). Now hide column B.

Not as elegant a solution but it is much faster than the IFERROR/ISNA
methods above using the the formula twice.

 

[Peo Sjoblom]

Excel 2007 has the IFERROR function for this purpose:
=IFERROR(value, value_if_error)

Prior versions must use the ISERROR/ISNA methods described above or a
user-defined function that implements IFERROR. The problem with
IFERROR/ISNA is that you are executing the same functions twice.
This can lead to worksheet calculation slowdowns or outright errors
when you don't update both versions of the formula.

To help usability/readability it may be useful to put the formula into
a named range like:
LookupResult = VLOOKUP(A8 & "-" & G8,'StiboRates'!$C$38:$D
$53,2,0)*F8+85+(22.5*F8)+(225*13)*H8

Then in your worksheet use the following:
=if(iserror(LookupResult),"",LookupResult)

This method is likely slower yet due to the named ranges but now you
only have to edit one copy of the formula.

It's still better to use ISNA than ISERROR if the OP wants to trap the #N/A
and the problem with IFERROR which is a very useful function btw is that
it won't work if the workbook is to be used on computers that don't have
Office 2007
which is by far the most likely scenario


--


Regards,


Peo Sjoblom




--


Regards,


Peo Sjoblom