The list below is ordered by how often you will reach for each one, not by difficulty. If you learn them in this order you will be useful long before you finish.
Lookups
This is the job Excel is most often asked to do: take a value in one sheet and pull the matching detail from another.
| Formula | What it does |
|---|---|
| XLOOKUP | The modern default. Searches a column, returns a value from another, and takes an if-not-found argument so you never see #N/A. |
| INDEX + MATCH | The older pairing. Still worth knowing, because it works in every version and can look leftwards, which VLOOKUP cannot. |
| VLOOKUP | Legacy, but still everywhere. Remember it counts columns from the lookup column, and breaks when someone inserts one. |
=XLOOKUP(A2, customers[id], customers[name], "Not found")
=INDEX(customers[name], MATCH(A2, customers[id], 0))MATCH and VLOOKUP default to an approximate match, which returns a nearby wrong answer instead of an error. That is how quiet mistakes get into reports.
Conditional maths
Four functions answer almost every "how much, but only for" question, and the plural forms take several conditions at once.
- SUMIFS — total where conditions hold. Note the order: sum range first, then each criteria pair.
- COUNTIFS — how many rows match.
- AVERAGEIFS — the mean of a subset.
- IF — a single branch. Nesting more than two is a sign you want IFS or a lookup table instead.
=SUMIFS(sales[amount], sales[region], "North", sales[month], ">="&DATE(2026,1,1))Cleaning text
Imported data arrives with trailing spaces, inconsistent case and joined-up fields. These five fix most of it without a single manual edit.
- TRIM — removes leading, trailing and repeated spaces. Run it before any lookup that is mysteriously failing.
- PROPER, UPPER, LOWER — normalise case so grouping works.
- TEXTSPLIT — break one column into several on a delimiter.
- TEXTJOIN — the reverse, with a separator and the option to skip blanks.
- SUBSTITUTE — strip currency symbols and thousands separators so a text column becomes numeric.
Handling errors on purpose
A sheet full of #N/A cannot be summed and does not survive being sent to anyone. Wrap the lookups that are allowed to miss, and leave the ones that are not.
=IFERROR(XLOOKUP(A2, ids, names), "Unknown")Be deliberate about it. IFERROR around a formula that should never fail hides the bug rather than handling it.
Key takeaways
- XLOOKUP first, INDEX and MATCH where you need portability or a leftward lookup.
- Always force an exact match.
- Run TRIM before blaming a lookup that returns nothing.
- Wrap only the lookups that are genuinely allowed to miss.
Turn this into a career, not a bookmark.
Data Science & Analytics with AI takes you from spreadsheets to dashboards a business runs on.
Explore the course