Excel Formulas Cheat Sheet

XLOOKUP

Finds a value and returns the matching item from another column — left, right, exact or approximate. Replaces VLOOKUP.

XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

=XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found")

VLOOKUP

Looks down the first column of a table and returns a value from the column number you give. Always pass FALSE for an exact match.

VLOOKUP(lookup, table, col_index, [approx])

=VLOOKUP(A2, Products!A:D, 3, FALSE)

INDEX + MATCH

The flexible classic lookup: MATCH finds the row, INDEX returns the value. Works in every version and in any direction.

INDEX(return_range, MATCH(lookup, lookup_range, 0))

=INDEX(C:C, MATCH(A2, B:B, 0))

FILTER

Returns every row that meets a condition, spilling the results into neighbouring cells.

FILTER(array, include, [if_empty])

=FILTER(A2:D500, C2:C500="West", "None")

UNIQUE

Lists the distinct values in a range.

UNIQUE(array, [by_col], [exactly_once])

=UNIQUE(B2:B500)

SORT / SORTBY

Returns a range sorted by a column; use -1 for descending.

SORT(array, [sort_index], [order])

=SORT(A2:C100, 3, -1)

SUMIFS

Adds up values that meet one or more conditions.

SUMIFS(sum_range, criteria_range1, criteria1, …)

=SUMIFS(D:D, B:B, "West", C:C, ">=2026-01-01")

COUNTIFS

Counts rows meeting every condition.

COUNTIFS(criteria_range1, criteria1, …)

=COUNTIFS(B:B, "West", E:E, "Closed won")

AVERAGEIFS

Averages values meeting every condition.

AVERAGEIFS(avg_range, criteria_range1, criteria1, …)

=AVERAGEIFS(D:D, B:B, "West")

MAXIFS / MINIFS

The largest (or smallest) value meeting the conditions.

MAXIFS(max_range, criteria_range1, criteria1, …)

=MAXIFS(D:D, B:B, "West")

SUMPRODUCT

Multiplies arrays item by item and sums the result — weighted averages and multi-condition sums.

SUMPRODUCT(array1, [array2], …)

=SUMPRODUCT(B2:B10, C2:C10) / SUM(C2:C10)

IF

Returns one value or another depending on a condition.

IF(test, value_if_true, value_if_false)

=IF(D2>=1000, "Large", "Small")

IFS

Several conditions without nesting IFs; use TRUE as the last test for a default.

IFS(test1, value1, test2, value2, …)

=IFS(D2>=10000,"Tier 1", D2>=1000,"Tier 2", TRUE,"Tier 3")

AND / OR

Combine conditions inside IF.

AND(test1, test2, …)

=IF(AND(B2="West", D2>500), "Target", "")

IFERROR

Replaces errors like #N/A or #DIV/0! with something readable.

IFERROR(value, value_if_error)

=IFERROR(C2/B2, 0)

SWITCH

Maps exact values to results — cleaner than nested IFs for codes.

SWITCH(expression, value1, result1, …, [default])

=SWITCH(B2, "N","North", "S","South", "Other")

TRIM / CLEAN

Removes extra spaces (TRIM) and non-printing characters (CLEAN) — run on any imported data.

TRIM(text)

=TRIM(CLEAN(A2))

TEXTJOIN

Joins values with a separator, optionally skipping blanks.

TEXTJOIN(delimiter, ignore_empty, text1, …)

=TEXTJOIN(", ", TRUE, A2:A10)

TEXTSPLIT

Splits text into columns (or rows) on a delimiter.

TEXTSPLIT(text, col_delimiter, [row_delimiter])

=TEXTSPLIT(A2, ",")

LEFT / RIGHT / MID

Extract characters from the start, end or middle of text.

MID(text, start, length)

=LEFT(A2, 3)

FIND / SEARCH

Position of text within text; SEARCH ignores case, FIND doesn't.

SEARCH(find_text, within_text)

=MID(A2, SEARCH("@", A2)+1, 100)

SUBSTITUTE

Replaces text by content rather than position.

SUBSTITUTE(text, old, new, [instance])

=SUBSTITUTE(A2, "-", "")

TEXT

Formats a number or date as text.

TEXT(value, format)

=TEXT(A2, "yyyy-mm")

TODAY / NOW

Today's date (NOW includes the time). Recalculates every time the sheet does.

TODAY()

=TODAY()-A2

EOMONTH

Last day of a month — great for month buckets.

EOMONTH(start_date, months)

=EOMONTH(A2, 0)

EDATE

Same day n months later or earlier.

EDATE(start_date, months)

=EDATE(A2, 12)

DATEDIF

Whole days, months or years between two dates.

DATEDIF(start, end, "d"|"m"|"y")

=DATEDIF(A2, TODAY(), "m")

NETWORKDAYS

Working days between two dates.

NETWORKDAYS(start, end, [holidays])

=NETWORKDAYS(A2, B2)

WEEKNUM / YEAR / MONTH

Pull the week, year or month out of a date for grouping.

WEEKNUM(date, [type])

=YEAR(A2)&"-W"&WEEKNUM(A2, 2)

AVERAGE / MEDIAN

Mean and middle value. Prefer the median for skewed data like salaries or order values.

MEDIAN(range)

=MEDIAN(D2:D500)

STDEV.S

Sample standard deviation (use STDEV.P for a whole population).

STDEV.S(range)

=STDEV.S(D2:D500)

PERCENTILE.INC / QUARTILE.INC

The value below which k of the data falls.

PERCENTILE.INC(range, k)

=PERCENTILE.INC(D2:D500, 0.9)

CORREL

Pearson correlation between two columns.

CORREL(array1, array2)

=CORREL(B2:B100, C2:C100)

RANK.EQ

The rank of a value in a list.

RANK.EQ(value, range, [order])

=RANK.EQ(D2, D$2:D$100)

ROUND / ROUNDUP / ROUNDDOWN

Round to a number of digits; negative digits round to tens, hundreds…

ROUND(value, digits)

=ROUND(D2, -3)

LET

Names intermediate results so long formulas are readable and faster.

LET(name1, value1, …, calculation)

=LET(rev, SUM(D:D), cost, SUM(E:E), (rev-cost)/rev)

LAMBDA

Builds your own reusable function (save it with Name Manager).

LAMBDA(param1, …, calculation)

=LAMBDA(x, x*1.2)(A2)

SEQUENCE

Generates a list of numbers or dates.

SEQUENCE(rows, [cols], [start], [step])

=SEQUENCE(12, 1, DATE(2026,1,1), 31)

GROUPBY / PIVOTBY

A pivot table as a formula (Microsoft 365).

GROUPBY(row_fields, values, function)

=GROUPBY(B2:B500, D2:D500, SUM)