OFFSET on results of FILTER

[boukasa]

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?

[#2 · Accepted solution]

@boukasa 

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

)