Very difficult to explain, but I have a formula (presented below) which is being used for 1000+ cells (it was simply dragged across all the cells, so there are no differences except relative references). In all cases, except for 2, it is returning the correct results. On those 2 results, the formula builder suggests it is being correctly calculated, however, I know the result of all the "IF" statements therein are FALSE, so the return should be 0. Pressing F9 (on keyboard) shows that when it gets to the IF being FALSE in the final stage, the possible results (on these 2 anomalies only) are not "0" as required (as in all other cells) but the array of values which are possible when IF statement is TRUE. The 'Sheet1' references are simply to the grid of possible results (when there is a TRUE). I have no idea what is going wrong having examined the formula multiple times.
=IFERROR(IF(AT69="top 5",INDEX('Sheet1'!$E$69:$E$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="top 10",INDEX('Sheet1'!$D$69:$D$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="top 3",INDEX('Sheet1'!$F$69:$F$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="> top 10",INDEX('Sheet1'!$C$69:$C$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),0)))),0)
=IFERROR(IF(AT69="top 5",INDEX('Sheet1'!$E$69:$E$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="top 10",INDEX('Sheet1'!$D$69:$D$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="top 3",INDEX('Sheet1'!$F$69:$F$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),IF(AT69="> top 10",INDEX('Sheet1'!$C$69:$C$75,SUMPRODUCT(--(AG69<='Sheet1'!$B$69:$B$75), --(AG69>='Sheet1'!$A$69:$A$75), ROW($A$1:$A$7))),0)))),0)
