# Referencing the nth occurence of an entry

#### Tom Regensburger

##### New Member
I'm looking to count the number of rows from the top of a range to the row that is occupied by the nth occurrence of a piece of data.

Example:
I would like to know on which row the 2nd occurrence of Greg occurs.
Row # Entry
1 Greg
2 Diane
3 John
4 Greg

The data that I am working with is approximately 250 rows and the entry "Greg" could easily occur 15 or 20 times.

I've concluded that I can accomplish this by creating some dynamic ranges and using the OFFSET and ROWS functions.

However, it seems to me that there should be an easier way and I'm hoping that someone might be able to offer some guidance.

Thanks.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### fairwinds

##### MrExcel MVP
Hi,

Try:

=SMALL(IF(A1:A20=D2,ROW(A1:A20)),D3)

Confirmed with Ctrl + shift + enter.
Book1
ABCDE
1Greg
2DianeNameGreg
3JohnOccurance3
4GregRow5
5Greg
6Diane
7John
8Greg
Sheet2

##### MrExcel MVP
Tom Regensburger said:
I'm looking to count the number of rows from the top of a range to the row that is occupied by the nth occurrence of a piece of data.

Example:
I would like to know on which row the 2nd occurrence of Greg occurs.
Row # Entry
1 Greg
2 Diane
3 John
4 Greg

The data that I am working with is approximately 250 rows and the entry "Greg" could easily occur 15 or 20 times.

I've concluded that I can accomplish this by creating some dynamic ranges and using the OFFSET and ROWS functions.

However, it seems to me that there should be an easier way and I'm hoping that someone might be able to offer some guidance.

Thanks.
Book2
ABCDE
1Entry
2GregGreg24
3DianeGreg37
4John
5Greg
6Jon
7Damon
8Greg
9Jon
10Brian
Sheet1

E2:

=IF(COUNTIF(\$A\$2:\$A\$10,C2)>=D2,SMALL(IF(\$A\$2:\$A\$10=C2,ROW(\$A\$2:\$A\$10)-ROW(\$A\$2)+1),D2),"Does Not Exist")

which is confirmed with control+shift+enter then copied down.

This gives you the position of the target's Nth occurrence relative to the range of interest. If you want the native position, remove the -ROW(\$A\$2)+1 bit from the formula.

Replies
0
Views
392
Replies
1
Views
498
Replies
2
Views
435
Replies
14
Views
1K
Replies
1
Views
618

1,195,712
Messages
6,011,266
Members
441,598
Latest member
chrispaulpearce

### 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.

### Which adblocker are you using?

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

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