We see this problem all the time: a spreadsheet full of messy text, a list of replacements that keeps growing, and the sinking feeling that nested `SUBSTITUTE` functions will turn your workbook into an unreadable tangle. The user who posted this question is not alone, they have a perfectly reasonable data-cleaning task, and they correctly identified that the brute-force approach doesn't scale. Their instinct to reach for `REDUCE` was smart, but the implementation missed a key detail.
The issue is straightforward. The `REDUCE` formula they wrote iterates over the *entire lookup range* in one pass, applying every replacement to the original text simultaneously rather than sequentially. When multiple old values appear in the same cell, like "Apples" and "Bananas" and "Lemons", the function overwrites previous substitutions because it processes the entire list at once. The output of "Blueberries" for every cell is the tell: the last replacement in the table (Strawberries → Blueberries) runs last and wipes out everything before it. The fix is to ensure `REDUCE` applies one replacement at a time, building on the result of the previous step. That means passing the *accumulated text* back into the next iteration, not starting fresh from the original cell each time.
For users familiar with Python, this is a natural fit. A simple loop over the replacement pairs, `for old, new in zip(old_list, new_list): text = text.replace(old, new)`, gives you clean, readable, and maintainable code. You can even wrap it in a custom function using Google Apps Script or Excel's VBA and call it from any cell. But for those who want to stay inside the formula bar, a corrected `REDUCE` approach works: `=REDUCE(A2, SEQUENCE(ROWS(lookup_table)), LAMBDA(acc, i, SUBSTITUTE(acc, INDEX(old_range, i), INDEX(new_range, i))))`. This applies each replacement in order, building the result step by step.
The lesson here is about choosing the right tool for the job, and understanding how that tool actually works under the hood. `REDUCE` is powerful, but it demands a mental model of accumulation, not batch processing. And when your transformation logic is inherently sequential, a simple loop in a scripting language can be more transparent than a clever formula that obscures its own behavior. The user's frustration is justified, but the path forward is clear: either fix the accumulator logic or switch to Python. Both approaches are accessible, and both will save hours of nested `SUBSTITUTE` headaches. The real win is not just cleaner data, it's a workflow you can actually read, debug, and share.