How to apply correctly INDIRECT in array with long formulas

Without modifying your base formula...

Formula:

=MIN(IF(INDIRECT("$E37:$I1255")=INDIRECT("$AN$35"),ROW(INDIRECT("$C37:$C888"))-ROW(INDIRECT("$C37"))+0))

Hi jhren,

It worked, Thank you so much !!


Regards

Now that you know it works, you can change the part...
ROW(INDIRECT("$C37:$C888"))-ROW(INDIRECT("$C37"))+0))
...to...
ROW(INDIRECT("1:"&888-37))))

And I do hope you realize you will get an error when the formula processes I889 through I1255... because there will be no corresponding row value in the [value if true] parameter of the IF statement....???

Hi jrhen,

It's very interesting and I did change the part, but it give me value 6.
(is supposed to be 5,) It might happen because was eliminated the +0 ??


Regards

Nah, my bad... or yours for formula etiquette, can't tell, but I'd rather blame it on you.

Anyway, it should have been...
ROW(INDIRECT("37:888"))-37))
...as your first returned value of the IF array should be either 0 or FALSE. I had errantly assumed it was 1, which is the difference between the returned 6 and should-be 5.

Hi jhren,


Yes sir, the -37 made the difference..



Thank you jhren