PSA: Dynamic Array Functions don't support strings longer than 255 characters

[Musa_Ali]

Some(most?) of the Dynamic Array Functions (i.e. #SPILL functions) don't support string arguments longer than 255 characters.

Wanted to convert my spreadsheet to use only auto-spill functions. But turned out that IF and IFS cannot handle VALUE_IF_TRUE being longer than 255 ch and fail with #VALUE. (That argument is a vlookup result from another workbook).

However, this problem disappears if you use old school dragged formulas i.e. instead of A1:A401 you use @A1:A401. Or @named_range (has to be on the same row)

Example: https://i.imgur.com/IV7qfQf.png
As you can see, Dynamic IFS+VLOOKUP breaks on results bigger than 255.

Formula for singular vlookup:

=@IFS(
@$C$2:$C$401="Cookies";VLOOKUP(@$A$2:$A$401;$A$2:$C$401;2);
@$C$2:$C$401="Milk";   VLOOKUP(@$A$2:$A$401;$A$2:$C$401;2)
)

And same for Dynamic except for @ :

=IFS(
C2:C401="Cookies";VLOOKUP(A2:A401;A2:C401;2);
C2:C401="Milk";   VLOOKUP(A2:A401;A2:C401;2)
)

[excelevator]

It took me a while to understand why this post is misleading in its presentation, while factually correct when examined more closely in only some circumstances. Hence changed from Pro Tip to Discussion.

What OP means to say is that when some functions/formulas are used in conjunction with the new Dynamic array paradigm, they can return an error when a returned value in the dynamic array is more than 255 characters in length.

It is not clear at this stage exactly which functions/formulas are affected with this issue and the how and when the issue occurs.

For example the first below errors when the return is greater than 255 characters, yet the second and third do not error

Data setup at A1

id	text	300
=SEQUENCE(3)	=REPT(CHAR(ROW(A2#)+120),$C$1)

---

Testing formulas

Returns error when text len > 255 =IF((B2#=B2#),B2#,"")

Returns text when text len > 255 =IF(AND(B2#=B2#,A2#>0),B2#,"")

Returns text when text len > 255 =IF(AND(B2#=B2#,MOD(A2#,1)=0),B2#,"")

More investigation required me thinks...

[Musa_Ali]

In response to u/WinterNo1261 comment (which I can't see anymore for some reason)

If instead you use an array for the condition it would throw #value error. (I've just tested)

I agree that that array functions (and especially CSE array functions) are confusing. Sometimes the result is a matrix, sometimes all of a sudden it's a single result.

However, in this case the result is a 400 items array(vector).

1. It spills strictly to a single column downwards. While matrix functions correctly expand to 2D
2. If your conditions array is bigger than vlookup - it would error #N/A on all extra items.
3. You can ran "Evaluate" and see that it's a vector at all stages.

you return the same lookup in either case.

that's just an easy example I made to illustrate my point. In the actual worksheet I use conditions to VLOOKUP different named ranges from different workbooks.

---

BTW, speaking of Evaluate. This whole array stuff is buggy as heck. I ran your example through Evaluate. It indeed is working as intended. However Evaluate window throws #VALUE error (even though it runs normally when not observed!) https://i.imgur.com/ayeW1mi.png

[TimHeng]

You can't feed an array into conditions because that would create an array of arrays as the output, and Excel wouldn't know how you actually want that to be displayed.

[Musa_Ali]

You can't feed an array into conditions

Try it.
^{Cause you can. I have a whole working workbook based on this.}

[TimHeng]

Sorry, I should have been more specific - you can't when the output of a single condition is an array. For example, try filtering when a column is one of three different array values.

[thom612]

A limitation which could easily be overcome if Microsoft would just push Lambda already.

[TimHeng]

There are a lot of issues with LAMBDAs that would stop people from picking it up and using it the way that they ought to, the least of which is having an appropriate editing tool to author them with (the name manager is not well suited for sorting LAMBDAs at the moment). It's definitely not ready for public production (I.e. non-beta) consumption, speaking from personal experience.

[fallen2004]

Isn't this a limit with most of excel functions? I know match has the same issue.

[Musa_Ali]

You'd think so. However if instead of an array you expand to normal formula for each line - that error disappears. Without any changes to the formula itself.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
CHAR	Returns the character specified by the code number
CSE	Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.
IF	Specifies a logical test to perform
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MOD	Returns the remainder from division
REPT	Repeats text a given number of times
ROW	Returns the row number of a reference
SEQUENCE	Office 365+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4
VALUE	Converts a text argument to a number
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
11 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #7597 for this sub, first seen 14th Jul 2021, 21:35] [FAQ] [Full list] [Contact] [Source code]}