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!

 

[Fluff]

How about

Book1
	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
Sheet2

Cell Formulas
Range	Formula
C2	{=LARGE(IF(A1:INDEX(A1:A10,MATCH("here",A1:A10,0))="Program",ROW(A1:A10),""),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Maybe just:

=LOOKUP(2,1/(A1:A9="Program"),ROW(A1:A9))

This will give you the row number of the last time Program is in A1:A9.

 

[Toadstool]

Hi DrDebit,

You can do it with AGGREGATE and use it's LARGE option to find the 1st largest row with a match.

	A	B	C
X
Y
Program
A
B
C
Program
R
S
Here

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=AGGREGATE(14,6,ROW($A$1:$A$12)/($A$1:$A$12="Program"),1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

As per the OP, Is the position 3 or 7 ?

 

[Fluff]

OP is asking for the first occurrence above the word "here", which is 7

I've worked on the basis there may be more data below the word "here", the other two are just pulling the last occurrence within the range

 

[Scott T]

OP is asking for the first occurrence above the word "here", which is 7

I've worked on the basis there may be more data below the word "here", the other two are just pulling the last occurrence within the range

If match is working in reverse then the line with here would be 1 and the row above would be 2...
So is it just the direction of the lookup that is reversed are both the direction and the row numbering?

 

[Sam_D_Ben]

If match is working in reverse then the line with here would be 1 and the row above would be 2...
So is it just the direction of the lookup that is reversed are both the direction and the row numbering?

Yeah, Cause the OP said going up instead of down.

 

[Sam_D_Ben]

How about

Wondering why i am getting 8.

Excel 2013/2016
	A	B
1	Data	O-Position
2	X	8
3	Y
4	Program
5	A
6	B
7	C
8	Program
9	R
10	S
11	Here
Sheet1

Cell Formulas
Range	Formula
B2	{=LARGE(IF(A2:INDEX(A2:A11,MATCH("here",A2:A11,0))="Program",ROW(A2:A11),""),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

If match is working in reverse then the line with here would be 1 and the row above would be 2...
So is it just the direction of the lookup that is reversed are both the direction and the row numbering?

Excellent point, I assumed the OP wanted the actual row number, but I could easily be wrong.
We'll have to wait for the OP to clarify.