Match function in reverse direction

DrDebit

Board Regular
Joined
May 20, 2013
Messages
72
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
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
How about
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">X</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Program</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Program</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">R</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">S</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Here</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">X</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Program</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Program</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">R</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">S</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">A1:INDEX(<font color="Green">A1:A10,MATCH(<font color="Purple">"here",A1:A10,0</font>)</font>)="Program",ROW(<font color="Green">A1:A10</font>),""</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,122
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

Active Member
Joined
Mar 5, 2018
Messages
434
Office Version
2016
Platform
Windows
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
Joined
Oct 17, 2012
Messages
381
As per the OP, Is the position 3 or 7 ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
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
Joined
Dec 14, 2016
Messages
2,605
Office Version
365, 2016
Platform
Windows
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
Joined
Oct 17, 2012
Messages
381
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
Joined
Oct 17, 2012
Messages
381
How about
Wondering why i am getting 8.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Data</td><td style="font-weight: bold;text-align: center;;">O-Position</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;color: #333333;;">X</td><td style="text-align: center;color: #333333;;">8</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;color: #333333;;">Y</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">4</td><td style="text-align: center;color: #333333;;">Program</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">5</td><td style="text-align: center;color: #333333;;">A</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">6</td><td style="text-align: center;color: #333333;;">B</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">7</td><td style="text-align: center;color: #333333;;">C</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">8</td><td style="text-align: center;color: #333333;;">Program</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">9</td><td style="text-align: center;color: #333333;;">R</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">10</td><td style="text-align: center;color: #333333;;">S</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">11</td><td style="text-align: center;color: #333333;;">Here</td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(102,0,0)"><thead><tr style=" background-color: rgb(255,255,255);color: rgb(102,0,0)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">B2</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">A2:INDEX(<font color="Green">A2:A11,MATCH(<font color="Purple">"here",A2:A11,0</font>)</font>)="Program",ROW(<font color="Green">A2:A11</font>),""</font>),1</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,443
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,084,839
Messages
5,380,211
Members
401,655
Latest member
bevercam

Some videos you may like

This Week's Hot Topics

Top