XLOOKUP to find Nth occurrence

[ollyhughes1982]

Hi,

I have the below worksheet (below), in which I have a working solution for the first table (‘MY parkruns - ALL COMPLETED RUNS - STOPWATCH BINGO #1’). This first table identifies the first Event (Venue) where times ending from '00' to '59' (the seconds digits - column A) were achieved.

This works fine and has no issues. Formulas below:

Column B:
=IFERROR(XLOOKUP(A4,TEXT(RIGHT('All Completed Runs'!$AN$4:$AN$2003,2),"00"),'All Completed Runs'!$C$4:$C$2003,""),"")

Column C:
=IFERROR(XLOOKUP(A4,TEXT(RIGHT('All Completed Runs'!$AN$4:$AN$2003,2),"00"),'All Completed Runs'!$Z$4:$Z$2003,""),"")

Column D:
=IFERROR(XLOOKUP(A4,TEXT(RIGHT('All Completed Runs'!$AN$4:$AN$2003,2),"00"),'All Completed Runs'!$E$4:$E$2003,""),"")

Column E:
=IFERROR(IF(B4="","",COUNTIF('All Completed Runs'!$AN$4:$AN$2003,A4)),"")

My problem, is that I would now like to find the second instance of each '00' to '59' achieved (‘MY parkruns - ALL COMPLETED RUNS - STOPWATCH BINGO #2’), third (‘MY parkruns - ALL COMPLETED RUNS - STOPWATCH BINGO #3’), fourth (‘MY parkruns - ALL COMPLETED RUNS - STOPWATCH BINGO #4’) and so on - up to ten. I haven’t been able to achieve this, despite trying a number of things including the SEQUENCE function, but just can’t seem to get it to work.

Workbook can be found here (One Drive - small file and opens easily): All Completed Runs.xlsx

Thanks in advance!

Olly.

 

[Fluff]

How about
for H4

=IFERROR(TAKE(DROP(FILTER(CHOOSECOLS('All Completed Runs'!$C$4:$Z$2003,1,-1,3),'All Completed Runs'!$AN$4:$AN$2003=A4),1),1),"")

and N4

=IFERROR(TAKE(DROP(FILTER(CHOOSECOLS('All Completed Runs'!$C$4:$Z$2003,1,-1,3),'All Completed Runs'!$AN$4:$AN$2003=A4),2),1),"")

 

[ollyhughes1982]

How about
for H4

=IFERROR(TAKE(DROP(FILTER(CHOOSECOLS('All Completed Runs'!$C$4:$Z$2003,1,-1,3),'All Completed Runs'!$AN$4:$AN$2003=A4),1),1),"")

and N4

=IFERROR(TAKE(DROP(FILTER(CHOOSECOLS('All Completed Runs'!$C$4:$Z$2003,1,-1,3),'All Completed Runs'!$AN$4:$AN$2003=A4),2),1),"")

Yes, that works perfectly. Brilliant, as always. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ollyhughes1982]

You're welcome & thanks for the feedback.

No problem. If there was an Oscars for Excel, you'd being taking home the gongs every year! Simply the greatest Excel-er there around!