if statements

[Julie]

Hi,
I have been successfully using the following statement:

=IF(ISERROR(VLOOKUP(A17,'Data
Base'!$A$1:$Y$413,14,FALSE)),"",VLOOKUP(A17,'Data Base'!$A$1:$Y$413,14,FALSE))

I want to add another if to this, so that I get:
If lookup results in error message, leave blank (first part above)
If lookup results in the value 0, leave blank (how do I add this?)
Otherwise, put result of lookup (second part above)

How do I had the the second IF to this equation so that if the lookup
results in the value 0, leave blank?

 

[akphidelt]

There might be a shorter solution out there but this should work

=IF(OR(ISERROR(VLOOKUP(A17,'Data
Base'!$A$1:$Y$413,14,FALSE)),VLOOKUP(A17,'Data
Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A17,'Data
Base'!$A$1:$Y$413,14,FALSE))

 

[Julie]

I must be doing something wrong - I tried your equation and while it did
succeed in putting blanks where the result was 0, it now does not put blanks
where athe results were error messages. Here is exactly the equation I tried:

=IF(OR(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)),
VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0),"",VLOOKUP(A2,'Data
Base'!$A$1:$Y$413,14,FALSE))

Do you see the problem? Or, can you tell me the easier way?

 

[akphidelt]

I was unaware that excel did that because I thought it evaluated the
expression to True, but it just creates an error... try this

=IF(ISERROR(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)),""
IF(VLOOKUP(A2,'Data Base'!$A$1:$Y$413,14,FALSE)=0,"",VLOOKUP(A2,'Data
Base'!$A$1:$Y$413,14,FALSE)))

 

[Julie]

OK - I added one comma to your equation and it worked - I added it right
after the first "" in the equation.

Thanks a bunch!

 

[akphidelt]

Nice call,

glad it worked

OK - I added one comma to your equation and it worked - I added it right
after the first "" in the equation.

Thanks a bunch!

 

[Dave Peterson]

I bet you really only want to hide that 0 only if the "sending" cell's value was
empty. You'd still want to see the 0 if that's what the cell contained.

If I'm right:

=if(isna(vlookup(...)),"",if(vlookup(...)="","",vlookup(...))))

or

=if(iserror(1/len(vlookup(...)),"",vlookup(...))