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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
      Diamond Contributor
      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.

Resources