INDEX AGGREGATE issue

[ollyhughes1982]

Hi all,

I have an issue with my Excel file formula. it was previously working, but seems to have now gone wrong since making some changes to my workbook. It's cell B48 in the 'All Completed Runs - Summary' worksheet. The cell should display 'Trelai Park parkrun' then ',' then 'United Kingdom - Wales', [From column D, in the 'All Completed Runs' worksheet], then the value from cell A47, then 'Away'. i.e. Concatenate the three fields, with a comma and a space, after the first and 'Away' at the end.

What the cell should do, is display the above, but only if these two conditions are met:
- It is not a junior event (i.e. blank in column V of the 'Miscellaneous Data - AP' worksheet)
- Has not yet been completed (i.e. blank in column K of the 'Miscellaneous Data - AP' worksheet)

The result should be Trelai Park, which is 14k away from my home event of Coed Cefn-pwll-du. At the moment, this formula (when it worked) was giving me Newport junior parkrun, which it shouldn't do, as that is a junior event.

Link: parkrun - My parkrun Record - WORKING.xlsx

Thanks in advance!

 

[KRice]

So I believe the OP really wants this then:
=INDEX(SORT(FILTER('Miscellaneous Data - AP'!B3:J2002,('Miscellaneous Data - AP'!V3:V2002<>1)*('Miscellaneous Data - AP'!K3:K2002<>1)*('Miscellaneous Data - AP'!I3:I2002>0)),8,1),1,1)

If I'm understanding the table conventions correctly: column V on 'Miscellaneous Data - AP' =1 if the event is a junior event, so <>1 is desired... is this correct?
And then the column K <>1 means the event was not completed...that looks okay.

@ollyhughes1982, please see my questions in post #2. You mention in post #1 that data might be coming from two different worksheets, as you have mentioned both 'Miscellaneous Data - AP' and 'All Completed Runs'. Is there any reason why it could not all be taken from 'Miscellaneous Data - AP'?

 

[ollyhughes1982]

You need to extend the range as I showed in red, in post#8

Great, thanks. Sorted now

 

[ollyhughes1982]

Thanks both, for all of your help ??

 

[Fluff]

Glad we could help & thanks for the feedback

 

[ollyhughes1982]

This site has bee absolutely invaluable in building my increasingly mega-sized parkrun results spreadsheet over the past month. I'm hopefully now coming towards the final stretch!