INDEX AGGREGATE issue finding first instances with conditions

[ollyhughes1982]

Hi all,

Would anyone please be able to help with calculations in my ‘All Completed Runs - C EC C’ worksheet, please?

The definition of what is needed here is: ‘Run in X or more countries’ - So, I need to list the countries (in order of date completed - earliest first) and the relevant first event for that country, as well as the relevant date and time achieved.

I have populated (in yellow) what the results should be.

I thought I previously had this working, with the INDEX / AGGREGATE formulas that are present, but they are now not working (except for the first two). Each country should be unique.

Link to file here: parkrun - My parkrun Record - WORKING 2 - Reduced.xlsx

Thanks in advance!

Also posted on Excel Forum: INDEX AGGREGATE issue finding first instances with conditions

 

[Fluff]

Pretty sure I've already helped you with a formula to get the unique countries. You can use that & then a simple vlookup to get the rest of the info.

 

[ollyhughes1982]

I know, I’ve been struggling to work it out for the past hour or so. Just can’t seem to get it to work, sure it was working previously. Just keep getting errors.

 

[Fluff]

The formula I showed you used unique & filter.

 

[ollyhughes1982]

=UNIQUE(FILTER('All Completed Runs'!D3:D2002,'All Completed Runs'!D3:D2002<>"")). Will this give them in date order? I don't think I can use vlookup as the event column which I need is to the left of the country column

 

[Fluff]

If you data is still in date order, then yes.
If you can't use vlookup, then use either index/match or better yet, the new Xlookup.

 

[ollyhughes1982]

Ok. Thanks. How would I do an xlookup, please? Not used that

 

[Fluff]

Have a look through your threads, I'm pretty sure I've given you a formula using it before.
Alternatively have a look at exceljet.net they have examples of most functions & how to use them.

 

[ollyhughes1982]

Great. Thanks. When doing the unique filter, what’s the way to stop it at a certain amount please? Is it a sequence function that needs to go in somewhere?

 

[Fluff]

If you need to list all the countries you've run in, then why do you need to limit it?