Formula to return order of occurrence in a range

LWell

New Member
Joined
Jun 1, 2015
Messages
20
Hi all,

Suppose I have a spreadsheet that looks something like this

File1004"Input Cell" ->1006
File1005"Return Occurrence" ->3
File1006
File1007
etc.

<tbody>
</tbody>

I need a formula in C2 that looks through column A to find the cell that contains "File"&C1. This can be done easily by using an Index Match formula (=INDEX(A:A,MATCH("*"&C1,A:A,0))).

What I need it to return, however, is what occurrence of "File*" has just been found. This could be done using another MATCH formula if they were sequential and the first file began on row 1. Unfortunately, there is other stuff in column A between the "File*" cells. In my previous example, there could be two rows between "File1004" and "File1005." Then six rows between "File 1005" and "File 1006".

To summarize: I need a formula in C2 that says "Cell C1 is 1028. "File1028" is the 37th occurrence of "File*" in column A if counting from the top. Return value "37".

Thank you all for any help! I hope I explained that well. I'm happy to clarify if you have any questions.
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

Just dump the INDEX part of your formula


=MATCH("*"&C1,A:A,0)
 

LWell

New Member
Joined
Jun 1, 2015
Messages
20
Just dump the INDEX part of your formula

I wish I could :( that's what I was trying to clarify in my second paragraph. If the list of "File*"s started in row 1 and occurred every row, it would be easy and I could simply use your formula. However, there are sporadic, other data in between each instance of "File*" in column A. Because of that, I really need to look through column A, count the "File*" cells and return the number of the order of my cell's occurrence.
 

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
Hi,

OK so we still need to find the row but we can then use that to create a range using indirect and count the instances of File*


=COUNTIF(INDIRECT("A1:A"&MATCH("*"&C1,A:A,0)),"File*")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Control+shift+enter, not just enter:

=MATCH("File"&C1,IF(ISNUMBER(SEARCH("|"&"File","|"&$A$1:$A$100)),$A$1:$A$100,0))

Is this what you are after?
 

LWell

New Member
Joined
Jun 1, 2015
Messages
20
Mike, that works absolutely perfectly! Thank you so much!

Aladin, yours returns the row number that the cell is in which is helpful but not quite what I was looking for. Thank you for the help!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,711
Office Version
  1. 2010
Platform
  1. Windows
Does this normally-entered, non-Volatile formula give you the result you are looking for...

=COUNTIF(INDEX(A:A,1):INDEX(A:A,MATCH("File"&C1,A:A,0)),"File*")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,119
Messages
5,599,821
Members
414,341
Latest member
Mohammedsobhey

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
Top