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)))

--
Biff
Microsoft Excel MVP


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

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:

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

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 ...

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


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

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

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(...))



jane wrote:

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

--

Dave Peterson

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)))

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

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)))

--
Biff
Microsoft Excel MVP


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

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" wrote:

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

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(...))



jane wrote:

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

--

Dave Peterson

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

jane wrote:

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" wrote:

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

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(...))



jane wrote:

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

--

Dave Peterson


--

Dave Peterson