Return row number from Excel sheet based on filters

Question

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?

I think this is exactly what you need: exceljet.net/formula/get-nth-match — M.Douda

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2018-07-24T09:59:54

=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

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

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

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

Return row number from Excel sheet based on filters

1 Answers