Forum Discussion
long texts with arrays in conditional functions
Good idea, but not easy on practice. Especially if we use other conditional formulae, like
=CHOOSE( {7,2,5,3,9,1}, ....
Interesting -
I think the earlier idea of mtarler to use INDEX may work depending on context
A) IF({condition},str1,str2)
maybe one of:
=INDEX((str1,str2),,,1+{condition})
=INDEX(HSTACK(str1,str2),1+{condition})
(first assumes cell references)
B) CHOOSE({7,2,5,3,9,1},col1,col2,...)
maybe instead:
=INDEX((col1,col2,...),SEQUENCE(ROWS(col1)),,{7,2,5,3,9,1})
=CHOOSECOLS(HSTACK(...),{7,2,5,3,9,1})
(first assumes column ranges)
- I think the problem with using INDEX was that although it could work (i.e. not get the error because the text string was >255) it would return 0 for blank cells. I believe a lot of the above was about doing both, working with text cells >255 AND returning blank for blank cells. As for using HSTACK, I don't have beta so I can't try that.
Yes, I see that zeroes are an issue with IF when first argument contains arrays but I believe INDEX returns a blank reference like FILTER does within arrays.
If 'blank' refers to a blank cell the following formulas return FALSE, TRUE, TRUE respectively:
=ISBLANK(IF({1},blank))
=ISBLANK(FILTER(blank,{1}))
=ISBLANK(INDEX(blank,{1})
so to replace the blanks with "" one can apply similar logic as SergeiBaklan above,
=SUBSTITUTE(INDEX(range,indices),,)
=INDEX(range,indices)&""
Not sure, will play with it. As for now, it looks like all such workarounds are very depend on content. Do we have horizontal or vertical vector or 2D array, which combinations of them and what we would like to achieve.
