I have a column containing a vlookup formula. With blank rows vlookup returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

hi Steve,

use IF(ISERROR(VLOOKUP(your formula here),"",VLOOKUP(you formula here)

please adjust the "(" as necessary

hope this helps
regards from Brazil
Marcelo

"Steve" escreveu:

I have a column containing a vlookup formula. With blank rows vlookup returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

Steve, Marcelo's answer will work very well. However, if you want to be very
specific for the #NA error only, you can us
=IF(ISNA(VLOOKUP(formula),"",VLOOKUP(formula))) instead. This would catch
#NA errors only, but if something else is wrong, then that would try to
return the true case which might show another error such as #VALUE.

"Marcelo" wrote:

hi Steve,

use IF(ISERROR(VLOOKUP(your formula here),"",VLOOKUP(you formula here)

please adjust the "(" as necessary

hope this helps
regards from Brazil
Marcelo

"Steve" escreveu:

I have a column containing a vlookup formula. With blank rows vlookup returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

This is just what I needed, but when I am getting an error on the "" in the
formula. I have tried just plain "", "0", and " ", but it still is not
working. Any ideas?

"Steve" wrote:

I have a column containing a vlookup formula. With blank rows vlookup returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

Sorry for posting it here. Hope someone can answer this.

I have multiple worksheets Sheet1, Sheet2 .to..Sheet7 which keep
varying in length on a weekly basis and I wish to append all these
sheets into one Mainsheet "MAIN".

All these sheets have different columns and I want to add 3 blank rows
between each table in the "MAIN" sheet. I want to create a macro so
that this is automated. Can anyone help me on this?

- Naveen



Looking-for-a-brainiac wrote:
This is just what I needed, but when I am getting an error on the "" in the
formula. I have tried just plain "", "0", and " ", but it still is not
working. Any ideas?

"Steve" wrote:

I have a column containing a vlookup formula. With blank rows vlookup returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

"Looking-for-a-brainiac"
wrote in message ...

"Steve" wrote:

I have a column containing a vlookup formula. With blank rows vlookup
returns
#N/A. To handle those situations I would like to put the vlookup formula
inside an 'if' statement that returned a value other than #N/A;ie,
If(vlookup()= "#N/A" ,0,Vlookup(....) )". However, using "#N/A" as the
condition doesn't seem to work-is there something else that does?

This is just what I needed, but when I am getting an error on the "" in
the
formula. I have tried just plain "", "0", and " ", but it still is not
working. Any ideas?

I think you may be trying to reply to JLatham's suggestion of
=IF(ISNA(VLOOKUP(formula),"",VLOOKUP(formula)))
in which case I think the problem is with the brackets.

Try
=IF(ISNA(VLOOKUP(formula)),"",VLOOKUP(formula))
--
David Biddulph