How to rebuild this formula without the (volatile) INDIRECT function?

I have this formula:

=MATCH("ON",INDIRECT("$A$"&$A14+1&":$A$500"),0)+A14

..which looks for the word "ON" in column A froms rows 25 to 500. For reasons I won't bore you with, cell A14 contains the number 25.

I've recently learnt that, sadly, INDIRECT is volatile. How could I reconstruct this formula using a non-volatile function?


Background:

I've discovered I can use:

=INDEX(A:A,A14)

..to target a specific cell, but I don't know how to transform that into referencing a full range, as per the above.


As always, all help appreciated and I'll give thanks and happily mark the thread as solved when appropriate!

How about

Formula:

Please Login or Register  to view this content.

Superb. I can't believe the syntax was that simple! Thank you so much

You're welcome & thanks for the feedback.