Is there a better way to write this formula?

[dv0x]

Hi Ninjas,

I'm working with 180k rows of data. Sometimes the formula below returns the correct result while other times it returns a #REF!. The data is fine, but perhaps there is too much?

{=IF($A70<>81,"",(IF(H70="OPEN","",INDIRECT("A"&MIN(IF($C2:$C70>$B70,ROW($C2:$C70),MAX($C2:$C70)))))))}

Is there a better way to write this formula that will provide reliable results? I've attached a sample.

Thank you

 

[bosco_yip]

Try,

1] I change your range C2:C69 of which all values lower than cell B70 for testing purpose.

2] Your formula in cell I70 appear failed.

3] My non-array formula show in J70, copy down :

=IF(($A70<>81)+($H70="OPEN"),"",INDEX($A2:$A70,IFERROR(INDEX(MATCH(1,--($C2:$C70>$B70),0),0),MATCH(MAX($C2:$C70),$C2:$C70,0))))

Regards
Bosco

 

[dv0x]

Thank you Bosco. This is helpful.