is blank AND is error

[jane]

This is my formula

'=IF(ISBLANK(VLOOKUP($L11,'Past RDW
'!$L$3:$AI$780,16,FALSE)),"",(VLOOKUP($L11,'Past RDW
'!$L$3:$AI$780,16,FALSE)))

I am getting #NA when there is not a match so need to have a combo of
isblank and iserror... is this possible?

thanks in advance! jane

 

[T. Valko]

Try this:

=IF(COUNTIF('Past RDW'!$L$3:$L$780,$L11)=0,"",
IF(VLOOKUP($L11,'Past RDW'!$L$3:$AI$780,16,0)="","",
VLOOKUP($L11,'Past RDW'!$L$3:$AI$780,16,0)))

 

[NeedToKnow]

Hi Jane,
sorry, I can't help but I'm wondering the same problem.

A B C D E F
date km litre *l/100km litre2 **l/100km

Only problem I have is that column E might have various amount (1-9) blank
cells and total usage must be counted from previus km that e-column has
number.

Let's hope someone has solution in this problem

"jane" kirjoitti:

 

[jane]

Hi there,
I get all blanks now.

TO add (if this helps), I am trying to retrieve text although not sure if it
is formatted as text ...

 

[Dave Peterson]

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

You could even make it a little more informative:
=if(isna(vlookup(...)),"No match",if(vlookup(...)="","",vlookup(...)))

And if you really wanted to return "" if either there was no match or the cell
was empty:

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

 

[T. Valko]

Hmmm...

That should work.

Try this:

=IF(ISNA(MATCH($L11,'Past RDW'!$L$3:$L$780,0)),"",IF(VLOOKUP($L11,'Past
RDW'!$L$3:$AI$780,16,0)="","",VLOOKUP($L11,'Past RDW'!$L$3:$AI$780,16,0)))

 

[jane]

That did it! thanks!

(ps... I changed the ISNA to ISERROR to take care on a DIVO that showed up)

take care, Jane

 

[Dave Peterson]

It sounds like the cell's value that you were returning really had that divide
by 0 error.