I have the array formula below which gives me a result when there is data but
when the data is missing, which is what I am checking for I get "#N/A" I
would like it to be a blank cell, ("") I have tried using "=if(isna( " and
combination, but get a too many arguments for this function error

{=index(Lpilot,match(1,(text(ldate,"ddmmyy")=text(#a136,"ddmmyy"))*(B$129=Ltype),0))}

any ideas / help

SPB

Try this (array entered):

=IF(ISNA(MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)),"",INDEX(Lpilot,MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)))

In the formula you posted you have:

#a136

I assume that's supposed to be: $A136

--
Biff
Microsoft Excel MVP


"Excel 2007 - SPB" <(E-Mail Removed)> wrote in message
news:0C3FFBE8-2697-4A3B-96F1-(E-Mail Removed)...
>I have the array formula below which gives me a result when there is data
>but
> when the data is missing, which is what I am checking for I get "#N/A" I
> would like it to be a blank cell, ("") I have tried using "=if(isna( " and
> combination, but get a too many arguments for this function error
>
> {=index(Lpilot,match(1,(text(ldate,"ddmmyy")=text(#a136,"ddmmyy"))*(B$129=Ltype),0))}
>
> any ideas / help
>
> SPB

Thanks, I though I tried this but it works now! I tried to paste & copy the
repeated info, maybe it did not like my time saver...

SPB

"T. Valko" wrote:

> Try this (array entered):
>
> =IF(ISNA(MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)),"",INDEX(Lpilot,MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)))
>
> In the formula you posted you have:
>
> #a136
>
> I assume that's supposed to be: $A136
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Excel 2007 - SPB" <(E-Mail Removed)> wrote in message
> news:0C3FFBE8-2697-4A3B-96F1-(E-Mail Removed)...
> >I have the array formula below which gives me a result when there is data
> >but
> > when the data is missing, which is what I am checking for I get "#N/A" I
> > would like it to be a blank cell, ("") I have tried using "=if(isna( " and
> > combination, but get a too many arguments for this function error
> >
> > {=index(Lpilot,match(1,(text(ldate,"ddmmyy")=text(#a136,"ddmmyy"))*(B$129=Ltype),0))}
> >
> > any ideas / help
> >
> > SPB
>
>
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PSB" <(E-Mail Removed)> wrote in message
news:510D686A-FC45-4A29-9717-(E-Mail Removed)...
> Thanks, I though I tried this but it works now! I tried to paste & copy
> the
> repeated info, maybe it did not like my time saver...
>
> SPB
>
> "T. Valko" wrote:
>
>> Try this (array entered):
>>
>> =IF(ISNA(MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)),"",INDEX(Lpilot,MATCH(1,(TEXT(ldate,"ddmmyy")=TEXT($A136,"ddmmyy"))*(B$129=Ltype),0)))
>>
>> In the formula you posted you have:
>>
>> #a136
>>
>> I assume that's supposed to be: $A136
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Excel 2007 - SPB" <(E-Mail Removed)> wrote in
>> message
>> news:0C3FFBE8-2697-4A3B-96F1-(E-Mail Removed)...
>> >I have the array formula below which gives me a result when there is
>> >data
>> >but
>> > when the data is missing, which is what I am checking for I get "#N/A"
>> > I
>> > would like it to be a blank cell, ("") I have tried using "=if(isna( "
>> > and
>> > combination, but get a too many arguments for this function error
>> >
>> > {=index(Lpilot,match(1,(text(ldate,"ddmmyy")=text(#a136,"ddmmyy"))*(B$129=Ltype),0))}
>> >
>> > any ideas / help
>> >
>> > SPB
>>
>>
>>