#N/A to blank
 

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)*H 8

#N/A to blank
 

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

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen Smith" wrote:

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)*H 8

#N/A to blank
 

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)*H 8)



--
Biff
Microsoft Excel MVP


"Karen Smith" wrote in message
...
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)*H 8

#N/A to blank
 

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)*H 8)

HTH,
Paul

--

"Karen Smith" wrote in message
...
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)*H 8

#N/A to blank
 

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


"John Bundy" (remove) wrote in message
...
iserror will do it
=If(iserror(VLOOKUP(A8 & "-" & G8,'Stibo
Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8),"",VLOOKUP(A8 & "-" &
G8,'Stibo Rates'!$C$38:$D$53,2,0)*F8+85+(22.5*F8)+(225*13)*H 8)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Karen Smith" wrote:

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)*H 8

#N/A to blank
 

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.

#N/A to blank
 

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.

#N/A to blank
 

wrote in message
...
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