INDEX MATCH SMALL likely function to find nth occurrences in a column

[ollyhughes1982]

Hi,

Please see attached link, where I am trying to find the first 10 unique events that begin with the letter 'S' as well as the dates on which they were first completed. I need to search the range C3:C2002 in the 'All Completed Runs' worksheet and return the relevant events and dates in columns B and C of the 'All Completed Runs - Ssssnakes' worksheet.

I thought that some sort of Index Match or Index Small function would do this, but I am really struggling to find a solution, if anyone might be able to help.

parkrun - My parkrun Record (Ssssnakes).xlsx

Microsoft Excel Workbook

1drv.ms

Thanks!

Also asked on Excel Forum: INDEX MATCH SMALL likely function to find nth occurrences in a column

 

[Fluff]

In A3 put
=UNIQUE(FILTER('All Completed Runs'!C3:C228,LEFT('All Completed Runs'!C3:C228,1)="s"),0)
You can then do a vlookup or index match to get the date

 

[ollyhughes1982]

Hi. Thanks for that, it works perfectly for the events column, but my vlookup doesn't seem to work, it isn't giving the correct dates. Have I missed something, it doesn't seem like the vlookup is picking the correct relevant row for the first instance of each unique event. Link below:

parkrun - My parkrun Record - WORKING.xlsx

Microsoft Excel Workbook

1drv.ms

 

[Fluff]

What is the formula you have used?

 

[ollyhughes1982]

=IFERROR(VLOOKUP(A3,'All Completed Runs'!$C$3:$E$2002,3),"")

Also, is there a way to make the Unique Filter stop at the 10th instance, as I only want the first 10 instances. Thanks again

 

[Fluff]

You have not included the final argument for the vlookup, so it's looking for an approximate.
Try
=IFERROR(VLOOKUP(A3#,'All Completed Runs'!$C$3:$E$2002,3,0),"")

By adding the # after A3 the formula will spill down as many rows as it needs.

 

[ollyhughes1982]

Great, that worked perfectly for the vlookup. What I mean re limiting the the unique filter is that I want it to stop after the tenth 'S' run has been completed, so it doesn't carry on filling the rest of the 'S' runs that I do, below below row 12. is there a way to stop the unique filter at a certain amount? The main reason I ask is that on another list it has a mixture. In that one I want it to list the first three 'B' events and then the first three 'G' events in the same column, so would need the unique filter to stop after 3 in that case. Thanks so much for your help.

 

[Fluff]

How about
=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!C3:C2002,LEFT('All Completed Runs'!C3:C2002,1)="S"),0),SEQUENCE(10)),"")

 

[Kaper]

A "traditional approach" which has been also published on Excelforum.com INDEX MATCH SMALL likely function to find nth occurrences in a column
A3 and B3 resp. and copied down:

=IF(B3<>"",INDEX('All Completed Runs'!C:C,MATCH(B3,'All Completed Runs'!E:E,0)),"")
=IFERROR(SMALL(IF((LEFT('All Completed Runs'!C$3:C$502,1)="S")*(ISERROR(MATCH('All Completed Runs'!C$3:C$502,A$2:A2,0))),'All Completed Runs'!E$3:E$502,""),1),"")

 

[ollyhughes1982]

Thanks both.