Why this LAMBDA return an array?

[yushang]

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 returned value is an array? What I expected is the value of C1. Many thanks!

[#2 · Accepted solution]

@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.

[#3]

@Sergei Baklan 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)

[#4]

@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.

[#5]

@Sergei Baklan 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.

[#6]

@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)
            )
        )
    )
)