Forum Discussion

yushang's avatar
yushang
Brass Contributor
Mar 20, 2023
Solved

Why this LAMBDA return an array?

Hi guys, Take a look at the following figure, the formula in D1 is =LAMBDA(store,col,LET( x,FILTER(A1:C3,INDEX(A1:C3,0,1)=store,NA()), IF(NOT(ISNA(x)),INDEX(x,1,col),x)))(2046,3) Why the returne...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 20, 2023

    yushang 

    Actually FILTER which is like

    FILTER(A1:C3, {true; false; false} ,NA()) (didn't test)

    returns first row of the range. 

    If  FILTER(A1:C3, {true, false, false} ,NA())

    it returns first column.

yushang's avatar
yushang
Brass Contributor
Mar 20, 2023

SergeiBaklan Thanks. I know what's wrong, this is the correct formula

=LAMBDA(store,col,LET(
x,FILTER(A1:C3,INDEX(A1:C3,0,1)=store,NA()),
IF(TYPE(x)=64,INDEX(x,1,col),x)))(2046,3)

SergeiBaklan's avatar
SergeiBaklan
MVP
Mar 20, 2023

yushang 

Yes, this one shall to return single value. However, you don't need NA() with filter (it returns record) and not necessary to check on TYPE(), you may simply return INDEX.

Not sure if there are parameters which are not in range.

  • yushang's avatar
    yushang
    Brass Contributor
    Mar 20, 2023

    SergeiBaklan Yes. The final formula looks like

    =LAMBDA(store,col,LET(
    x,FILTER(A1:C3,INDEX(A1:C3,0,1)=store),
    IF(TYPE(x)=64,INDEX(x,1,col),x)))(2046,3)

    if FILTER failed, the error will propagate to the lambda caller.

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Mar 20, 2023

      yushang 

      Okay, but not sure all errors are handle correctly. Perhaps something like

      =LAMBDA(store, storeColumn,
    LET(
        getStore, XMATCH(store, CHOOSECOLS(data, 1)),
        IF(
            ISNA(getStore),
            "no such store",
            IF(
                storeColumn > COLUMNS(data),
                "no such column",
                INDEX(data, getStore, storeColumn)
            )
        )
    )
)

Resources