Forum Discussion
OFFSET on results of FILTER
I assume I'm making a basic error or overlooking something silly.
_test is a 5 column table with a column Bool. I want the first two columns only, for those rows where Bool is "Yes".
I have a function like this:
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
shows, OFFSET(matches,0,0,,2),
shows
)
I get a #value error. If I display matches instead of shows, I see my 2 rows.
If I break it up into two separate calculations, I get the correct result:
=LET(
matches,FILTER(_test,_test[Bool]="Yes","None"),
matches
)
gives me two rows, and then (result being in C10)
=LET(
shows,OFFSET(C10#,0,0,,2),
shows)
gives me the two column result I wanted.
Why doesn't OFFSET(FILTER()) work, but OFFSET(materialized filter data) does?
OFFSET() requires range as first parameter, not an array. Instead you may use something like
=LET( matches,FILTER(_test,_test[Bool]="Yes","None"), shows, OFFSET(matches,0,0,,2), one, FILTER(matches, {1,1,0,0,0} ), two, INDEX( matches, SEQUENCE(ROWS(matches)), {1,2}), two )
1 Reply
OFFSET() requires range as first parameter, not an array. Instead you may use something like
=LET( matches,FILTER(_test,_test[Bool]="Yes","None"), shows, OFFSET(matches,0,0,,2), one, FILTER(matches, {1,1,0,0,0} ), two, INDEX( matches, SEQUENCE(ROWS(matches)), {1,2}), two )