# 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.

Source: https://spreadsheetpractice.com/functions/category/lookup · Updated October 29, 2026

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.

## Which one to use

| You want to | Use | Why |
|---|---|---|
| Return a value from a table by ID or name | [XLOOKUP](https://spreadsheetpractice.com/functions/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](https://spreadsheetpractice.com/functions/index) | Pair INDEX with MATCH for a lookup that works in every version. |
| Find the position of a value in a list | [MATCH](https://spreadsheetpractice.com/functions/match) | Returns the row or column number, which INDEX then uses. |
| Find the last match instead of the first | [XMATCH](https://spreadsheetpractice.com/functions/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](https://spreadsheetpractice.com/functions/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](https://spreadsheetpractice.com/functions/hlookup) | The sideways VLOOKUP, for tables with labels across the top. |

## All 13 lookup functions

| Function | What it does | Available in |
|---|---|---|
| [COLUMN](https://spreadsheetpractice.com/functions/column) | 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. | Every current version |
| [COLUMNS](https://spreadsheetpractice.com/functions/columns) | COLUMNS counts how many columns a range or array has, whether or not the cells contain anything. | Every current version |
| [HLOOKUP](https://spreadsheetpractice.com/functions/hlookup) | 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. | Every current version |
| [INDEX](https://spreadsheetpractice.com/functions/index) | INDEX returns the value at a given row and column of a range: INDEX(A2:C10,4,2) is the fourth row, second column. | Every current version |
| [INDIRECT](https://spreadsheetpractice.com/functions/indirect) | INDIRECT turns text into a reference: "B3" becomes the cell B3, and "A2:A"&D1 becomes a range whose last row comes from a cell. | Every current version |
| [LOOKUP](https://spreadsheetpractice.com/functions/lookup) | LOOKUP finds a value in a sorted row or column and returns the value in the same position of another row or column. | Every current version |
| [MATCH](https://spreadsheetpractice.com/functions/match) | MATCH returns the position of a value in a row or column: 3 means the third cell. | Every current version |
| [OFFSET](https://spreadsheetpractice.com/functions/offset) | OFFSET returns a reference that starts a given number of rows and columns away from a starting cell, optionally resized to a new height and width. | Every current version |
| [ROW](https://spreadsheetpractice.com/functions/row) | 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. | Every current version |
| [ROWS](https://spreadsheetpractice.com/functions/rows) | ROWS counts how many rows a range or array has, whether or not the cells contain anything. | Every current version |
| [VLOOKUP](https://spreadsheetpractice.com/functions/vlookup) | VLOOKUP looks for a value in the first column of a table and returns a value from another column in the same row. | Every current version |
| [XLOOKUP](https://spreadsheetpractice.com/functions/xlookup) | XLOOKUP searches one column or row for a value and returns the matching item from another. | Excel 2021, 2024 and Microsoft 365 |
| [XMATCH](https://spreadsheetpractice.com/functions/xmatch) | 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. | Excel 2021, 2024 and Microsoft 365 |

## Comparisons

- [VLOOKUP vs XLOOKUP](https://spreadsheetpractice.com/functions/compare/vlookup-vs-xlookup): Use XLOOKUP if everyone who opens the file has Excel 2021, 2024 or Microsoft 365.
- [XLOOKUP vs INDEX and MATCH](https://spreadsheetpractice.com/functions/compare/xlookup-vs-index-match): They 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.

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
