Forum Discussion
Formula repeating LAMBDA/MAKEARRAY a result
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
- 2 position values to search stored in a matrix (P).
- 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
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)
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)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.
