Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Array Formula | Excel Discussion (Misc queries) | |||
Array Formula Help | Excel Discussion (Misc queries) | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |