0

I am using Excel version 2205 (Build 15225.20356)

I expect that each of these functions produces the identical result $A$1

// no lambda or let
=ADDRESS(ROW(A1),COLUMN(A1))

// lambda alone
=LAMBDA(cell, ADDRESS(ROW(cell), COLUMN(cell)))(A1)

// let alone
=LET(
    cell,
    A1,

    f,
    ADDRESS(ROW(cell), COLUMN(cell)),
    
    f
)

// let and lambda (version 1)
=LET(
    cell,
    A1,

    f,
    LAMBDA(x,
        ADDRESS(ROW(x), COLUMN(x))
    ),

    f(cell)
)

// let and lambda (version 2)
=LET(
    cell,
    A1,

    f,
    LAMBDA(x,
        ADDRESS(ROW(cell), COLUMN(x))
    ),

    f(cell)
)

The last function produces a #VALUE! error. Why?

EDIT: here's an example that does something similar, but does work as expected. Assume $A$1=1, then this will output 2

=LET(
    cell,
    A1,

    f,
    LAMBDA(x, cell + x),

    f(cell)
)
PaulH
  • 7,759
  • 8
  • 66
  • 143
  • Because, `LET()` function can preserve only calculated result not function. You define `f` for lambda function which is not possible inside LET() function. Name manager can work in this type fashion. – Harun24hr Jul 27 '22 at 01:58
  • @Harun24hr - I define a `LAMBDA` function inside a `LET` in 2 other examples that work as expected. Why is the last example different? – PaulH Jul 27 '22 at 12:50
  • In the last case what is `cell` referring to in the portion `ROW(cell)`? Essentially you have a typo as `x` is defined as a function over `cell` but `cell` itself is undefined within the context of the LAMBDA function. I assume it should read `ADDRESS(ROW(x), COLUMN(x))` – Tragamor Jul 28 '22 at 20:40
  • @Tragamor - The example directly above that is as you say. The failure case an intentionally trivial example intended to show the error. The LAMBDA() function fails when it uses both a variable from the LET and from the lambda-expression. – PaulH Jul 28 '22 at 21:32

0 Answers0