CODEWITHSUNDEEP
excel
StarLlama
StarLlama

Reputation: 390

How to find the row number of all occurances of a text value in excel column

I'm really not familiar at all with excel, so your help is greatly appreciated.

Here, I want to find the row numbers of the first occurrence of the text value "dll_ctrl0", the second row index, etc...

For example, in the picture below, I'd like an expression that would return the first occurrence (35), another expression that would return the second (36), and so on...

So far, I've been able to find the row index of the FIRST occurrence using the expression: =MATCH("dll_ctrl0",'random_sheet_name'!C:C,0). This returns 35. I also want an expression that will return 36, one that returns 37 (etc...), all corresponding to the picture below.

I'd like to find the row numbers of all occurrences of "dll_ctrl0", for example.

Upvotes: 0

Views: 4230

Answers (2)

Gary's Student
Gary's Student

Reputation: 96781

Say we have data like:

enter image description here

and we want all the rows for Springfield. We will use MATCH() to find the first row. We will use a second MATCH() to find the next row. The second match will begin just below the first one. We will continue until there are no more matches. In D1 we enter:

 Springfield

In D2 enter:

=MATCH($D$1,A:A,0)

In D3 enter:

=MATCH($D$1,INDEX(A:A,D2+1):INDEX(A:A,9999),0)+D2

and copy D3 downwards:

enter image description here

Once we hit the error we can stop. Each formula relies on the formula above it. Because the formulas use only the MATCH() and INDEX() functions, this approach can be use with older as well as newer versions of Excel.

Upvotes: 2

user4039065
user4039065

Reputation:

Try,

=aggregate(15, 6, row($1:$999)/('random_sheet_name'!C$1:C$999="dll_ctrl0"), row(1:1))

Fill down for subsequent row numbers. Wrap in IFERROR if you don't want error codes when you run out of matches.

Upvotes: 0

Related Questions