Weird Results from Array Manipulation - Any ideas on why?

joelb95 Here's some additional information you may find helpful or interesting...

(1) The CHOOSE Function:

The CHOOSE function handles arrays in very much the same way as the IF function. To illustrate this point, consider the following examples:

=IF(TRUE, "a", SEQUENCE(3))

=IF(FALSE, "a", SEQUENCE(3))

=IF({TRUE}, "a", SEQUENCE(3))

=IF({TRUE,FALSE}, "a", SEQUENCE(3))

When a single logical_test is performed, the corresponding result is returned as expected. However, when an array object is passed to the logical_test argument (even if that array only contains a single value), Excel sees this as an attempt to generate multiple sets of results for each item in the array. As such, in order to eliminate the possibility of generating a "jagged array" of results, the value_if_true and value_if_false arguments are automatically resized to be the same dimensions (ie: an argument containing a single value is repeated to fill the same number of rows/columns as the argument with the greatest number of rows/columns).

This same behavior can also be observed with the CHOOSE function:

=CHOOSE(1, "a", SEQUENCE(3), SEQUENCE(2))

=CHOOSE(3, "a", SEQUENCE(3), SEQUENCE(2))

=CHOOSE({1}, "a", SEQUENCE(3), SEQUENCE(2))

=CHOOSE({1,3}, "a", SEQUENCE(3), SEQUENCE(2))

In the examples shown above, out of the three possible value arguments to be returned, SEQUENCE(3) contains the greatest number of rows. When an array object is then passed to the index_num argument, the other two value arguments are automatically resized with the same number of rows as SEQUENCE(3). In order to prevent a "jagged array" of results from being returned, "a" is repeated 3 times to fill 3 rows and SEQUENCE(2) is resized to fit 3 rows. Note: SEQUENCE(2) cannot be repeated 3 times because it already contains multiple rows, which would overlap each other if repeated. Instead, it is "expanded" to fit 3 rows, with the additional rows returning #N/A.

(2) The BYROW and BYCOL Functions:

• BYROW and BYCOL cannot produce an array of arrays (nested arrays are not supported)
• the LAMBDA function applied to each row or column must produce a single result
• each iteration references an entire row or column (even when using a one-dimensional array)

For example, with =BYCOL(SEQUENCE(1, 3), LAMBDA(c, ...)), the SEQUENCE(1, 3) function returns an array object consisting of {1,2,3}. While you might expect each iteration of the c variable to return a single value from 1 to 3, it actually returns another array consisting of a single value from {1} to {3}, which represents an entire column in the array. As discussed previously, when an array object is then passed to the index_num argument of the CHOOSE function, multiple results can be returned (as is the case with your custom MANY function).

Alternatively, the MAP function can be better suited for working with one-dimensional arrays, because the LAMBDA function is applied to each value in the array individually. For example, with =MAP(SEQUENCE(1, 3), LAMBDA(n, ...)), each iteration of the n variable will in fact return a single value from 1 to 3.

(3) Alternative Methods (Array of Arrays):

MAP with TEXTJOIN, plus TEXTBEFORE and TEXTAFTER:

=LET(
    many, LAMBDA(choice, CHOOSE(choice + 1, 10, "b", "harry", "c", SEQUENCE(4, 1, 5), SEQUENCE(3), 45, "d", "lots", "e", "ten")),
    arr, MAP(SEQUENCE(1, many(0)), LAMBDA(n, TEXTJOIN("|", FALSE, "", many(n), ""))),
    IFERROR(TEXTBEFORE(TEXTAFTER(arr, "|", SEQUENCE(ROWS(many({0})))), "|"), "")
)

MAKEARRAY with INDEX:

=LET(
    many, LAMBDA(choice, CHOOSE(choice + 1, 10, "b", "harry", "c", SEQUENCE(4, 1, 5), SEQUENCE(3), 45, "d", "lots", "e", "ten")),
    IFERROR(MAKEARRAY(ROWS(many({0})), many(0), LAMBDA(r,c, INDEX(many(c), r))), "")
)

REDUCE with HSTACK or VSTACK:

=LET(
    many, LAMBDA(choice, CHOOSE(choice + 1, 10, "b", "harry", "c", SEQUENCE(4, 1, 5), SEQUENCE(3), 45, "d", "lots", "e", "ten")),
    IFNA(REDUCE(many(1), SEQUENCE(1, many(0) - 1, 2), LAMBDA(a,v, HSTACK(a, many(v)))), "")
)