is blank AND is error

  • Thread starter Thread starter jane
  • Start date Start date
J

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
 
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)))
 
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:
 
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 ...
 
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(...))
 
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)))
 
That did it! thanks!

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

take care, Jane
 
It sounds like the cell's value that you were returning really had that divide
by 0 error.
 
Back
Top