Return row number from Excel sheet based on filters

I have the following excel file with two sheets namely Sheet1 and Sheet2. Sheet1 contains few names with repetitions like below.

Column E
-------- ----------
Row 3 tom
Row 4 jerry
Row 5 mick
Row 6 tom
Row 7 john
Row 8 mike
Row 9 mick
Row 10 eric
Row 11 matt
Row 12 mike


I want to be able to determine the row in which, for example, the second occurrence of the name "Pete" occurs. For this I have to set up a new worksheet (Sheet2) that will allow me to enter a person's name and a positive integer (such as n), and returns the row in which the name occurs for the nth time.**

Enter Name : tom (cell B1)
Enter Integer :

Result : `2`


For the result cell I have applied the below formula which is returning the no. of times the name occurs.

=COUNTIF(Sheet1!E3:E12,Sheet2!B1)


But I am not been able to find the desired answer.

Can it be done to with Countif, Countifs, Count, CountA, and CountBlank Functions?

Can anyone please help?

1 Answer
1

=AGGREGATE(15,6,1/(myRng=B1)*ROW(myRng),B2)


Explanation

myRng=B1 match each entry in myRng with the name in B1 giving an array of TRUE;FALSE

myRng=B1

TRUE;FALSE

1/… changes that to an array of {DIV/0,1,... depending on whether it matches

1/…

{DIV/0,1,...

*ROW(myRng) converts that to an array of {DIV/0, row_num}

*ROW(myRng)

{DIV/0, row_num

AGGREGATE(15,6,resultant_array,B2) returns the nth smallest value from that array, ignoring the errors

AGGREGATE(15,6,resultant_array,B2)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.