I am having a problem error proofing this formula. The formula as listed
successfully takes a heading and concatenates some text onto it and then goes
into a second detail sheet. It takes the value in column RC5 which is the
highest €œAll: Net Profit€�, finds those values (possible multiple high values)
in the €œAll: Net Profit€� detail column, then selects the minimum value in the
column with the desired heading. When the column does not exist in the
second sheet a #NA is returned.

{=IF(RC5="","",MIN(IF(INDIRECT("'"&RC2&"'!r2c"&MAT CH("All: Net
Profit",INDIRECT("'"&RC2&"'!r1",FALSE),0)&":r8001c "&MATCH("All: Net
Profit",INDIRECT("'"&RC2&"'!r1",FALSE),0),FALSE)=R C5,INDIRECT("'"&RC2&"'!r2c"&MATCH(INDIRECT("R2C"&T RUNC((COLUMN()-1)/7)*7+1,FALSE)&"
("&R1C1&")",INDIRECT("'"&RC2&"'!r1",FALSE),0)&":r8 001c"&MATCH(INDIRECT("R2C"&TRUNC((COLUMN()-1)/7)*7+1,FALSE)&"
("&R1C1&")",INDIRECT("'"&RC2&"'!r1",FALSE),0),FALS E))))}

I am trying to trap the #NA by inserting this IF before the MIN.

IF(ISNA(MATCH(INDIRECT("R2C"&TRUNC(COLUMN()/7)*7+1,FALSE),'302b'!R1,0)),"",

In my thinking this should return a blank if the column heading is not
matched. On a non-array it works and returns a blank. However, when the
array braces are added, the else clause is always executed. The exact
failure is the COLUMN() function when used in an array formula. It is needed
to operate correctly as an array because the remainder of the formula is an
array.

For testing I have minimized the problem to this formula. When the heading
does not exist, this returns €œna€� only as a non-array formula. It does not
return "na" as an array formula.

{=IF(ISNA(MATCH(INDIRECT("R1C"&COLUMN(),FALSE),'30 2z'!R1,0)),"na","ok")}

Without the COLUMN() it returns €œna€� with or without being an array (as it
should if the heading does not exist in the detail sheet).

{=IF(ISNA(MATCH(INDIRECT("R1C2",FALSE),'302z'!R1,0 )),"na","ok")}

TEST CASE
Sheet 1
Switch,Head1
302z,{formula}

Sheet 302z
Head0,Head3

ERROR.TYPE function yields the same results.
=IF(ERROR.TYPE(MATCH(INDIRECT("R1C"&COLUMN(),FALSE ),'302z'!R1,0))=7,"na","ok")

ISERROR always yields "na" as an array, even when the heading exists in the
detail sheet. It does work correctly as a non-array function.
=IF(ISERROR(MATCH(INDIRECT("R1C"&COLUMN(),FALSE),' 302z'!R1,0)),"na","ok")

Any suggestions would be appreciated.