Ten Excel Formulas Every Analyst Reuses Constantly

VLOOKUP is still everywhere, but it only looks rightward from its lookup column and breaks silently if someone inserts a column in between. XLOOKUP fixes both problems — it can look in any direction and returns a clear #N/A instead of a wrong number when nothing matches.

If you're on a version of Excel without XLOOKUP, INDEX+MATCH does the same job and is worth learning once: MATCH finds the row, INDEX pulls the value from it, and neither one cares which side of the lookup column your answer lives on.

SUMIFS and COUNTIFS handle the question 'total this, but only where these other columns match certain conditions' — total sales for one region in one month, count of orders above a threshold, and similar. They take multiple criteria ranges at once, which plain SUMIF and COUNTIF can't do.

IFERROR wraps around a formula that might fail — a lookup that doesn't find a match, a division by zero — and lets you show a blank or a custom message instead of an ugly error code spreading through your sheet.

TRIM removes extra spaces that silently break lookups and comparisons (two values that look identical but don't match because one has a trailing space). TEXTJOIN combines several cells into one string with a separator, which is faster than chaining a long string of & operators.

Here's the exact kind of request the Formula Assistant is built for: describe "Sum column B where column A equals 'Paid'" and it returns =SUMIF(A:A,"Paid",B:B) along with a short explanation of what each part does. That's a SUMIF, not one of the more advanced formulas above — most real requests are simpler than people expect, which is part of why describing the need is often faster than looking up the exact syntax.

The same tool handles the reverse case: paste a formula that's returning #REF! or a wrong total, describe what it should actually do, and it suggests a corrected version. This is the same beta model referenced above — always run the result on your real data before trusting it.

Asking ChatGPT (or a similar general-purpose chatbot) for an Excel formula works fine for a one-off, low-stakes question — you don't need an account, and it's a familiar interface. The tradeoff is copying your description into a separate chat window, then copying the answer back into your spreadsheet: an extra round trip for something you're about to paste into the same file you're already looking at.

Formula Assistant is built for the version of that same task where you're already inside a spreadsheet workflow: describe the formula on the tool page, get it back with a short explanation, and copy it straight in — free, no signup, no separate app. It's still an AI model in beta either way, so the accuracy tradeoff is the same: treat the output as a first draft and test it against your real data, whichever tool you use.

Most "broken" formulas aren't wrong logic — they're clean data problems wearing a formula error. A VLOOKUP or XLOOKUP that returns #N/A on a row that clearly has a match is very often a trailing space in one of the two columns being compared, not a typo in the formula (see our guide on trailing spaces breaking VLOOKUP for how to catch this before it costs you an afternoon).

A SUMIFS or COUNTIFS returning zero when you know matching rows exist usually means a criteria column has inconsistent formatting — text-formatted numbers, or a date stored as text in one file and a real date in another. Cleaning the columns first (Clean Excel Data's formatting options handle both cases) fixes the formula without touching the formula itself.

A #DIV/0! from SUMIFS-style aggregation over a filtered range that came back empty is a real signal, not a bug — IFERROR should wrap it to show a blank or a custom message rather than let the error propagate into a chart or a downstream formula.