Forum Discussion

LBROWN7's avatar
LBROWN7
Brass Contributor
Aug 30, 2023

Element not allowed inside an array in a LAMBDA function

Can anyone advise why   the array expression {a,b} in the LAMBDA expression below generates an error. =LAMBDA(a,b,IF(a<b,{0,0},{a,b}))(1,2) it indicates, "element not allowed inside an array...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 30, 2023

LBROWN7 

{...} assumes constants inside. You may try

=LAMBDA(a,b,IF(a<b,{0,0}, HSTACK(a,b)))(1,2)
  • LBROWN7's avatar
    LBROWN7
    Brass Contributor
    Aug 30, 2023

    Thanks!! This is correct.
    If a & B are names ={a,b} fails, but ={1,2} works!!

    Follow up Question is this issue concerning the Excel Braces Array Constructor only working with numbers documented anyplace?

    Thanks

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Aug 30, 2023

      LBROWN7 

      "Excel Braces Array Constructor only working with numbers"

       

      That is not entirely correct. It works only with constant values, including text strings, TRUE/FALSE and error values. For example, the following is valid:

       

      {"Excel", 37, TRUE, #N/A}

       

      See Guidelines and examples of array formulas section 'Array constant syntax':

      • LBROWN7's avatar
        LBROWN7
        Brass Contributor
        Aug 30, 2023

        Thanks, the Excel Braces constructor generates an Array CONSTANT.
        I wrongly assumed it generated a generic Array.

        Now in a LAMBDA function where all names are immutable, relaxing this constraint (on constant VALUES) to allow immutable names would be great, as the braces constructor would still generate and Array CONSTANT.

        NOTE - in a normal Excel formula  global and sheet names are not immutable, hence the braces constructor would not generate an Array Constant.

Resources