Hi all,
Suppose I have a spreadsheet that looks something like this
<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.
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.
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.
