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]

Wondering why i am getting 8.

Because my take is the OP wants the row number for the first occurrence of "Program" above the word "here"

 

[Eric W]

My thought is that the OP wants the formula in the cell marked "here". As you said, we need more clarification.

 

[Sam_D_Ben]

I guess Mr OP is looking for something similar to this.

Excel 2013/2016
	A	B
1	Data	Position
2	X	6
3	Y
4	Program
5	A
6	B
7	C
8	Program
9	R
10	S
11	A
12	R
13	Program
14	R
15	A
16	C
17	R
18	R
19	Here
20	R
21	R
22	S
23	C
24	Program
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTA(INDIRECT("A"&MATCH("Here",A2:A50,0)+1):INDEX(INDIRECT("A2:A"&MATCH("Here",A2:A50,0)),LOOKUP(2,1/(INDIRECT("A2:A"&MATCH("Here",A2:A50,0))="Program"),ROW(INDIRECT("A2:A"&MATCH("Here",A2:A50,0)))-MIN(ROW(INDIRECT("A2:A"&MATCH("Here",A2:A50,0)))))+1))-1

 

[DrDebit]

Thank you so much for this.

 

[Fluff]

You have had a number of replies, which one are you referring to?

 

[DrDebit]

Thank you, Eric. This works...in theory...

The problem that I am having is while I get the correct result, it shows up in the cell as a 0. I have checked the function dialogue box, as well as the F9 trick. For both, I get the right answers, but in the cell, I keep getting 0. I have checked the formatting dialogue box, and it keeps coming up 0. Any ideas? Thank you again so much!

 

[Eric W]

That's a poser, I don't know quite what to tell you. Try copying the cell with the formula, then select a cell somewhere that has nothing in it and no formatting, and do a Paste Special > Values, and see what you get. That should tell us if it's a formula or formatting issue. Have you tried looking at the Evaluate Formula tool? Select the cell with the formula, then go to the Formula tab, and click Evaluate Formula, and step through it and see what happens. If that doesn't give any enlightenment, try Toadstool's formula from post 4. It's the same basic idea as mine, but it uses a different function that might work differently.

 

[DrDebit]

Thank you for your patience. I had the cell range to include the cell that it was looking for. When I used the range excluding the "here" cell it worked.

As I told you, the strange thing is when doing the F9 evaluate, I got the right answer, but in the cell, it came up 0.

Regardless, I so appreciate your time and attention to this matter...as well as all the other that helped.

Thank you one and all!!!

Dr. Debit

 

[Toadstool]

try Toadstool's formula from post 4.

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:

	A	B
Data	Position
X
Program
A
B
C
Program
R
R
Program
R
R
Here
R
Program

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

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

 

[DrDebit]

Thank you one and all...it has helped me so much. Kudos and thanks!!