Array formula & #N/A
 

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

Array formula & #N/A
 

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

Array formula & #N/A
 

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

Array formula & #N/A
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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