Match function in reverse direction

[DrDebit]

Is it possible to use the MATCH function to find the ordinal position of text, but going up instead of going down.

X
Y
Program
A
B
C
Program
R
S
Here

In the cell with "Here" in it, I want to be able to find the position of the first time that the word, "Program," appears, but going up instead of down.

Thank you!

 

[Toadstool]

Actually I'd misunderstood the question and was giving the last entry for "Project".
If OP needs the last "Project" before "Here" then I think this is it:

Somebody messaged me saying the above didn't work, but it was a misunderstanding of the old posting format. Here is the working XL2BB version.

DrDebit.xlsx
	A	B
1	Data	Position
2	X	10
3	Program
4	A
5	B
6	C
7	Program
8	R
9	R
10	Program
11	R
12	R
13	Here
14	R
15	Program
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AGGREGATE(14,6,ROW(INDIRECT("$A$1"&":"&ADDRESS(MATCH("Here",$A$1:$A$15,0),1)))/($A$1:$A$15="Program"),1)

 

[Fluff]

Or without volatile functions May need CSE entry depending on version

+Fluff 1.xlsm
	A	B	C
1	X
2	Y		7
3	Program
4	A
5	B
6	C
7	Program
8	R
9	S
10	Here
11	X
12	Y
13	Program
14	A
15	B
16	C
17	Program
18	R
19	S
Data

Cell Formulas
Range	Formula
C2	C2	=LARGE(IF(A1:INDEX(A1:A10,MATCH("here",A1:A10,0))="Program",ROW(A1:A10),""),1)

 

[Rahul1987]

Or without volatile functions May need CSE entry depending on version

+Fluff 1.xlsm
	A	B	C
1	X
2	Y		7
3	Program
4	A
5	B
6	C
7	Program
8	R
9	S
10	Here
11	X
12	Y
13	Program
14	A
15	B
16	C
17	Program
18	R
19	S
Data

Cell Formulas
Range	Formula
C2	C2	=LARGE(IF(A1:INDEX(A1:A10,MATCH("here",A1:A10,0))="Program",ROW(A1:A10),""),1)

Thanks to both of you.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Rahul1987]

Thanks to both of you.

Hi Fluff,

As soon as I increase by range , formula does not recognize it ( i.e. I7 & I8 )

Book1
	A	B	C	D	E	F	G	H	I
2	X	10		#N/A
3	Program
4	A					10		A	Program
5	B		#VALUE!
6	C				Here
7	Program								#N/A
8	R				10				#N/A
9	R
10	Program
11	R
12	R
13	Here
14	R
15	Program
Sheet1

Cell Formulas
Range	Formula
B2	B2	=AGGREGATE(14,6,ROW(INDIRECT("$A$1"&":"&ADDRESS(MATCH("Here",$A$1:$A$15,0),1)))/($A$1:$A$15="Program"),1)
D2	D2	=LARGE(IF(A1:INDEX(A1:A10,MATCH("here",A1:A10,0))="Program",ROW(A1:A10),""),1)
F4,E8	F4	=AGGREGATE(14,6,ROW(INDIRECT("A2"&":"&ADDRESS(MATCH("Here",$A$2:$A$15,0),1)))/($A$2:$A$15="Program"),1)
H4	H4	=A2:INDEX($A$2:$A$15,COUNT(A2:A15))
C5	C5	=AGGREGATE(14,6,INDEX(A2:INDEX($A$2:$A$15,MATCH("Here",$A$2:$A$15,0),1)/($A$2:$A$15="Program"),1))
E6	E6	=INDEX($A$2:$A$15,MATCH("Here",$A$2:$A$15,0))
I7	I7	=LARGE(IF(A1:INDEX($A$1:$A$15,MATCH("Here",$A$1:$A$15,0))="Program",ROW(A1:A15),""),1)
I8	I8	=LARGE(IF(A1:INDEX(A1:A10,MATCH("here",A1:A10,0))="Program",ROW(A1:A10),""),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

=AGGREGATE(14,6,ROW(A1:INDEX(A1:A20,MATCH("here",A1:A20,0)))/(A1:A20="Program"),1)