Excel formulas cheat sheet

=XLOOKUP(A2, Products[SKU], Products[Price], "Not found")

// Next smaller match, e.g. tax or discount bands
=XLOOKUP(B2, Bands[From], Bands[Rate], , -1)

// The classic, works in every version
=INDEX(Products[Price], MATCH(A2, Products[SKU], 0))

// Row by region (G2), column by month (H1)
=XLOOKUP(G2, A2:A20, XLOOKUP(H1, B1:M1, B2:M20))

=SUMIFS(Sales[Amount], Sales[Region], "North",
        Sales[Date], ">=" & DATE(2026, 1, 1))
=COUNTIFS(Sales[Region], "North", Sales[Amount], ">100")
=AVERAGEIFS(Sales[Amount], Sales[Product], "Espresso")
=MAXIFS(Sales[Amount], Sales[Region], G2)

=FILTER(Sales, Sales[Region] = "North", "No rows")

// AND = multiply, OR = add
=FILTER(Sales,
    (Sales[Region] = "North") * (Sales[Amount] > 100))
=FILTER(Sales,
    (Sales[Region] = "North") + (Sales[Region] = "South"))

=SORT(UNIQUE(Sales[Customer]))
=SORTBY(Sales, Sales[Amount], -1)

=TAKE(SORTBY(Sales, Sales[Amount], -1), 10)
=CHOOSECOLS(Sales, 1, 3, 4)
=VSTACK(Sales2025, Sales2026)

=GROUPBY(Sales[Region], Sales[Amount], SUM)
=PIVOTBY(Sales[Region], Sales[Month], Sales[Amount], SUM)

=LET(total, SUM(Sales[Amount]),
     north, SUMIFS(Sales[Amount], Sales[Region], "North"),
     north / total)

// Save in Formulas > Name Manager as SafeDivide
=LAMBDA(x, y, IF(y = 0, 0, x / y))
=SafeDivide(A2, B2)

=TRIM(PROPER(A2))
=TEXTBEFORE(A2, "@")
=TEXTAFTER(A2, "@")
=TEXTSPLIT(A2, ",")
=TEXTJOIN(", ", TRUE, B2:B10)

// Last and first day of the month
=EOMONTH(A2, 0)
=EOMONTH(A2, -1) + 1

// Three months later, and working days between two dates
=EDATE(A2, 3)
=NETWORKDAYS(A2, B2)

// As text, e.g. 2026-10
=TEXT(A2, "yyyy-mm")

=IFS(A2 >= 50, "High", A2 >= 25, "Medium", TRUE, "Low")
=SWITCH(B2, "N", "North", "S", "South", "Other")
=IFERROR(A2 / B2, 0)
=IFNA(VLOOKUP(A2, Products, 3, FALSE), "Missing")