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...
- Agreed, I suspected MAKEARRAY would be viable also, but I agree with you that the formulae are ugly and unwieldy.
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.
- so I was wondering if this affects IF vs alternatives and found similar results using IFS, CHOOSE, SWITCH. but, INDEX does seem to work. Can you rework the problem using the output of the IF be the input of the INDEX and the conditional of the IF be the indexing of the INDEX?
