Curated formulas, grouped by what you're trying to do
Every entry shows the syntax, a real-world example, and one line on when to reach for it (and when not to).
XLOOKUP
=XLOOKUP(lookup, lookup_array, return_array, [if_not_found])
The modern lookup. Searches in any direction, has a built-in not-found fallback, and doesn't break when columns are inserted.
INDEX + MATCH
=INDEX(return_range, MATCH(lookup, lookup_range, 0))
The classic flexible pair. Still useful in older Excel where XLOOKUP isn't available.
VLOOKUP
=VLOOKUP(lookup, table, col_index, FALSE)
Common but fragile — breaks when columns shift. Prefer XLOOKUP whenever you have it.
IF / IFS
=IFS(A2>=90,"A", A2>=75,"B", A2>=60,"C", TRUE,"D")
IFS reads top-down — much cleaner than nested IFs. End with TRUE, default as a catch-all.
SWITCH
=SWITCH(A2, "N","North", "S","South", "Other")
Better than IF chains when you're mapping discrete values to labels.
IFERROR
=IFERROR(formula, fallback)
Catches every error type. Use it as the outermost wrapper — never bury a real bug under a blanket IFERROR.
TEXTSPLIT
=TEXTSPLIT("a,b;c", ",", ";")
Splits by both column and row delimiters. Replaces nested LEFT/MID/FIND combos.
TEXTJOIN
=TEXTJOIN(", ", TRUE, A2:A20)
Joins a range with a separator and skips blanks — something CONCATENATE could never do.
TRIM + CLEAN
=TRIM(CLEAN(A2))
Strip extra spaces and non-printing characters. Run this on every imported text column before lookups.
DATEDIF
=DATEDIF(start, end, "Y")
Hidden function (no autocomplete) that returns date differences in Y/M/D/YM/MD units. Perfect for age and tenure.
EOMONTH
=EOMONTH(TODAY(), 0)
Last day of the given month. Use offset 1 for next month-end, -1 for previous.
WORKDAY.INTL
=WORKDAY.INTL(start, n_days, weekend_code, [holidays])
N working days from a start date, with custom weekends and a holidays range. The .INTL version handles non-Mon-Fri weeks.
SUMIFS / COUNTIFS / AVERAGEIFS
=SUMIFS(amount, region, "East", date, ">="&TODAY())
Multi-condition aggregation. Always use the plural ...IFS form — it scales when you add a second condition later.
SUBTOTAL
=SUBTOTAL(9, range)
Aggregates only visible rows — perfect with autofilters. Function code 9 = SUM, 1 = AVERAGE, 3 = COUNTA.
AGGREGATE
=AGGREGATE(9, 6, range)
Like SUBTOTAL but can ignore errors (option 6). The most underused aggregation function in Excel.
FILTER
=FILTER(data, condition, "No matches")
Returns matching rows that update live with the source. Replaces ad-hoc autofilter copy-paste.
UNIQUE / SORT
=SORT(UNIQUE(A2:A1000))
One-formula deduplicated, ordered list. No menu, no refresh.
SEQUENCE
=SEQUENCE(12, 1, 1, 1)
Generate 1..12 down a column without dragging. Great for dates: =DATE(2026, SEQUENCE(12), 1) gives the first of each month.
LET
=LET(net, A2-B2, tax, net*0.18, net+tax)
Name sub-expressions inside a formula. Read better, recalc faster, no helper cells.
LAMBDA
=LAMBDA(x, y, x*x + y*y)(3, 4)
Build your own reusable function. Define once in Name Manager, call it like a built-in everywhere.