VLOOKUP
The classic lookup — match on the leftmost column, return from any column to the right.
InteractiveIndex · 18 interactive lessons
Learn Excel & Google Sheets formulas — interactively.
Every Excel formula on this site comes with a small, editable sandbox. Change the arguments, watch the result recompute, read the comparisons — then reach for the right function because you already know what each piece does.
The 18 functions below cover the overwhelming majority of real-world spreadsheet work. Conditional aggregates (SUMIFS, COUNTIFS, AVERAGEIFS) handle dashboards and reports. Lookups (VLOOKUP, XLOOKUP, INDEX+MATCH) handle joins and references. Text helpers (TEXTJOIN, LEFT, SUBSTITUTE) clean up imports. Dynamic arrays (UNIQUE, FILTER) power live views that recompute as data changes. Everything works in both Microsoft Excel and Google Sheets — where behaviour differs, each page calls out exactly how. For official specifications see the Microsoft Excel function reference and the Google Sheets function list.
Start here
Essential Excel formulas to learn first
Five functions cover ~80% of everyday spreadsheet work. If you’re new to formulas or onboarding someone who is, this is the order that gives the best payoff per hour invested.
- IF — conditional logic. Every other formula eventually nests inside an IF.
- VLOOKUP — look up values across tables. The classic that’s in every legacy spreadsheet file.
- SUMIFS — conditional aggregation for dashboards. Single most used formula in finance and ops sheets.
- INDEX + MATCH — flexible two-way lookups. What you reach for when VLOOKUP isn’t enough.
- XLOOKUP — modern replacement for VLOOKUP. Cleaner, handles left lookups, has a built-in fallback.
Excel lookup cheat sheet
Excel VLOOKUP vs XLOOKUP vs INDEX + MATCH
The lookup family is the most-asked Excel decision. Pick based on direction, Excel version, and how resilient the formula needs to be to column changes.
| Feature | VLOOKUP | XLOOKUP | INDEX + MATCH |
|---|---|---|---|
| Lookup direction | Right only | Both directions | Both directions, including 2D |
| Column reference | Integer index | Range | Range |
| Built-in if_not_found | No — wrap with IFERROR | Yes | No — wrap with IFNA |
| Survives column insertion | No | Yes | Yes |
| Available in | Every Excel version | Excel 365 / 2021+, Sheets | Every Excel version |
| Google Sheets | Yes | Yes | Yes |
Quick rule: on modern Excel or Google Sheets, reach for XLOOKUP first. INDEX + MATCH still wins for 2D lookups (row × column) and for older Excel versions where XLOOKUP isn’t available. VLOOKUP survives because every legacy file already uses it — learn it to read existing sheets, prefer the alternatives for new work.
Excel lookup & reference formulas
Excel lookup & reference formulas
Pull values out of a table by matching on a key — the backbone of every join, report, and dashboard. Every spreadsheet workflow eventually needs one of these. The three here cover the evolution of lookups: VLOOKUP is in every Excel file ever written, INDEX + MATCH is the flexible power tool, XLOOKUP is the modern single-function replacement.
Excel conditional logic formulas
Excel conditional logic formulas
Decide what to return based on a boolean test, or guard a formula against errors before they propagate. IF handles the simple branch; IFERROR wraps anything that might fail. Together they handle the vast majority of defensive and decision-making logic in spreadsheets.
Excel conditional aggregate formulas
Excel conditional aggregate formulas
Sum, count, or average rows that match one or more criteria — the workhorses of dashboards and reports when you need a summary without building a pivot table. The plural -IFS versions handle multiple conditions AND'd together; the older single-criterion variants stay around for compatibility with older Excel files.
SUMIF
Add up numbers that match a single condition.
InteractiveSUMIFS
SUMIF with up to 127 conditions. Watch the argument-order flip.
InteractiveCOUNTIF
Count cells in a range that meet a single condition.
InteractiveCOUNTIFS
Multi-condition counting — same order as COUNTIF, no surprises.
InteractiveAVERAGEIFS
Conditional averaging. Returns #DIV/0! on empty matches (unlike SUMIFS).
InteractiveExcel text manipulation formulas
Excel text manipulation formulas
Extract substrings, join with delimiters, replace by value or position. Text handling is half of real-world spreadsheet work — cleaning up CSV imports, building IDs from pieces, stripping delimiters, stitching together report-ready strings. These three cover extraction, replacement, and joining.
Excel math & statistics formulas
Excel math & statistics formulas
Round numbers to specific precisions, or pull the largest value out of a column. ROUND is how you keep financial totals consistent to the penny; MAX is the quiet workhorse behind top-N dashboards and tie-breaking. Both have edge cases (negative digits, text coercion) worth knowing before you rely on them in production sheets.
Excel date & time formulas
Excel date & time formulas
Build and manipulate date values. Excel's date arithmetic has more tricks than most people realise — the overflow rollover alone handles half of what EDATE and EOMONTH are typically used for. DATE builds explicit values from year/month/day integers; other date functions in the library follow once the foundation is solid.
Excel dynamic array formulas
Excel dynamic array formulas
Modern Excel (365 / 2021+) and Google Sheets. One formula in one cell, many output cells — no Ctrl+Shift+Enter required. UNIQUE dedupes; FILTER keeps rows that pass a boolean test. Combined with SORT (and each other), they replace what used to require pivot tables, helper columns, and array-formula incantations.
Marginalia
Excel formulas: frequently asked questions
1Are Excel and Google Sheets formulas interchangeable?▸
Mostly, yes. The core set — VLOOKUP, IF, SUMIFS, INDEX/MATCH, TEXTJOIN, UNIQUE, FILTER — behaves identically in both products. Differences are at the edges: XLOOKUP arrived in Excel 365 and Google Sheets around the same time; dynamic arrays are native to both; a handful of functions (XLOOKUP’s match_mode, Sheets-only ARRAYFORMULA) aren’t fully reciprocal.
2Which Excel formula should I learn first?▸
IF, VLOOKUP, and SUMIFS cover 80% of real work. IF teaches boolean logic; VLOOKUP teaches lookup tables; SUMIFS teaches multi-criteria aggregation. Once those are second nature, INDEX+MATCH and XLOOKUP are natural next steps, along with IFERROR for error handling.
3What’s the difference between VLOOKUP and XLOOKUP?▸
XLOOKUP can look in either direction (VLOOKUP only goes left-to-right), takes a built-in if_not_found argument (VLOOKUP needs IFERROR), and references columns by range (VLOOKUP uses integer indices that break when columns are inserted). For new work in Excel 365 or Google Sheets, reach for XLOOKUP first.
4Do I need Excel 365 to use the formulas here?▸
Most work in every version. XLOOKUP, UNIQUE, FILTER, SORT, and TEXTJOIN require Excel 2019 or 365. Google Sheets has had all of these for years. VLOOKUP, IF, SUMIFS, INDEX+MATCH, ROUND, DATE, LEFT, and SUBSTITUTE work everywhere including Excel 2010.
5Why do Excel formulas behave differently in Google Sheets sometimes?▸
Mostly because of the dynamic array engine. Google Sheets evaluates many formulas as arrays by default, while Excel needs you to either use Ctrl+Shift+Enter (legacy) or be on Excel 365 to get the same behaviour. Specific function quirks exist too — IMPORTRANGE is Sheets-only, ARRAYFORMULA has no direct Excel equivalent, and date serial numbers use slightly different epoch bases.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.