Finding the first instance of a given number in a long column from the bottom up

[Marvo]

If you have a column of numbers, is there a way of looking up from the bottom to find the first instance of a number higher than the one you place in a box below?

So in the example picture, A11 would be the cell you'd want.

 

[shift-del]

Hi Marvo

Mappe15
	A	B	C
1	12
2	13
3	23
4	12
5	17
6	19
7	16
8	21
9	16
10	23
11	13
12	16
13	21
14	20
15	25
16	12
17	15
18	21
19	24
20	15
21	13
22	20
23	25
24	22
25
26
27
28	21	22	22
Tabelle1

Cell Formulas
Range	Formula
B28	B28	=LOOKUP(9^99,FILTER(A1:A24,A1:A24>A28))
C28	C28	=TAKE(FILTER(A1:A24,A1:A24>A28),-1)

 

[Scott Huish]

Hi Marvo

Mappe15
	A	B	C
1	12
2	13
3	23
4	12
5	17
6	19
7	16
8	21
9	16
10	23
11	13
12	16
13	21
14	20
15	25
16	12
17	15
18	21
19	24
20	15
21	13
22	20
23	25
24	22
25
26
27
28	21	22	22
Tabelle1

Cell Formulas
Range	Formula
B28	B28	=LOOKUP(9^99,FILTER(A1:A24,A1:A24>A28))
C28	C28	=TAKE(FILTER(A1:A24,A1:A24>A28),-1)

TAKE AND FILTER aren't available in Excel 2021. Perhaps:
=LOOKUP(9^99,IF(A1:A24>A28,A1:A24))

Confirm with CTRL-SHIFT-ENTER instead of just Enter.

 

[Marvo]

Thank you both.

Its not quite what I'm after, what I would like is a result of where to find the answer as the actual column is over 5,000 rows long.

So the preferred result in the example I gave would be A11. If I could get that I may even be able to create a hyperlink to take me to that cell?

 

[Fluff]

How about

=ADDRESS(XMATCH(A28+0.5,A1:A24,1,-1),1,4)

 

[Marvo]

Thanks Fluff, that's brilliant, job done. Thanks to the the guys too, I'll leave you all in peace.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Marvo]

As a bit more feedback, excel changed your formula when I moved it to the big workbook to
=ADDRESS(XMATCH(A28+0.5,A1:A24,1,-1)*1,4)

It still works.

 

[Fluff]

You're missing a comma, & that should return an address in col D not A

 

[Marvo]

Yes, excel said it had made a change and would I accept it.

I've also hit a snag. I need to find the first time the main cell is higher than, not just one more than. I'm searching for more than 10 but the formula is looking for the first instance of 11.
I also have to do the reverse and find the first instance lower than 10, even if that is say 6 or 7.

Most times the result of this search wont matter as it will be very close to the bottom of the column, its only when the team is on a very good or very poor run that this will be used.