Business Analyst · #036 · August 28, 2026 · 2 min read
Which Excel formulas still matter in 2026?
Lookups, conditional math, text, dates, logic and the modern spill functions: the 22 formulas behind most business analysis that ships, plus the three polite lies Excel tells by default.
Get the free PDF
One page, print-ready, free to share. No signup needed.
The whole company still runs on Excel, so run it well. These 22 formulas carry most business analysis that actually ships. The print-ready A4 PDF is at the bottom.
Lookups
XLOOKUP(v, A:A, B:B): the modern one, exact by default.INDEX(B:B, MATCH(…)): the classic combo.VLOOKUP(v, A:B, 2, 0): legacy; the final0means exact.
Conditional math
SUMIFS(S, C, "x"): sum if.COUNTIFS(C, ">100"): count if.AVERAGEIFS(S, C, "x"): average if.SUMPRODUCT(a, b): weighted sums.
Text
TRIM(x)/CLEAN(x): strip junk.TEXTSPLIT(x, ","): split into cells.LEFT(x,3)/RIGHT(x,2): slice.SUBSTITUTE(x,"a","b"): swap.
Dates
TODAY()/NOW(): current.EOMONTH(d, 0): end of month.NETWORKDAYS(a, b): workdays only.DATEDIF(a, b, "d"): the gap, in a function Excel does not even autocomplete.
Logic
IF(test, yes, no): branch.IFS(t1, v1, t2, v2): many branches, no nesting.IFERROR(x, 0): dashboards without #N/A walls.AND()/OR(): combine tests.
Spill magic
FILTER(A:C, C:C>0): a query, straight in a cell.UNIQUE(A:A): distinct list.SORT(x)/SORTBY(…): ordered spill.LET(x, …, x*2): name things. Stay sane.
XLOOKUP, complete
=XLOOKUP(
A2, what to find
Prices!A:A, where to look
Prices!B:B, what to return
"not found") instead of #N/A
No column counting, looks in any direction, and the fourth argument kills #N/A at the source.
The traps: the spreadsheet lies politely
| The setup | What happens | The fix |
|---|---|---|
| VLOOKUP without the 0 | approximate match, wrong row | always end with 0, or XLOOKUP |
| numbers stored as text | SUM returns 0, no error | Text to Columns, General |
| merged cells | sort and formulas break | unmerge, Center Across Selection |
Excel rarely errors. It answers the question you accidentally asked, with full confidence.
Frequently asked questions
Should I use XLOOKUP or VLOOKUP?
XLOOKUP wherever it exists. It matches exactly by default, looks left or right, needs no column counting, and its fourth argument replaces #N/A with anything you choose. VLOOKUP survives in older workbooks: if you must use it, always end it with 0 to force exact match.
What are Excel spill functions?
Functions that return a whole range from one cell: FILTER(A:C, C:C>0) queries a table, UNIQUE(A:A) returns distinct values, SORT and SORTBY order the result. Together they replace most helper columns and a good share of pivot tables.
Why does SUM return 0 on a column full of numbers?
The numbers are stored as text, usually after an export or a copy-paste. Excel shows them right-aligned-looking values but SUM treats them as words and returns 0 with no error. Fix with Data, Text to Columns, General, or multiply the range by 1.
What does the LET function do?
LET(x, expression, x*2) names intermediate results inside a formula, so a long formula computes each piece once and reads like steps instead of a single nested one-liner. Faster to compute and far easier to debug six months later.
Get the free PDF
One page, print-ready, free to share. No signup needed.