Match function in reverse direction

DrDebit

Board Regular
Joined
May 20, 2013
Messages
123
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
How about

Book1
ABC
1X
2Y7
3Program
4A
5B
6C
7Program
8R
9S
10Here
11X
12Y
13Program
14A
15B
16C
17Program
18R
19S
Sheet2
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
How about

Wondering why i am getting 8.


Excel 2013/2016
AB
1DataO-Position
2X8
3Y
4Program
5A
6B
7C
8Program
9R
10S
11Here
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top