Hi Everybody

I have this array formula which when entered in a cell evaluates to a number. Howerver, if I wrap this function in an 'if' condition where I am essentially testing for it evaluating to an error value and then executing the 'if' depending upon whether the result is evaluated as an error or not (see below for details), it evaluates to an error condition (when the function actually evaluates to a numeric value - hence not an error value) - and executes the TRUE part of the 'if' conditon rather than the FALSE part of the 'if' condition.

The formula is like so :-

=--LARGE(OFFSET(INDIRECT("'Data'!"&ADDRESS((MATCH(CONCATENATE($B117,$C117,$D117,$A117),(CONCATENATE(INDEX(LockCountbyType,,1),INDEX(LockCountbyType,,2),INDEX(LockCountbyType,,3),INDEX(LockCountbyType,,6))),0)+ROW(LockCountbyType)-1),COLUMN(LockCountbyType))),0,4,SUMPRODUCT(--(INDEX(LockCountbyType,,1)=$B117),--(INDEX(LockCountbyType,,2)=$C117),--(INDEX(LockCountbyType,,3)=$D117),--(INDEX(LockCountbyType,,6)=$A117)),1),AC$107)

It is array entered by CSE.

If the above formula is array entered in say cell A1, it evaluates to 93. So far so good. But when I enter the above formula (again array entered) like so :-

IF(ISERROR(--LARGE(OFFSET(INDIRECT("'Data'!"&ADDRESS((MATCH(CONCATENATE($B117,$C117,$D117,$A117),(CONCATENATE(INDEX(LockCountbyType,,1),INDEX(LockCountbyType,,2),INDEX(LockCountbyType,,3),INDEX(LockCountbyType,,6))),0)+ROW(LockCountbyType)-1),COLUMN(LockCountbyType))),0,4,SUMPRODUCT(--(INDEX(LockCountbyType,,1)=$B117),--(INDEX(LockCountbyType,,2)=$C117),--(INDEX(LockCountbyType,,3)=$D117),--(INDEX(LockCountbyType,,6)=$A117)),1),AB$107)), 0, 1)

it evaluates to '0' - the iserror() get evaluated as TRUE when I think it should evaluate to FALSE!

Any suggestions on this please!!!!!!!!



Best regards



Deepak Agarwal