Likely amended IFERROR(INDEX formula required

[ollyhughes1982]

Hi,

I have another query regarding my parkrun record spreadsheet that I have made queries about in recent days.

There is a new challenge that has been introduced today, where you have to run the first parkrun that was ever to take place in each of the 23 parkrun countries. I have identified these first events from records and created a column that identifies these with a ‘1’ in column AK of the ‘Miscellaneous Data - P & C p’ reference worksheet.

There is a similar, but slightly more simple, existing challenge to this, where I list the first event that I completed in each of the various countries - so I have used an amended version of that challenge’s worksheet (‘All Completed Runs - Pioneer’) as the template for this new challenge. Therefore, what this new challenge needs to do differently is, to only show my first event completed in each country where there is a ‘1’ in the AK column of the ‘Miscellaneous Data - P & C p’ reference worksheet. i.e. The event was the first to ever be staged in that country and is also the first one that I completed in that country.

Please note: In some countries there were multiple events that were staged on the same date for the very first time in that country - For simplicity I only want to list the first one I complete, if I do happen to complete more than one of these multiple first events.

So, in essence I want to do the same as what the template worksheet doing currently, but add in the ned to have a ‘1’ in the AK column of the ‘Miscellaneous Data - P & C p’ reference worksheet.

In the template worksheet, it currently shows my first event in the UK as Newport - which is correct. However, the first event to ever take place in the UK was Bushy, which I do happen to have completed. So, the record should show Bushy, rather than Newport. If I hadn’t have happened to have completed Bushy, then the record should be empty.

Sorry for the long post, but wanted to explain it properly.

The link to the worksheet is here: parkrun Pioneer Challenge.xlsx

Apologies, but the file can take up to a minute to open, due to the many links between worksheets etc.

Thanks in advance!

Olly.

 

[ollyhughes1982]

Thinking about it, not sure if it might be some sort of FILTER( function I might need for this. Completely stuck on this one! Any help appreciated. Thanks.

 

[ollyhughes1982]

Hi all,

I haven't had any responses to this one, and wondered if some of you may have missed this post. Any help much appreciated, I'm still totally stuck on this one!

Thanks!

Olly.

 

[Tivakaran]

Hi, It tooks very long time to load this file in my very old Pentium III.

ok I didnt understand much the links but as I see....the reason it shows newport is because it is the first match for UK. Now to show Bushy, you need additional crtiteria....how to detect that Bushy is first completed event? I didnt see any column indicating so.

you'll need to find a better way to reorganize the links in this sheet...

 

[ollyhughes1982]

Hi, It tooks very long time to load this file in my very old Pentium III.

ok I didnt understand much the links but as I see....the reason it shows newport is because it is the first match for UK. Now to show Bushy, you need additional crtiteria....how to detect that Bushy is first completed event? I didnt see any column indicating so.

you'll need to find a better way to reorganize the links in this sheet...

Hi, thanks for having a look. There is a column (AK column of the ‘Miscellaneous Data - P & C p’ worksheet) that I have added in a lookup worksheet that identifies all of the events in each country that were the ones to be the first to ever take place in that country.

So, I need the current formula to work in the same sort of way, but with the additional criteria of it only populating if the lookup column (AK column of the ‘Miscellaneous Data - P & C p’ worksheet) is populated with a '1'.

 

[Tivakaran]

Hi. I'm still blur (sorry ) but to vlookup two criteria you'll need helper column. This is what I did:

in Miscellaneous Data - P & C p...put helper formula in column AN (in AN3 type =Ak3&D3)..
then in your index formula, change to this:
=INDEX('Miscellaneous Data - P & C p'!B:B,MATCH(1&'All Completed Runs - Pioneer'!A25,'Miscellaneous Data - P & C p'!AN:AN,0),0)

it should give you bushy....but if I linked to wrong sheet, I think you could relink back using the logical there.

 

[ollyhughes1982]

This seems to work perfectly. Thanks!