INDEX curly bracket issue likely

[ollyhughes1982]

Hi.

I have this formula, which identifies the closest events to me from a table that contains a list of distances in column I:

=IFERROR(LET(f,FILTER('parkrun Reader Dump'!B3:J10002,('parkrun Reader Dump'!K3:K10002="")*('parkrun Reader Dump'!N3:N10002=1)*('parkrun Reader Dump'!O3:O10002=1)*('parkrun Reader Dump'!P3:P10002=1)*('parkrun Reader Dump'!AS3:AS10002<>1)*('parkrun Reader Dump'!I3:I10002<200)),SORT(INDEX(f,SEQUENCE(50),{8,9,1}),1)),"")

This works perfectly, but I now want to do the same, but pick up slightly different columns (the one for volunteering (R)), but it isn’t working for me. I have put below what I think it should be, but isn’t working:

=IFERROR(LET(f,FILTER('parkrun Reader Dump'!B3:J10002,('parkrun Reader Dump'!R3:R10002="")*('parkrun Reader Dump'!N3:N10002=1)*('parkrun Reader Dump'!O3:O10002=1)*('parkrun Reader Dump'!AS3:AS10002<>1)*('parkrun Reader Dump'!I3:I10002<200)),SORT(INDEX(f,SEQUENCE(50),{8,9,1}),1)),"")

This is what I amended:

• I swapped K to the relevant column R
• I removed the reference to the P column (as it isn’t needed for this one)

I’m guessing it’s the part in curly brackets ({8,9,1}) that it wrong, but I don’t know what that is doing, so am not sure how it would be amended.

Thanks in advance!

Olly.

 

[Fluff]

The 8 refers to col I the 9 to col J & the 1 to col B.
Just change the numbers to the columns you want.

 

[ollyhughes1982]

The 8 refers to col I the 9 to col J & the 1 to col B.
Just change the numbers to the columns you want.

Great, thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ollyhughes1982]

Hi. Apologies, I now know how the curly brackets part works, but my formula still doesn't. I thought that by just substituting K for R it would pick up the ones where I haven't yet volunteered, rather than ones where I haven't run, as was the case in the the original formula. The removal of the P reference is because it doesn't have to be a 5k event for volunteering, as you can volunteer at both and 5k and 2k (junior) events. These two changes should give me a list starting with Llanishen (5 miles), which is closer than Riverfront (6.34 miles). Is there something I am missing, I thought I could just substitute K for R? I have attached a link to a small version for you to please look at.

Link: Example.xlsx

The only events that shouldn't appear in the list of 50 are Newport, Newport junior and Coed Cefn-pwll-du, as these are the only three events that I have volunteered at so far.

Thanks again.

 

[Fluff]

You need to sort the data before you can take the 50 shortest distances like

=IFERROR(LET(f,SORT(FILTER('parkrun Reader Dump'!B3:J10002,('parkrun Reader Dump'!R3:R10002="")*('parkrun Reader Dump'!N3:N10002=1)*('parkrun Reader Dump'!O3:O10002=1)*('parkrun Reader Dump'!AS3:AS10002<>1)*('parkrun Reader Dump'!I3:I10002<200)),8),INDEX(f,SEQUENCE(50),{8,9,1})),"")

 

[ollyhughes1982]

You need to sort the data before you can take the 50 shortest distances like

=IFERROR(LET(f,SORT(FILTER('parkrun Reader Dump'!B3:J10002,('parkrun Reader Dump'!R3:R10002="")*('parkrun Reader Dump'!N3:N10002=1)*('parkrun Reader Dump'!O3:O10002=1)*('parkrun Reader Dump'!AS3:AS10002<>1)*('parkrun Reader Dump'!I3:I10002<200)),8),INDEX(f,SEQUENCE(50),{8,9,1})),"")

Brilliant, thank you. Why is it that the original one didn't require the sort?

 

[Fluff]

You're original formula was doing the sort, but after taking 50 values.

 

[ollyhughes1982]

Thanks. OK, I think that needs to be amended to do the sort first too then? Not sure how it's working without the sort, but it does seem to be

 

[Fluff]

Not sure how it's working without the sort

Probably just pure coincidence, if it is actually working.