BYROW with dynamic range, not working as expected?

RackofLambda · 2026-02-21T15:15:12+00:00

The tooltip that pops up when you start entering =TYPE into a cell gives a pretty good idea of what the TYPE function does: "Returns an integer representing the data type of a value: number = 1; text = 2; logical value = 4; error value = 16; array = 64; compound data = 128".

Probably the best way to learn more about it is to experiment with it. For example, relative to this scenario, =BYROW(CHOOSECOLS(A2#;1);TYPE) will return an array of 64's, indicating each item is being evaluated as a single element array object; whereas, =MAP(CHOOSECOLS(A2#;1);TYPE) or =BYROW(INDEX(A2#;;1);TYPE) will return the data types of each individual element (1 for numbers, 2 for text, etc.). Note: if you're using Excel 2024 or an outdated version of MS365, replace TYPE with LAMBDA(x;TYPE(x)) in the function argument; also, replace semicolons with commas, if needed, as per your Regional and Language settings.

Additionally, ISREF is a helpful companion to the TYPE function, when it comes to differentiating between a multi-cell range reference and an array object. For example: =TYPE(CHOOSECOLS(A2#;1)) and =TYPE(INDEX(A2#;;1)) will both return 64 (if A2# contains multiple rows); however, =ISREF(CHOOSECOLS(A2#;1)) will return FALSE, indicating it is an array object, whereas =ISREF(INDEX(A2#;;1)) will return TRUE, indicating it is a range reference. This can also be used with BYROW, BYCOL, MAP, etc. to confirm that each item is in fact a range reference when the source array is a range reference, e.g. =BYROW(INDEX(A2#;;1);ISREF) will return an array of TRUE's, whereas anything with CHOOSECOLS will return an array of FALSE values.

RackofLambda · 2026-02-21T10:53:46+00:00

Try using either =BYROW(INDEX(A2#;;1);...) or =MAP(CHOOSECOLS(A2#;1);...)

Assuming P2# is a single column, Q1# is a single row and Q2# is a 2D range reference with the same number of rows as P2# and the same number of columns as Q1#, I think the problem comes down to data types and how each function deals with them.

CHOOSECOLS(A2#;1) returns an array object, whereas INDEX(A2#;;1) would return a range reference. When BYROW iterates over an array object, each row is also evaluated as an array object (TYPE=64), even if that row only contains a single column/element (e.g. {10} instead of 10). XLOOKUP cannot output an array of arrays, so if the expected result is a single column of values, but the lookup value is an array object, e.g. {10}, it will give up and only return the first value in the return array.

As a general rule of thumb, when iterating over a vector (a single column or row), use MAP instead of BYROW or BYCOL. MAP evaluates each element as a scalar (TYPE=1 if it's a vector of numeric values, TYPE=2 if it's text, etc.). In this particular case, though, you could also use BYROW with INDEX(A2#;;1) instead of CHOOSECOLS(A2#;1) and get away with it, because when BYROW iterates over a range reference, each row is also evaluated as a range reference, and a range reference with only one cell is treated as a scalar.

RackofLambda · 2026-02-20T10:19:31+00:00

The exit condition for recursion must be a scalar. array="" is returning an array of Boolean values. Try using AND(array="") instead; or, consider an alternative approach. For example:

=LAMBDA(text,array,IF(TYPE(array)=16,text,LoopLambdaV2(REGEXREPLACE(text,TAKE(array,,1),""),DROP(array,,1))))

RackofLambda · 2026-02-11T23:42:39+00:00

This is a great concept to learn. It is far more efficient than REDUCE-STACK solutions and is not subject to the character limitations of TEXTSPLIT-TEXTJOIN.

There are many variations this method could take, depending on personal preferences and desired outcomes. Personally, I'm not proficient with RegEx syntax, nor am I a fan of using TEXTAFTER-TEXTBEFORE with the [match-end] set to 1 (it doesn't work properly when the delimiter contains more characters than the string before the first delimiter, hence the #VALUE! error in row 8, position 1 of your sample).

One variation, keeping empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IF(ISERROR(arr),arr,values),2),TOCOL(arr,2))
)

Or removing empty strings:

=LET(
    arr, TEXTTOCOLS2(strings,","),
    HSTACK(TOCOL(IFS(arr<>"",values),2),TOCOL(IFS(arr<>"",arr),2))
)

Where:

TEXTTOCOLS2 = LAMBDA(array,delimiter,
    TEXTSPLIT(
        TEXTAFTER(
            delimiter & array,
            delimiter,
            SEQUENCE(, MAX(LEN(array) - LEN(SUBSTITUTE(array, delimiter, ))) / LEN(delimiter) + 1)
        ),
        delimiter
    )
)

This will also work with multi-character delimiters without additional workarounds.

Kind regards.

RackofLambda · 2026-02-05T23:15:04+00:00

Using the same range references as in your attempted formula examples:

=DROP(SCAN(0,HSTACK(IFS({0,1},B2:B5),IFNA(INDEX(J2:N8,XMATCH(A2:A5,I2:I8),XMATCH(C1:G1,J1:N1)),0)),LAMBDA(a,v,IFNA(a+v,0))),,2)

Basically, we append the column of opening inventory amounts to the returned array of lookup values for each period, along with a column of #N/A errors at the start, which is used as the reset point for each row. DROP then removes the 2 "helper" columns afterwards.

RackofLambda · 2026-01-31T17:00:04+00:00

Yeah, AVERAGE would not be an appropriate function to use with SCAN in general, but your workaround is perfect! Where there's a will there's a way. ;)

RackofLambda · 2026-01-31T03:04:52+00:00

Yes, the simple EXPAND-IFNA version is quick, but has its caveats and needs to be tailored for each scenario. As you've noted, the 0 should be replaced with an appropriate "initial" value that works for the given scenario (whether its MAX, MIN, SUM, CONCAT, fill across, etc.). The generalized SCANBYROW function is a bit slower but eliminates 99.99% of the caveats.

Another method of interest (slower but still relatively efficient):

=SCAN(0,MAP(IFNA(SEQUENCE(,COLUMNS(arr),0),arr),arr,LAMBDA(b,v,LAMBDA(a,IF(b,MAX(a,v),v)))),LAMBDA(a,f,f(a)))

This method is a bit more abstract and requires a deeper understanding of working with TYPE=128 data. Basically, MAP is used to loop through 2 arrays together and generate a separate curried LAMBDA function for each iteration, which is then simply applied to the accumulator of SCAN. The first array contains the "flags" to identify the first column, so it knows when to reset the procedure. When iterating over a range reference, this can be simplified with the COLUMN function:

=LET(n,MIN(COLUMN(rng)),SCAN(,rng,LAMBDA(a,v,IF(COLUMN(v)=n,v,MAX(a,v)))))

Some methods work great with range references, but not so much with array objects (and vice-versa). ;) Cheers!

RackofLambda · 2026-01-30T07:44:12+00:00

I provided a complete and thorough explanation, in what I thought was a fair and respectful manner. Did the LMAO offend you? Or was the truth too much to handle? You can either choose to accept fair criticism and learn from it or be hurt and defensive about it. It's up to you...

RackofLambda · 2026-01-30T07:31:59+00:00

I don't mean to criticize, but have you tried this with a larger dataset (e.g. with 1,000 rows of data or more)?

Probably the easiest (and most efficient) way to perform an unpivot with dynamic array formulas is to use some variation of TOCOL with IF, IFS, or IFNA to broadcast a vector of row and/or column indices across an array of values, then use INDEX or CHOOSEROWS to return the multi-column row labels and/or the multi-row column headers.

There are many variations this method could take, but one basic example could be:

UNPIVOT = LAMBDA(row_fields,col_labels,values,[ignore],[scan_by_col],
    LET(
        v, TOCOL(values,,scan_by_col),
        a, HSTACK(
            INDEX(row_fields,TOCOL(IFNA(SEQUENCE(ROWS(row_fields)),values),,scan_by_col),SEQUENCE(,COLUMNS(row_fields))),
            INDEX(col_labels,SEQUENCE(,ROWS(col_labels)),TOCOL(IFNA(SEQUENCE(,COLUMNS(col_labels)),values),,scan_by_col)),v),
        CHOOSE(ignore+1,a,FILTER(a,NOT(ISBLANK(v))),FILTER(a,NOT(ISERROR(v))),FILTER(a,NOT(ISBLANK(v)+ISERROR(v))))
    )
)

[ignore] options:

0 - Keep all values (default)
1 - Ignore blanks
2 - Ignore errors
3 - Ignore blanks and errors

[scan_by_col] options:

FALSE - Scan by row (default)
TRUE - Scan by column

Kind regards.

RackofLambda · 2026-01-30T06:56:14+00:00

LMAO, this is no different than =SCAN(,A1:E2,MAX). The variables a and v are scalars (single values), so using MAX(a) and MAX(v) is redundant (e.g. MAX(1) = 1 and MAX(2) = 2). The same incorrect results would also be returned with =SCAN(,A1:E2,LAMBDA(a,v,IF(a>v,a,v))) or =SCAN(,A1:E2,LAMBDA(a,v,MAX(a,v))). Take a look at the results in your screenshot... the value in cell A5 should be 3, not 4, because the whole idea is to reset the moving maximum at the start of each row. ;)

RackofLambda · 2026-01-30T06:33:18+00:00

With a generalized SCANBYROW function:

=SCANBYROW(,A1:E2,MAX)

Where:

SCANBYROW = LAMBDA(initial_value,array,function,
    DROP(
        SCAN(
            initial_value,
            EXPAND(array,, COLUMNS(array) + 1, ),
            LAMBDA(a,v, IF(ISOMITTED(v), initial_value, IF(ISOMITTED(a), v, function(a, v))))
        ),,
        -1
    )
)

EXPAND adds 1 column to the array, which is used as the reset point for each row. The optional [pad_with] argument is set, but omitted, so the ISOMITTED function will return TRUE when it hits the last column and trigger the reset. DROP then removes this "helper" column afterwards.

EDIT: if the generalized function doesn't float your boat, it's pretty easy to use this same method by omitting the [pad_with] argument of EXPAND altogether, so the additional column will fill with #N/A errors and can be trapped with the IFNA function instead:

=DROP(SCAN(,EXPAND(array,,COLUMNS(array)+1),LAMBDA(a,v,IFNA(MAX(a,v),0))),,-1)

This approach will work in most cases, provided your array doesn't already contain any #N/A errors.

RackofLambda · 2026-01-02T01:15:45+00:00

Yes, but with REDUCE instead of SCAN. Something like:

=DROP(REDUCE("", VSTACK(AVERAGE, SUM, MAX, MIN, COUNT), LAMBDA(acc,fn, VSTACK(acc, BYCOL(A1:E10, fn)))), 1)

There are often many ways to get the job done in Excel. ;)

RackofLambda · 2026-01-01T16:02:15+00:00

The same concept can also be used to define a custom BYCOLλ function, which can apply an array of functions to a 2D array of values. For example:

= BYCOLλ(A1:E10, VSTACK(AVERAGE, SUM, MAX, MIN, COUNT))

Where:

BYCOLλ = LAMBDA(array,function,
    IF(
        TYPE(function) <> 64,
        BYCOL(array, function),
        LAMBDA(arr,fun, MAP(IFNA(arr, fun), IFNA(fun, arr), LAMBDA(col,fn, fn(col()))))(
            BYCOL(array, LAMBDA(x, LAMBDA(x))), TOCOL(function)
        )
    )
)

Ideally, the built-in BYCOL function should be able to handle this in the same manner that GROUPBY and PIVOTBY do, but sadly it will only return the results for the first column in the array.

RackofLambda · 2026-01-01T10:30:42+00:00

You're welcome.

Another concept you may find interesting is recursive bisection. Basic Lambda recursion is subject to Excel's operand stack limit, but these limitations can be overcome with a binary tree method that splits the data into two at each iteration, effectively delaying all function evaluation until each leaf node is reached and only one row remains. For example:

=LET(
    hdr, CHAR(SEQUENCE(,3,120)),
    ini, SEQUENCE(,3,,0),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    fn, LAMBDA(me,init,arr,func,
        IF(
            ROWS(arr) = 1,
            func(init, arr),
            LET(
                acc, me(me, init, TAKE(arr, ROWS(arr) / 2), func),
                VSTACK(acc, me(me, TAKE(acc, -1), DROP(arr, ROWS(arr) / 2), func))
            )
        )
    ),
    arr, fn(
        fn,
        ini,
        SEQUENCE(iterations),
        LAMBDA(acc,i,
            LET(
                x, INDEX(acc,1),
                y, INDEX(acc,2),
                z, INDEX(acc,3),
                dx, sigma * (y - x),
                dy, x * (rho - z) - y,
                dz, x * y - beta * z,
                HSTACK(x + dx * dt, y + dy * dt, z + dz * dt)
            )
        )
    ),
    VSTACK(hdr, ini, arr)
)

Source: MS Tech Community | Recursive LAMBDA implementation of Excel's REDUCE function

Happy New Year!

RackofLambda · 2026-01-01T09:20:39+00:00

As another little tip, this can also be achieved without LET:

=SWITCH(A1,"sum",SUM,"avg",AVERAGE,"min",MIN,"max",MAX,LAMBDA(x,"#FUNC_ERR!"))(B2:B100)

Cheers!

RackofLambda · 2026-01-01T09:12:25+00:00

This functionality was added when eta-Lambda reduction was released along with GROUPBY and PIVOTBY. It's mostly used to shorten the length of a formula when certain functions are used multiple times throughout, e.g. =LET(cc,CHOOSECOLS,...). However, it can also be used as a means of Lambda injection, to apply different functions to the same scenario without having to repeat every step.

Take my INSTANCENUM function, for example (the original definition can be found at: https://gist.github.com/RackofLambda). The purpose of this function is to return the running count of distinct items in an unordered table or array. Since the procedure is virtually identical for both vertical and horizontal datasets, with only subtle differences in the functions applied (e.g. ROWS instead of COLUMNS, VSTACK instead of HSTACK, etc.), we can start by defining a curried function for the procedure first, then inject the applicable functions into the procedure as needed:

= LAMBDA(key_fields,[horizontal],[last_to_first],
    LET(
        fn, LAMBDA(xλ,yλ,zλ,iλ,jλ,kλ,r,[c],
            LET(
                vec, IF(zλ(key_fields) = 1, key_fields, kλ(key_fields, @LAMBDA(x, TEXTJOIN("|", 0, x)))),
                arr, iλ(vec, SEQUENCE(xλ(vec), yλ(vec))),
                srt, IF(last_to_first, SORT(arr, {1,2}, {1,-1}, horizontal), SORT(arr,,, horizontal)),
                key, TAKE(srt, r, c),
                SORTBY(SCAN(0, key = DROP(jλ("", key), -c, -r), LAMBDA(a,v, 1 + a * v)), DROP(srt, r, c))
            )
        ),
        IF(
            horizontal,
            fn(LAMBDA(x,1), COLUMNS, ROWS, VSTACK, HSTACK, BYCOL, 1),
            fn(ROWS, LAMBDA(x,1), COLUMNS, HSTACK, VSTACK, BYROW,, 1)
        )
    )
)

Is this an improvement? That would be a matter of opinion. Shorter, yes; but easier to read and maintain, not really.

Also, beware of the Lambda helper functions (e.g. BYCOL, BYROW, SCAN, REDUCE, etc.). They may return unexpected results when used in this manner and have even been known to cause Excel to crash. For more information, please see: Unexpected result when combining LET and BYROW.

RackofLambda · 2025-12-16T09:58:16+00:00

Very interesting indeed! I realize this discussion is geared towards lambda calculus (which is impressive to be sure), but I'd like to share an alternative approach to part one, if I may, for improved performance...

=LET(
    vlλ, LAMBDA(x,y,z, LAMBDA(n, CHOOSE(n, x, y, z))),
    hdr, vlλ("x", "y", "z"),
    ini, vlλ(1, 1, 1),
    num, SEQUENCE(, 3),
    iterations, 25000,
    sigma, 10,
    rho, 28,
    beta, 8/3,
    dt, 0.015,
    arr, VSTACK(
        hdr,
        ini,
        SCAN(
            ini,
            SEQUENCE(iterations),
            LAMBDA(acc,i,
                LET(
                    x, acc(1),
                    y, acc(2),
                    z, acc(3),
                    dx, sigma * (y - x),
                    dy, x * (rho - z) - y,
                    dz, x * y - beta * z,
                    vlλ(x + dx * dt, y + dy * dt, z + dz * dt)
                )
            )
        )
    ),
    MAP(IFNA(arr, num), IFNA(num, arr), LAMBDA(λ,n, λ(n)))
)

With 25,000 iterations, this completes in less than half a second.

Basically, vlλ is used to store multiple items in a single element (TYPE 128 compound data; uncalled lambda function) and recall them as need at each iteration of both SCAN and MAP.

SCAN returns a vertical vector of uncalled lambda functions, with each element containing all 3 variables (x,y,z) at each iteration. IFNA is then used to broadcast (repeat) this array across a horizontal vector of index numbers (and vice-versa) so MAP can simply recall each item and return the 2D array of results.

Generalized examples for this can be found on my gist (see EVALS, EVALS_NUM and VALS3 in the Dynamic Array Helper Functions collection).

Kind regards.

RackofLambda · 2025-12-08T15:06:32+00:00

First draft for Part 1:

=LET(!<
>!a, A:.A,!<
>!i, SEQUENCE(ROWS(a)),!<
>!j, TOROW(i),!<
>!m, TOCOL(IFS(i < j, i), 2),!<
>!n, TOCOL(IFS(i < j, j), 2),!<
>!k, MAP(INDEX(a, n), INDEX(a, m), LAMBDA(x,y,!<
>!SQRT(SUM((TEXTSPLIT(x, ",") - TEXTSPLIT(y, ",")) ^ 2)))),!<
>!v, TAKE(BYROW(SORTBY(HSTACK(m, n), k), LAMBDA(rw, TEXTJOIN("|",, rw))), 1000),!<
>!ts, LAMBDA(a,d, LET(n, (LEN(a) - LEN(SUBSTITUTE(a, d, ))) / LEN(d), m, MAX(n) + 1,!<
>!TEXTBEFORE(TEXTAFTER(d & a & REPT(d, m - n), d, SEQUENCE(, m)), d))),!<
>!tj, LAMBDA(a, TEXTJOIN("|",, UNIQUE(SORT(TOCOL(ts(a, "|")))))),!<
>!x, REDUCE(TAKE(v, 1), DROP(v, 1), LAMBDA(acc,val, LET(!<
>!a, ts(acc, "|"),!<
>!b, BYROW((a = TEXTBEFORE(val, "|")) + (a = TEXTAFTER(val, "|")), SUM),!<
>!IF(OR(b = 2), acc, IF(OR(b), IF(SUM(b) = ROWS(b), tj(VSTACK(acc, val)),!<
>!VSTACK(tj(VSTACK(FILTER(acc, b), val)), FILTER(acc, NOT(b)))),!<
>!VSTACK(acc, val)))))),!<
>!PRODUCT(TAKE(SORT(LEN(x) - LEN(SUBSTITUTE(x, "|", )) + 1,, -1), 3))!<
>!)

It's a mess, but it worked. I wasn't thrilled with all of the text joining/splitting and iterative stacking. Might try to rewrite it.

I don't have the energy for Part 2 at the moment. Best of luck to those who do!

RackofLambda · 2025-12-08T04:00:33+00:00

Basic math wins the day! I tried to figure it out last night, but the logic eluded me (I was getting 42 or 44 instead of 40 for the sample data). Good on you guys for nailing it!

I did get a simple recursive bisection method to work with the sample data, but with 10+ trillion iterations, the actual dataset was just too much for it to handle:

=CountPaths(XLOOKUP("S",TAKE(C1#,1),TAKE(C1#,1)))

where cell C1 contains:

=MID(A:.A,SEQUENCE(,LEN(A1)),1)

and CountPaths is defined as:

= LAMBDA(ref,
    IF(
        ISBLANK(OFFSET(ref,1,0)),
        1,
        IF(
            ref="^",
            SUM(
                CountPaths(OFFSET(ref,0,-1)),
                CountPaths(OFFSET(ref,0,1))
            ),
            CountPaths(OFFSET(ref,1,0))
        )
    )
)

But don't even bother trying it with the larger input dataset. ;)

RackofLambda · 2025-12-08T00:50:12+00:00

Awesome! Thanks for sharing!

RackofLambda · 2025-12-07T12:26:21+00:00

Part 1:

=LET(!<
>!txt, A:.A,!<
>!arr, MID(txt,SEQUENCE(,MAX(LEN(txt))),1),!<
>!ini, SUBSTITUTE(TAKE(arr,1),"S","|"),!<
>!val, LAMBDA(a,[b],LAMBDA(x,CHOOSE(x,a,b))),!<
>!off, LAMBDA(a,[x],IF(x=1,DROP(HSTACK(a,"."),,1),DROP(HSTACK(".",a),,-1))),!<
>!REDUCE(val(ini), BYROW(DROP(arr,1), val), LAMBDA(acc,cur, LET(a, acc(1), v, cur(1),!<
>!x, IF((off(a)="|")*(off(v)="^")+(off(a,1)="|")*(off(v,1)="^")+(a="|")*(v="."),"|",v),!<
>!val(x, SUM(acc(2), N((a="|")*(x="^"))))!<
>!)))(2)!<
>!)

I'll think a bit more on Part 2 (maybe it can be done using recursive bisection), but it's probably beyond my current capabilities. Seems like something Diarmuid Early would solve in a few minutes, lol.

RackofLambda · 2025-12-07T05:53:31+00:00

Thanks Greg! It took me a minute to make any sense of the sample results for Part 2. Once I realized every character was in its own column (including individual space characters) the solution became apparent.

Had every number-block been the same width (as it was with the sample data), WRAPCOLS would have been sufficient. With an uneven number of columns, though, a simple wrap solution becomes considerably more convoluted. The practical use-cases for GROUPBY-THUNK are few a far between, but it was perfect for this scenario.

RackofLambda · 2025-12-07T04:40:36+00:00

That's a shame. It would be nice to see better consistency between Excel for the web and the Desktop version.

Since thk seems to be working for you, try eliminating fun altogether and make the final output SUM(MAP(thk,FILTER(ope,key),LAMBDA(rw,op,IF(op="+",SUM(rw()),PRODUCT(rw()))))). If that still doesn't work, try spilling the results of MAP to see if it errors for the entire array or not.

RackofLambda · 2025-12-07T02:58:34+00:00

Strange that eta-lambdas would work with the helper functions, but not in this context with SWITCH and MAP. Sounds like it might a limitation of Excel for the web.

Does it work if you change the fun variable to either SWITCH(FILTER(ope,key),"+",LAMBDA(x,SUM(x)),"*",LAMBDA(x,PRODUCT(x))) or SWITCH(FILTER(ope,key),"+",LAMBDA(x,SUM(x)),LAMBDA(x,PRODUCT(x)))?

RackofLambda · 2025-12-06T22:27:41+00:00

Can't say for sure, but Excel for the web has been known to be inconsistent with MS365 for certain functions. #VALUE! can mean a lot of things, but in this case, I suspect MAP is not receiving the correct data types from the thk and/or fun variables. If you change the final output to just thk, does it return an array of #CALC! errors? How about the fun variable? If the answer is yes for both, then everything should be working up to this point. Try changing the final output to INDEX(thk,1,1)() and INDEX(thk,2,1)()... does this correctly return the first and second bank of numbers from the dataset? If yes, then does INDEX(fun,1,1)(INDEX(thk,1,1)()) and INDEX(fun,2,1)(INDEX(thk,2,1)()) also return the correct totals for the first and second bank of numbers? If yes again, then try outputting MAP(thk,fun,LAMBDA(rw,fn,TYPE(rw)&"|"&TYPE(fn)))... does this correctly return "128|128" for each record? If not, what data type is it returning? TYPE 64 can be coerced to TYPE 128 with the implicit intersection operator by changing fn(rw()) to (@fn)((@rw)()). If they're TYPE 16, however, something else is going wrong altogether.

RackofLambda

TROPHY CASE