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

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS
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.


Thanks!

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

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS
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:

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
What is the formula you have used?
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Nov 27, 2018
Messages
269
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,001
Office Version
  1. 365
Platform
  1. Windows
How about
=IFERROR(INDEX(UNIQUE(FILTER('All Completed Runs'!C3:C2002,LEFT('All Completed Runs'!C3:C2002,1)="S"),0),SEQUENCE(10)),"")
 

Kaper

Board Regular
Joined
Mar 14, 2014
Messages
230
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,118,986
Messages
5,575,383
Members
412,658
Latest member
LS0009
Top