Formula repeating LAMBDA/MAKEARRAY a result

[Hecatonchire]

Hello everyone,

Could you explain to me the difference in results between these 2 formulas (simplified version).

Formula 1 (works well):
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2),"KO")))(6,1) Result => 2-1
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2)/0,"KO")))(6,1) Result => KO

Formula 2 (stutters):
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2),Tab)))(6,1) Result =>2-1  2-1  2-1...
=LAMBDA(nbC,nbR,LET(Tab,MAKEARRAY(nbR,nbC,LAMBDA(r,c,c&"-"&r)),IFERROR(CHOOSECOLS(Tab,2)/0,Tab)))(6,1) Result => 1-1  2-1  3-1...

In my real formula, in the event of an error I would like to return the entire table as in formula 2. But with this formula Excel starts repeating the result when there is no error!

Another problem

1. 2 position values ​​to search stored in a matrix (P).
2. A value matrix (V) of 2 rows and 6 columns.

I'm trying to:

• Find in the first line of V the value in position indicated by the first value of P.
• Find in the second line of V the value in position indicated by the second value of P.

I use CHOOSECOLS but I also get a repetition of the results (cross product)

How to prevent this?

With MAP I get nested table!

Regards

Arnaud

[#2 · Accepted solution]

@Hecatonchire 

What is happening in 'Formula 2' is that the presence of the array in the IFERROR condition causes the result of CHOOSECOLS(Tab, 2) to broadcast to match the error argument in size.  I think the problem may be that CHOOSECOLS(Tab, 2) is still of TYPE 64 (array) despite it returning only one value.

Using '@' to truncate the supposed array may sort this problem

=LAMBDA(nbC, nbR,
    LET(
        Tab, MAKEARRAY(nbR, nbC, LAMBDA(r, c, c & "-" & r)),
        IFERROR(@CHOOSECOLS(Tab, 2), Tab)
    )
)(6, 1)

[#3]

@PeterBartholomew1 

Thank you for your answer.

I'm going to go to bed less stupid 😁

For my other problem, I solved it by using an INDEX function instead of CHOOSECOLS.

I apply a matrix {1,2} for exemple with SEQUENCE to the row argument.