Finding first cell after having done that in another column

[Red over White]

Having looked on Mr Excel I have found the formula



=MIN(ISNUMBER(SEARCH(R2,'All results'!A3170:A3179)),ROW('All results'!A3170:A3179))



which goes some way to solving my issue as it comes up with 3170. R2 = “2023-24”

Row	Column A	Column B
3169	2022-23
3170	2023-24
3171	2023-24
3172	2023-24	National
3173	2023-24	National

What I am try to do is hone in on cell B3172, the first cell where Column A is equal to Cell R2 and then the first cell in Column B which has an entry (can be anything).

Any suggestions?

 

[Fluff]

How about

=INDEX('All results'!B:B,AGGREGATE(15,6,ROW('All results'!A3170:A3179)/(R2='All results'!A3170:A3179)/('All results'!B3170:B3179<>""),1))

 

[Red over White]

Its coming up with what is in the cell rather than the row number

 

[Red over White]

NATIONAL.xlsm
	E
3	National Division (North) 2
All results

Cell Formulas
Range	Formula
E3	E3	=INDEX(B:B,AGGREGATE(15,6,ROW(A1:A12)/(ISNUMBER(SEARCH(E1,A1:A12)))/(B1:B12<>""),1))

 

[Red over White]

NATIONAL.xlsm
	A	B	C	D	E	F
1	2022-23				2023-24
2	2022-23
3	2023-24				National Division (North) 2
4	2023-24	National Division (North) 2
5	2023-24	National Division (North) 2
6	2023-24	National Division (North) 2
7	2023-24	National Division (North) 2
8	2023-24	National Division (North) 2
9	2023-24	National Division (North) 2
10	2023-24	National Division (North) 2
11	2023-24
12	2023-24
13
All results

Cell Formulas
Range	Formula
E3	E3	=INDEX(B:B,AGGREGATE(15,6,ROW(A1:A12)/(ISNUMBER(SEARCH(E1,A1:A12)))/(B1:B12<>""),1))

 

[Fluff]

If you want the row number use

=AGGREGATE(15,6,ROW(A1:A12)/(E1=A1:A12)/(B1:B12<>""),1)