Most spreadsheet users learn SUM, AVERAGE, and maybe IF — then stop. That's like learning three chords on a guitar and wondering why your songs all sound the same. The functions below are the ones that actually show up in real-world work: looking up data, handling errors gracefully, summarizing conditionally, and making your spreadsheets resilient when data changes.
We'll go through each one with a practical example. These work in Microsoft Excel (desktop and 365) and most work in Google Sheets too. If you're also interested in making your spreadsheets more shareable, check our guide on designing spreadsheets others can actually use.
1. XLOOKUP — The Lookup Function You Actually Want
XLOOKUP replaced VLOOKUP and HLOOKUP, and it's better in every way. It searches a range for a value and returns a corresponding result — without the limitations that made VLOOKUP frustrating.
=XLOOKUP(search_value, lookup_range, return_range, "Not found")The fourth argument is what makes it elegant: instead of showing #N/A when a value isn't found, you can return a friendly message. XLOOKUP also searches left or right (VLOOKUP only searched right), handles column insertions gracefully, and supports approximate matching natively.
When to use it
Any time you need to pull a value from one table based on a matching key — names, prices, statuses, categories. This is the most-used function in professional spreadsheet work.
2. FILTER — Return Multiple Results, Not Just One
FILTER returns all rows that match a condition — not just the first one. Before FILTER, you'd need complex array formulas or pivot tables to do this. Now it's one function.
=FILTER(A2:D100, B2:B100="Completed", "No results")This returns every row where column B equals "Completed." The results spill into adjacent cells automatically — that's dynamic arrays at work, which we'll cover next.
3. Dynamic Arrays — One Formula, Many Results
Dynamic arrays are less a single function and more a capability that changed how Excel works. When you write a formula that returns multiple values, Excel "spills" them into adjacent cells automatically. No more dragging formulas down or using Ctrl+Shift+Enter.
Key dynamic array functions:
- SORT:
=SORT(A2:A100)— returns a sorted list - UNIQUE:
=UNIQUE(A2:A100)— returns distinct values only - SEQUENCE:
=SEQUENCE(10)— generates 1 through 10 - SORTBY:
=SORTBY(A2:A100, B2:B100, -1)— sort A by B, descending
These compose beautifully: =SORT(UNIQUE(FILTER(B2:B100, A2:A100="Active"))) gives you a sorted list of unique values where status is Active. One formula, real work done.
4. IFERROR — Graceful Error Handling
When a formula can't compute, Excel shows an error code: #N/A, #DIV/0!, #VALUE!. These are ugly and break downstream calculations. IFERROR lets you catch errors and return something useful instead.
=IFERROR(A2/B2, "Cannot divide by zero")Wrap any formula that might fail in IFERROR. It won't hide bugs — it makes your spreadsheets robust when data is incomplete, which in real life is always.
5. SUMIFS — Conditional Summing (Done Right)
SUMIF exists, but SUMIFS is strictly better — it supports multiple conditions and is easier to read. Sum values where criteria match across multiple columns.
=SUMIFS(D2:D100, A2:A100, "Q1", B2:B100, "Enterprise")This sums column D where A equals "Q1" AND B equals "Enterprise." The pattern extends to COUNTIFS, AVERAGEIFS, MAXIFS, and MINIFS — same syntax, different math.
6. INDEX + MATCH — The Old Reliable
Before XLOOKUP, INDEX and MATCH were the power-user alternative to VLOOKUP. They still matter because they work in older Excel versions and Google Sheets.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))It's more verbose than XLOOKUP but does the same thing: find a value and return a corresponding result. If you're building spreadsheets that others will open in older software, INDEX+MATCH remains the safe choice.
7. TEXTJOIN — Combine Text Intelligently
TEXTJOIN concatenates text with a delimiter and can skip empty cells — something the old CONCATENATE couldn't do.
=TEXTJOIN(", ", TRUE, A2:A10)This joins A2 through A10 with commas, skipping blanks. Perfect for building email lists, creating comma-separated summaries, or assembling addresses from component parts.
8. EOMONTH — Date Arithmetic Made Simple
Working with end-of-month dates is painful without EOMONTH. It returns the last day of a month, offset by however many months you specify.
=EOMONTH(TODAY(), 0) 'Last day of current month
=EOMONTH(TODAY(), -1) 'Last day of previous month
=EOMONTH(TODAY(), 3) 'Last day of month, 3 months aheadPair with EDATE (which returns the same day, offset by months) for complete date arithmetic. These eliminate the hacky date math that makes spreadsheets fragile.
9. LET — Define Variables Inside Formulas
LET lets you name intermediate calculations within a single formula, making complex formulas readable and faster (Excel computes each variable once).
=LET(
revenue, A2*100,
cost, B2*60,
margin, revenue - cost,
margin / revenue
)This calculates revenue, cost, and margin — then returns margin ratio. Without LET, you'd write (A2*100-B2*60)/(A2*100), which is unreadable and computes A2*100 twice. LET is a readability and performance win.
10. LAMBDA — Create Your Own Functions
LAMBDA is the most powerful function on this list. It lets you define custom, reusable functions — without macros or VBA.
=LAMBDA(price, qty, discount, price * qty * (1 - discount))Save this as a named function (e.g., NetTotal) and use it anywhere: =NetTotal(100, 5, 0.1) returns 450. LAMBDA turns Excel into a mini programming environment. It's advanced, but if you find yourself writing the same complex formula repeatedly, LAMBDA is the answer.
Putting It Together
The real power comes from combining these functions. Here's a pattern we use constantly:
=LET(
data, FILTER(A2:E1000, C2:C1000="Active"),
SORT(UNIQUE(INDEX(data, , 1)))
)This filters to active records, extracts the first column, gets unique values, and sorts them — all in one readable formula. Five years ago, this required a pivot table and manual steps. Now it's a single cell.
Learn These, Skip the Rest (For Now)
Excel has over 400 functions. You don't need most of them. Master these 10 — understand when to use each, how they compose, and where they fail — and you'll be in the top 5% of spreadsheet users. When you're ready to go further, explore automating tasks with macros to eliminate repetitive work entirely.
