Forum Discussion
long texts with arrays in conditional functions
PeterBartholomew1 , tboulden or someone else
Perhaps you know the workaround, even with lambdas. In simplest case
=IF( 1, REPT("a", 256) ) works
=IF( {1}, REPT("a", 256) ) returns #VALUE!
...
=IF( {1}, REPT("a", 256) )works for me if I confirm the formula with Ctrl+Shift+Enter (using Excel 2021, which has dynamic arrays).
In general it works with MAP()
=LET( t, FILTER(range, criteria), MAP( t, LAMBDA(v, IF( v= "", "", v) ) ) )or back to simplest case
=LET( arr, {1}, MAP( arr, LAMBDA(v, IF( v, REPT("a", 256) ) ) ) )Perhaps shorter solution?
- Sergei, you honor me with thinking I can be of help 🙂 However, if I understand the problem correctly, I think you're on the right track with MAP, and I'd venture that MAP vs BYROW internal workings exemplify the issue between your examples using 1 vs {1}. Assuming single column array for arr, I think MAP takes the value from each row-cell, however BYROW takes each row-cell as an array.
Thank you. Not sure about BYROW(), will play with it. MAKEARRAY() works, but formulas are so ugly...
I don't have LAMBDA functions, so let's see if someone else (Peter Bartholomew?) comes up with a better suggestion.
HansVogelaar , thank you. I still try to find some workaround without lambdas to keep compatibility, and one with lambda is not very elegant.
Yes, it works. CSE returns first element of the array, other words works as with scalar value. In more complex case like
=LET( t, FILTER( range, criteria), IF( t = "", "", t ) )it doesn't work
Or, for example,
