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!
...
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?
Yes, correct, you may add IFERROR() and IFNA() to the list.
With INDEX() perhaps you mean something like
=IF( INDEX( range, sequence ) , fn( <text>, sequence ) )Nope, that doesn't work. As soon as we have an array instead of scalar value conditional functions refuse to work with long texts.
- no i meant a more direct use of Index since adding the IF causes the issue. So in your simple IF example above: IF(A2:A3,B2#) doing something like INDEX(B2#,(A2:A3>0)*ROW(A2:A3)-1)