Formula to return order of occurrence in a range

[LWell]

Hi all,

Suppose I have a spreadsheet that looks something like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]File1004[/TD]
[TD="align: right"]"Input Cell" ->[/TD]
[TD]1006[/TD]
[/TR]
[TR]
[TD]File1005[/TD]
[TD="align: right"]"Return Occurrence" ->[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]File1006[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File1007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Mike LH]

Hi,

Just dump the INDEX part of your formula


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

 

[LWell]

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]

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]

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]

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]

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*")

 

[LWell]

Does this normally-entered, non-Volatile formula give you the result you are looking for...

Yes, Rick, it does! Thanks for looking into that. Both yours and Mike's answers work exactly how I want them.