Hi,

You can try using the formula below, which is based on the same logic you used but avoids the full‑column ranges. In many cases it performs faster and prevents unnecessary recalculation.

=LET(
     data, Detail!A1:BZ20,
       rd, --REDUCE(data, CHAR(160), LAMBDA(a,b, SUBSTITUTE(a, b, ""))),
       ts, TEXTSPLIT(TEXTJOIN(";",, CHOOSECOLS(data, 49)), ",", ";"),
     FILTER(SORT(UNIQUE(HSTACK(CHOOSECOLS(rd, {1,3,4,5,68}),
     IFNA(IF(ISNUMBER(--ts), --ts, ts), ""), CHOOSECOLS(rd, 53)), TRUE)),
     COUNTIF(B16:B25, Detail!BP1:BP20) > 0)
)

Note: I have assumed that all the data within the range Detail!A1:BZ20 consists only of numeric values, and that there are no comma‑separated entries anywhere except in column 49.

IlirU

Split Column by Delimiter

Since you are trying to split 2 columns by delimiter, it's a good idea to create a Lambda function to do this:

=LAMBDA(c,d,LET(
    n,CountD(c,d),
    m,MAX(n)+1,
    TEXTBEFORE(TEXTAFTER(d&c&REPT(d,m-n),d,
        SEQUENCE(,m)),d)))

Using the Name Manager, you could define a name, e.g., SplitD and copy the formula above into the RefersTo box.

Similarly, since you are doing your 'non-breaking space business' several times, you could create the following Lambda function named, e.g. Nbs:

=LAMBDA(a,TRIM(SUBSTITUTE(a,CHAR(160),"")))

I kept the functions inside the formula so you need not bother at the moment.

=LET(a,Detail!A2:BZ21,b,$B$16:$B$25,
    Nbs,LAMBDA(a,TRIM(SUBSTITUTE(a,CHAR(160),""))),
    SplitD,LAMBDA(c,d,LET(
        n,LEN(c)-LEN(SUBSTITUTE(c,d,)),
        m,MAX(n)+1,
        TEXTBEFORE(TEXTAFTER(d&c&REPT(d,m-n),d,
            SEQUENCE(,m)),d))),
    ia,Nbs(CHOOSECOLS(a,49))<>"",
    ib,COUNTIF(b,CHOOSECOLS(a,68))>0,
    f,FILTER(a,ia*ib),
    base,CHOOSECOLS(f,1,3,4,5,68),
    cAW,TRIM(SplitD(Nbs(CHOOSECOLS(f,49)),",")),
    cBA,TRIM(SplitD(Nbs(CHOOSECOLS(f,53)),",")),
    r,SORT(UNIQUE(HSTACK(base,cAW,cBA)),1,1),
    r)

I would strongly suggest you put your data into a table and use structured references instead of utilizing column indices.

Your error is coming from TEXTSPLIT() when the input is blank / not text (or when the filter returns no rows). Make the split ranges “safe” by coercing to text and providing a fallback.

Try this pattern (key change: IFERROR(TEXTSPLIT(...), value) and &"" to coerce to text):

=LET( f, FILTER(Detail!$A:$BZ, (COUNTIF($B$16:$B$25,Detail!$BP:$BP)>0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW,CHAR(160),""))<>"") ), base, CHOOSECOLS(f,1,3,4,5,68), c49, SUBSTITUTE(CHOOSECOLS(f,49)&"",CHAR(160),""), split49, IFERROR(TRIM(TEXTSPLIT(c49,",")), TRIM(c49)), c53, SUBSTITUTE(CHOOSECOLS(f,53)&"",CHAR(160),""), split53, IFERROR(TRIM(TEXTSPLIT(c53,",")), TRIM(c53)), SORT(UNIQUE(HSTACK(base,split49,split53)),1,TRUE) )

If you also want to avoid errors when FILTER returns nothing, wrap f:

f, IFERROR(FILTER(...), "")

That should stop the #VALUE! and still “explode” comma-separated values when present.

Let me know, if it works or not...

Here’s a clearer explanation of why your formula breaks — and a corrected version that will work even when there are no commas in columns 49 or 53.

💡 Why your formula returns #VALUE!

TEXTSPLIT() throws a #VALUE! error when the input is a vertical array (multiple rows) and the delimiter is not found in some rows.
In your formula:

TEXTSPLIT(CHOOSECOLS(f,49), ",")

CHOOSECOLS(f,49) returns multiple rows, but TEXTSPLIT() expects a single text string, not an array of many rows.

So when a row has no comma, Excel can’t split it consistently across rows → #VALUE!.

✅ The fix: apply TEXTSPLIT() row‑by‑row using MAP()

MAP() lets you apply a function to each row independently, which prevents the #VALUE! error.

Here is the corrected formula:

=LET( f, FILTER( Detail!$A:$BZ, (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "") ), base, CHOOSECOLS(f, 1,3,4,5,68), split49, MAP( CHOOSECOLS(f,49), LAMBDA(x, TEXTSPLIT(TRIM(SUBSTITUTE(x,CHAR(160),"")), ",")) ), split53, MAP( CHOOSECOLS(f,53), LAMBDA(x, TEXTSPLIT(TRIM(SUBSTITUTE(x,CHAR(160),"")), ",")) ), SORT(UNIQUE(HSTACK(base, split49, split53)), 1, TRUE) )

🧠 What this version does differently

✔ MAP() processes each row individually

So if one row has "A,B,C" and another has "X" (no comma), both are handled safely.

✔ TEXTSPLIT() always receives a single text value

No more array‑wide splitting → no more #VALUE!.

✔ Output columns expand correctly

Rows with more comma‑separated values simply spill into more columns.

If you want, I can also:

• Modify it to pad missing columns
• Combine split49 and split53 into a single dynamic block
• Return results in a vertical exploded list instead of horizontal columns
• Add error‑handling or blank‑value suppression

Just tell me how you want the output to behave.

Hey tonyceccato​, if you'd like for me to keep trying lmk! I hope this helps. 

StephanieBowie​