Filter Function in non Contiguous ranges

[nmounir]

Hello Everyone,

I am trying to extract the names of the people who scored greater than 400. The names are in non contiguous columns. The countifs function works fine but the filter function is not extracting contiguous records.

is there a way to extract this ? and if possible in one single row

your help is much appreciated.

 

[Fluff]

How about

=LET(Data,(A1:B5,F1:G5),r,ROWS(A1:A5),s,SEQUENCE(r*AREAS(Data),,0),i,INDEX(Data,MOD(s,r)+1,{1,2},INT(s/r)+1),INDEX(FILTER(i,INDEX(i,,2)>400),,1))

note that both the ranges must be the same size, even if it means including blank rows.

 

[nmounir]

How about

=LET(Data,(A1:B5,F1:G5),r,ROWS(A1:A5),s,SEQUENCE(r*AREAS(Data),,0),i,INDEX(Data,MOD(s,r)+1,{1,2},INT(s/r)+1),INDEX(FILTER(i,INDEX(i,,2)>400),,1))

note that both the ranges must be the same size, even if it means including blank rows.

Thank you fluff, the formula worked perfectly. it is just a bit difficult to understand especially the last filter inside of index.

 

[Fluff]

You're welcome & thanks for the feedback.