Formula that returns the first in range

Hello,

I've been working on this for hours trying to find a way via COUNTIF, INDEX, FIND and much more, but my brain is fried.

I have a column of data, say A1:A54 that is going to be nothing but numbers descending. I'm looking to create a formula say in $C$1 that finds the first instance of the number dropping below "-9" in column A's data and then performs the OFFSET function of grabbing the data in the neighboring cell in column B.

I've created a conditional formatting formula with COUNTIF to highlight the row of the first instance, but haven't been successful otherwise.


Thank you for your help.

B.O.B.
Burnt Out Brain.

Try

=INDEX(B:B,MATCH(D1,A:A,-1)+1)

D1=Search value

Hi John,

I've tried that, the issue is, the search value isn't always going to be the same (which would make it so much easier). That's why I'm searching for the first to reach below -9, as sometimes that could be -10 or -2000.

Thank you for your help.

Oh! I thought it would only search only for -9, not anything else.

Thanks!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.