Return a different column from FILTER function. OFFSET and FILTER required?

[ollyhughes1982]

Hi,

‘All Completed Runs - SAT L’ worksheet:

‘All Completed Runs - SAT’ worksheet:

I have the below formula in cell A4 of the ‘All Completed Runs - SAT L’ worksheet, which is working correctly (it pulls the correct data from column D of the ‘All Completed Runs - SAT’ worksheet).

=IFERROR(SORT(FILTER('All Completed Runs - SAT'!$D$4:$D$2003,('All Completed Runs - SAT'!$D$4:$D$2003>=1)*('All Completed Runs - SAT'!$D$4:$D$2003<=10))),"")

However, in B4 of the ‘All Completed Runs - SAT L’ worksheet I now want to return corresponding columns from the ‘All Completed Runs - SAT’ worksheet, but am not sure how to do this. I have tried the below, but it doesn’t work correctly. As you can see, the first value that corresponds to 1 in the ‘All Completed Runs - SAT’ worksheet is ‘Newport parkrun’, but this formula returns ‘Cardiff parkrun’, which isn’t corect.

=IFERROR(SORT(FILTER('All Completed Runs - SAT'!$A$4:$A$2003,('All Completed Runs - SAT'!$D$4:$D$2003>=1)*('All Completed Runs - SAT'!$D$4:$D$2003<=10))),"")

I’m not sure what I’m doing wrong here, I thought that changing the first part of the filter ('All Completed Runs - SAT'!$D$4:$D$2003) to 'All Completed Runs - SAT'!$A$4:$A$2003 would return the correct values from column A in the ‘All Completed Runs - SAT’ worksheet.

I’m thinking that I might need some sort of OFFSET function? I have tried a few things, but cannot get anything to work.

Thanks in advance!

 

[hagia_sofia]

Hello, could you please describe in more detail what exactly is the problem? E.g. there are not all values that should be returned?

 

[ollyhughes1982]

Hello, could you please describe in more detail what exactly is the problem? E.g. there are not all values that should be returned?

It's returning values from column A in the source worksheet ('All Completed Runs - SAT'), but not the correct ones. Is there a way that I could just use the original formula, which works and tell Excel to pull a corresponding value from another column? You can see that it is pulling 'Cardiff parkrun' for 1, but in the data 'Cardiff parkrun' is 7. The corresponding values for the two '1' ranks should be 'Newport parkrun' and 'Coed Cefn-pwll-du parkrun'. I want to do the exact same FILTER function, but just pull a different corresponding column from the worksheet instead.

 

[hagia_sofia]

Maybe I am overlooking something but if 'Cardiff parkrun' = 7 it is within the filtered interval ('All Completed Runs - SAT'!$D$4:$D$2003>=1)*('All Completed Runs - SAT'!$D$4:$D$2003<=10)?

 

[ollyhughes1982]

Yes, but it should be returning next to 7, not 1, as that’s its rank in the source sheet

 

[hagia_sofia]

Ah, I see. FILTER is working correctly here, the problem is that you are using SORT which sorts both outputs separately (first numbers and then lettes). To make a single array of it you could e.g. try:

=IFERROR(SORT(CHOOSECOLS(FILTER('All Completed Runs - SAT'!$A$4:$D$2003,('All Completed Runs - SAT'!$D$4:$D$2003>=1)*('All Completed Runs - SAT'!$D$4:$D$2003<=10)),4,1)),"")

 

[ollyhughes1982]

Ah, I see. FILTER is working correctly here, the problem is that you are using SORT which sorts both outputs separately (first numbers and then lettes). To make a single array of it you could e.g. try:

=IFERROR(SORT(CHOOSECOLS(FILTER('All Completed Runs - SAT'!$A$4:$D$2003,('All Completed Runs - SAT'!$D$4:$D$2003>=1)*('All Completed Runs - SAT'!$D$4:$D$2003<=10)),4,1)),"")

Great, I'll try that now. I did try CHOOSECOLS last night, but couldn't get it to work. Will have another try with your formula now. Thanks.

 

[ollyhughes1982]

Yes, that worked. I replaced the original column A formula with that one and it spilled them across. Thanks!