Find first value in a range greater than a reference cell value

steveTu · November 26, 2024, 9:00am

I have a range of cells containing values in no order. I want to scan back up (or down) the list and find the first cell value greater than the reference cell.
IE a1:a10 contains 3,7,12,2,5,8,24,32,6,3 and a11 contains 15. I want to find the first cell in the range a1:a10 has a value greater than the value in a11. So if searching ascending I want a8 and if descending a7.

karolus · November 26, 2024, 10:13am

Hallo

=INDEX(FILTER(A1:A10;A1:A10>A11);1)    #for the first value >A11
=LET(x;FILTER(A1:A10;A1:A10>A11);INDEX(x;COUNT(x)))  # for the last value >A11

both Formulas for LO24.8 or later!!

steveTu · November 26, 2024, 10:56am

Thanks for that. But I’m on 24.2.7.2 - apologies - I should have stated that in my initial post.

karolus · November 26, 2024, 11:13am

=INDEX(A1:A10;MINIFS(ROW(A1:A10);A1:A10;">"&A11))  #first …
=INDEX(A1:A10;MAXIFS(ROW(A1:A10);A1:A10;">"&A11)) #last …

steveTu · November 28, 2024, 7:27am

Thanks for the input, but I get a ‘VALUE!’ error from that. My actual code is:
=INDEX(B93:B267,MINIFS(ROW(B93:B267),B93:B267,">"&B268))

karolus · November 28, 2024, 7:49am

…MINIFS(ROW(B1:B175),B93:B267,">"&B268))

mikekaganski · November 28, 2024, 8:24am

In this latter form, it would become difficult to keep the number of rows in sync. Despite looking more complex, this form could be a bit simpler in maintenance:

MINIFS(ROW(B93:B267)-ROW(B93)+1;B93:B267;">"&B268))

steveTu · November 29, 2024, 5:24pm

I’m doing something wrong, as I’m getting either Value errors or zero. Thanks though both for your help. I’ll play around a bit now you’ve given me some pointers.