Relevance of setting result in variable or not in a LET Expression (FORMULA / Google Sheets)

Using the formula below

=LET(
      tabVincName,  {  {2024, "A"}
                     ; {2020, "B"} 
                     ; {1960, "C"} 
                    }
     , dataCol,      FILTER(tabVincName, BYCOL(tabVincName, LAMBDA( c,  c >= 2021 )))
     , dataCol
)

I get error #VALUE commented like "second argument must to have single row or single column".

But referencing the output passed to filter's second argument via variable ("fi") I get the expected output.

=LET(
      tabVincName,  {  {2024, "A"}
                     ; {2020, "B"} 
                     ; {1960, "C"} 
                     }
     , fil,          BYCOL(tabVincName, LAMBDA( c,  c >= 2021 ))
     , dataCol,      FILTER(tabVincName, fil)
     , dataCol
)

Why?

PS: The locale settings (BR-PT/"pão de queijo") do not use comma and semicolon as they are used world around (thanks Windows's internacionalizators, thanks Google's developers to keep this tradition!). I've tryied to adapt them , but I'm not safe if the test is correct. So, they original snippets are left below for safety.

NOT WORKING SNIPPET

=LET(
       tabVincName;  {  {2024; "DESIGNADO"}
                      \ {2020; "CONVOCADO"} 
                      \ {1960; "DESIGNADO"} 
                     }
       ; dataCol;    FILTER(tabVincName; BYCOL(tabVincName; LAMBDA( c;  c >= 2021 )))
       ; dataCol
     )

WORKING SNIPPET

=LET(
                    tabVincName;  {  {2024; "DESIGNADO"}
                                   \ {2020; "CONVOCADO"} 
                                   \ {1960; "DESIGNADO"} 
                                  }
                  ; fil;          BYCOL(tabVincName; LAMBDA( c;  c >= 2021 ))
                  ; dataCol;      FILTER(tabVincName; fil)
                  ; dataCol)

Solution

The following part of the formula is an array formula

BYCOL(tabVincName; LAMBDA( c;  c >= 2021 ))

Specifically, the part inside the LAMBDA

c >= 2021

is checking an entire column at once. If you don't array enable it, it only checks the first value of each column, so it returns a 1D array.

In the first formula you provided, that part is inside the FILTER function, which is an array enabling function, so it returns a 2D array. This is why it gives an error (FILTER expects 1D arrays in every parameter after the first one).

Even though your second formula doesn't give an error, it's probably not what you want either. In general, if a formula gives you the result you want when it's not array enabled but it breaks when you array enable it, there is something wrong with it.

You probably want something like this instead:

=LET(
   tabVincName; {
                  2024\ 2020\ 1960; 
                  "DESIGNADO"\ "CONVOCADO"\ "DESIGNADO"
                }; 
   FILTER(tabVincName; INDEX(tabVincName; 1) >= 2021)
)