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​