Excel
The Excel formula patterns worth knowing
Most spreadsheet requirements reduce to about eight formula patterns. Learn these and generated formulas become easy to verify.
Updated September 20, 2026
Lookups
XLOOKUP takes the not-found value directly, which removes the IFERROR wrapper that hides real problems. Use INDEX/MATCH when you must support older versions.
=XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$C:$C, 0)
=INDEX(Sheet2!$C:$C, MATCH(A2, Sheet2!$A:$A, 0))Conditional aggregation
=SUMIFS($D:$D, $B:$B, $F$2, $A:$A, ">="&DATE(YEAR(TODAY()),1,1))
=COUNTIFS($C:$C, ">"&AVERAGE($C:$C))Text and dates
=TEXTAFTER(A2, "@")
=EOMONTH(A2, 0)
=TEXTSPLIT(A2, ",")Common pitfalls
| Symptom | Usual cause |
|---|---|
| #N/A on a lookup that should match | Numbers stored as text, or trailing spaces |
| Sum is lower than expected | Numeric column contains text values |
| Formula breaks after inserting a row | Hard-coded ranges instead of full columns or tables |
| Wrong result after sharing | Locale decimal and list separators differ |
Frequently asked questions
Why does my SUMIFS return zero?
Most often the criteria range and sum range are different sizes, or the values you are matching are text that looks numeric. Check with ISNUMBER on a sample cell.
Related reading
Analyze your data with AI
Open the GridMind workspace, bring in a CSV, Excel file or your Power BI model, and let AI build the formulas, charts and dashboards for you.