# Match function in reverse direction

#### DrDebit

##### Board Regular
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

##### MrExcel MVP, Moderator
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Last edited:

#### Eric W

##### MrExcel MVP
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.

##### Active Member
Hi DrDebit,

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

ABC
1X
2Y
3Program7
4A
5B
6C
7Program
8R
9S
10Here

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
C3=AGGREGATE(14,6,ROW(\$A\$1:\$A\$12)/(\$A\$1:\$A\$12="Program"),1)

<tbody>
</tbody>

<tbody>
</tbody>

#### Sam_D_Ben

##### Active Member
As per the OP, Is the position 3 or 7 ?

#### Fluff

##### MrExcel MVP, Moderator
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

##### Well-known Member
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

##### Active Member
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

##### Active Member
Wondering why i am getting 8.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### Fluff

##### MrExcel MVP, Moderator
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.