Excel lookup functions
Lookup functions find a value in one place and bring back related data from another: a price for a SKU, a name for an ID, a rate for a tier. If you only learn one, learn XLOOKUP. If you share files with people on older versions, learn INDEX and MATCH too.
- Functions
- 10
- With a graded exercise
- All 10
- Start with
XLOOKUP
Which one should I use?
| If you need to… | Use | Why |
|---|---|---|
| Return a value from a table by ID or name | XLOOKUP | Exact match by default, looks left or right, and has a fallback for missing values. |
| Do the same in Excel 2019 or earlier | INDEX | Pair INDEX with MATCH for a lookup that works in every version. |
| Find the position of a value in a list | MATCH | Returns the row or column number, which INDEX then uses. |
| Find the last match instead of the first | XMATCH | Search from the bottom with search_mode -1 to get the latest entry in a log. |
| Assign a rate from a tier table | VLOOKUP | Approximate match on a sorted first column returns the right tier for any amount. |
| Look up a value in a row instead of a column | HLOOKUP | The sideways VLOOKUP, for tables with labels across the top. |
Try XLOOKUP live
The most useful lookup function, on a sample sheet you can edit. Change a value and watch the results update.
F2=XLOOKUP(E2,A2:A5,C2:C5) $54 Price for the SKU typed in E2F3=XLOOKUP("Monitor arm",B2:B5,A2:A5) B-210 Looks left: the SKU for a product nameF4=XLOOKUP("Z-999",A2:A5,C2:C5,"Not found") Not found A missing SKU returns the fallback text instead of #N/A
All 10 lookup functions
| Function | What it does | Practice exercise |
|---|---|---|
COLUMN function | COLUMN returns the column number of a cell: COLUMN(D1) is 4, and COLUMN() with nothing inside returns the column of the formula's own cell. | One formula, three months |
COLUMNS function | COLUMNS counts how many columns a range or array has, whether or not the cells contain anything. | Average over all months |
HLOOKUP function | HLOOKUP is VLOOKUP turned sideways: it searches the top row of a table for a value and returns a value from a row below it, in the same column. | Sign-ups for a month |
INDEX function | INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column. | Two-way lookup |
MATCH function | MATCH returns the position of a value in a row or column: 3 means the third cell. | Find the position |
ROW function | ROW returns the row number of a cell: ROW(C7) is 7, and ROW() with nothing inside returns the row of the cell the formula sits in. | Running average |
ROWS function | ROWS counts how many rows a range or array has, whether or not the cells contain anything. | Share of stores at target |
VLOOKUP function | VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. | Look up product names |
XLOOKUP functionExcel 2021, 2024 and Microsoft 365 | XLOOKUP searches one column or row for a value and returns the matching item from another. | ID to name |
XMATCH functionExcel 2021, 2024 and Microsoft 365 | XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data. | Latest price paid |
Head-to-head
- VLOOKUP vs XLOOKUPUse XLOOKUP if everyone who opens the file has Excel 2021, 2024 or Microsoft 365.
- XLOOKUP vs INDEX and MATCHThey do the same job. XLOOKUP is one function with readable arguments; INDEX and MATCH is a two-function pattern that works in every version of Excel.
Learn them in order
These functions are taught step by step in the Lookups track. Each lesson ends with graded exercises on real-looking data.
- VLOOKUP9 min · Pro
- XLOOKUP9 min · Pro
- INDEX & MATCH10 min · Pro
- Tiered lookups & IFERROR9 min · Pro
- RANK, LARGE & SMALL9 min · Pro
- Capstone: analyst interview test12 min · Pro