Formula Reference Hub
Excel Formulas: Complete Guide
Every essential Excel formula category — lookup, conditional math, logic, and text — with full guides, syntax references, and troubleshooting for when they break.
Lookup & Reference
XLOOKUPRecommendedThe modern replacement for VLOOKUP. Looks any direction, returns arrays, handles missing values natively.
Full guide →INDEX MATCHUniversalMore flexible than VLOOKUP. Works in all Excel versions, can look left, never breaks on column inserts.
Full guide →VLOOKUPThe classic lookup function. Limited to looking right, fragile to column changes — but still widely used.
Full guide →Conditional Math
SUMIFSEssentialSum cells matching multiple conditions — by region, date range, product, or any combination.
Full guide →COUNTIFSEssentialCount rows matching multiple conditions. Same syntax as SUMIFS — no separate sum range.
Full guide →SUMIF / COUNTIFSingle-condition versions. SUMIFS and COUNTIFS are more flexible — prefer those in new work.
Full guide →Logic & Error Handling
IF / IFSEssentialReturn different values based on a condition. IFS handles multiple branches without nesting.
Full guide →IFERROR / IFNASuppress or replace formula errors. IFNA catches only #N/A; IFERROR catches all error types.
Full guide →AND / OR / NOTCombine multiple logical tests inside IF, SUMIFS, or conditional formatting formulas.
Full guide →Text Functions
TRIM / CLEANData CleaningRemove extra spaces and non-printable characters — essential before any lookup or comparison.
Full guide →CONCAT / TEXTJOINCombine text from multiple cells. TEXTJOIN handles delimiters and can skip blank cells.
Full guide →LEFT / RIGHT / MIDExtract a fixed number of characters from the start, end, or middle of a text string.
Which Lookup Formula Should You Use?
| Situation | Use This |
|---|---|
| Excel 365 or 2021, any lookup task | XLOOKUP |
| Need compatibility with Excel 2019 or older | INDEX MATCH |
| Simple right-lookup in a legacy file | VLOOKUP (avoid for new work) |
| Need case-sensitive matching | INDEX(MATCH with EXACT) |
| Two-way lookup (row + column) | Nested XLOOKUP or INDEX(MATCH, MATCH) |
| Approximate match / tiered rates | XLOOKUP with match_mode -1, or INDEX MATCH with match_type 1 |
Formula Troubleshooting Guides
Formula Not Calculating
Manual mode, Text format, leading apostrophe — the 6 most common causes.
Read guide →
VLOOKUP Not Working
Data type mismatches, column index issues, approximate vs exact match.
Read guide →
SUMIF Returns 0
Text numbers, range size mismatch, wrong criteria format.
Read guide →
SUMIFS Returns 0
Same root causes as SUMIF — plus date format and wildcard problems.
Read guide →
COUNTIF Not Working
Missing quotes, data type issues, spaces in criteria.
Read guide →
Circular Reference
How to find and break a formula loop across any workbook.
Read guide →
Formula errors hiding in your workbook?
ExcelErrorFinder audits every formula across all sheets and flags errors with the cell address and a plain-English explanation.
Frequently Asked Questions
What are the most important Excel formulas to learn?
The formulas with the widest practical impact: XLOOKUP (or INDEX MATCH for older versions) for lookups; SUMIFS and COUNTIFS for conditional aggregation; IF and IFS for logic; IFERROR for error handling; TRIM and TEXT for text cleanup; and UNIQUE, FILTER, SORT for dynamic lists (Excel 365 only). Master these and you can handle 90% of everyday spreadsheet tasks.
What is the difference between XLOOKUP and INDEX MATCH?
XLOOKUP is simpler to write and read, has a built-in not-found argument, and can return multiple columns. INDEX MATCH is compatible with Excel 2019 and earlier, supports case-sensitive matching, and handles some advanced two-way lookup patterns more cleanly. Use XLOOKUP if you have Excel 365 or 2021; use INDEX MATCH if you need backwards compatibility.
Why is my formula not calculating in Excel?
The most common causes: (1) Calculation mode is set to Manual — press F9 to force recalculate or go to Formulas → Calculation Options → Automatic. (2) The cell is formatted as Text. (3) There is a leading apostrophe before the equals sign. See the full troubleshooting guide for all six causes.