# XLOOKUP (Excel lesson 13 of 45)

> The modern lookup: any direction, exact by default, with a fallback.

Source: https://spreadsheetpractice.com/learn/xlookup · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Lookups"
- **Time:** about 9 minutes, 3 graded exercises
- **Functions:** `XLOOKUP`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=XLOOKUP(F2,A2:A9,B2:B9)`: ID → name

## How it works

`=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])`. You give it the column to search and the column to return as separate ranges, so it can look left as easily as right.

It matches exactly by default, and the fourth argument replaces #N/A with your own message. Inserting columns can't break it.

## Syntax

| Formula | What it does |
|---|---|
| `=XLOOKUP(F2,A2:A9,B2:B9)` | ID → name |
| `=XLOOKUP(F4,B2:B9,A2:A9)` | Name → ID (looks left) |
| `=XLOOKUP(F6,A2:A9,C2:C9,"Not found")` | With a fallback |

## Watch out for

- The lookup and return ranges must be the same height, or you get #VALUE!.
- XLOOKUP needs Excel 2021 or Microsoft 365.

## The exercises

Solve them in the live grid at [XLOOKUP](https://spreadsheetpractice.com/learn/xlookup). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **ID to name.** In G2, return the name for the employee ID in F2.
2. **Look left.** In G4, return the employee ID for the name in F4. VLOOKUP can't do this one.
3. **Handle missing IDs.** In G6, return the department for the ID in F6, or the text Not found if the ID doesn't exist.

Previous: [VLOOKUP](https://spreadsheetpractice.com/learn/vlookup) · Next: [INDEX & MATCH](https://spreadsheetpractice.com/learn/index-match)

---

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