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

 

[ollyhughes1982]

There are multiple versions of essentially the same worksheet, each has more countries on it. You achieve a different 'class' each time you reach a certain number of countries. The first table needs to show the first 4, the second one goes up to 10, then 15, then 20 then 22 (all of them). So I need to cut off each worksheet at those limits of records. It is duplication, but it is how they do it.

Each icon is clickable and that's when the table shows

 

[Fluff]

You can do it like
=INDEX(UNIQUE(FILTER('All Completed Runs'!D3:D2002,'All Completed Runs'!D3:D2002<>"")),SEQUENCE(2,,1,1))

This will show the first 2 countries.

 

[ollyhughes1982]

Brilliant. That works perfectly now. That xlookup is awesome - So much better than index match and vlookup. Much easier to use and understand. Thanks again.

 

[ollyhughes1982]

Sorry for being such an amateur. I promise I am pretty much finished on this project now. Been 6 weeks of hard graft, with lots of help from some Excel geniuses, such as yourself. I owe you some cold beers

 

[ollyhughes1982]

The only one issue I am getting with the xlookup is that it is returning zeroes for the rows where I haven't completed a country yet. I have tried the iferror and "" and also putting "" within the '[if_not_found]' part of the xlookup, but it still returns zeroes.

 

[Fluff]

What is the formula?

 

[ollyhughes1982]

The original one was: =XLOOKUP(A3,'All Completed Runs'!$D$3:$D$2002,'All Completed Runs'!$C$3:$C$2002)

I then added in iferror, to try and stop the zeroes from row 5 onwards: =IFERROR(XLOOKUP(A3,'All Completed Runs'!$D$3:$D$2002,'All Completed Runs'!$C$3:$C$2002),"")

I also tried the the option built into the xlookup function: =XLOOKUP(A3,'All Completed Runs'!$D$3:$D$2002,'All Completed Runs'!$C$3:$C$2002,"")

it still gives a 0 result from for 5 onwards though.

 

[Fluff]

Assuming A5 is blank, you will need to use
=IF(A5="","",XLOOKUP(A3,'All Completed Runs'!$D$3:$D$2002,'All Completed Runs'!$C$3:$C$2002,""))

 

[ollyhughes1982]

Yes, works perfectly. Thanks again. You have taught me a lot!

 

[Fluff]

You're welcome & thanks for the feedback.