Simplify a Complex Google Sheets Formula (Without Breaking It)

The Old Way (Nested IF, Then IFS)

Here is the shape you almost certainly inherited.

The same VLOOKUP is written three times. Sheets does not cache it for you, so each cell can run up to three full column scans. Across 10,000 rows that is up to 30,000 scans, which is where your eleven second recalculation comes from.

The most common attempt to clean this up is IFS, because it flattens the nesting.

This looks tidier and is quietly broken. IF short circuits, so the branch it does not take is never evaluated. IFS evaluates its conditions eagerly. When A2 is not in the lookup range, the second condition runs anyway, produces #N/A, and that error propagates out of the whole formula. You get #N/A in the cell instead of the string N/A the original returned. The refactor changed the output on exactly the rows the formula existed to handle.

Fix the Repetition With LET

Before reaching for anything clever, name the repeated subexpression. LET evaluates it once and reuses the value.

One lookup per cell instead of three, same output including the N/A string, and it now fits on one line. That is the honest native answer, and for a pure lookup you should stop here. No AI needed.

Two rules cover most of these rewrites. Anything written more than once becomes a LET variable, and any IF(ISERROR(x), fallback, x) pattern collapses to IFERROR(x, fallback).

The FITS Way (When the Branches Encode Judgment)

LET cannot help with the other kind of monster, the one that grew a new nested branch every time somebody found an edge case. You have seen it.

No amount of refactoring fixes this, because the length is not the bug. The bug is that it is a keyword list pretending to be a rule. It sends "I want my money back" to Other, because nobody thought to add that phrase yet, and it will keep growing forever.

Replace the whole chain with the rule it was approximating.

One line, no keyword list to maintain, and new phrasings land in the right bucket without an edit. The constrained label set is what keeps the column joinable.

FITS also helps before you touch anything, when the problem is that nobody knows what the formula does. Paste it into a cell as text and ask.

Do that first. Rewriting a formula whose behaviour you have not written down is how the IFS mistake above gets shipped.

When to Use Each

If the formula is long because a subexpression repeats, use LET and IFERROR and keep it native. Reach for =FITS() when the branches are a growing list of special cases, because that list is never finished. Related reading: the IF THEN formula explained, describing a formula instead of writing one, and classifying text into fixed categories. The full tour is in automating Google Sheets tasks you used to need regex for.