# Last match, nearest value & wildcards (Excel lesson 17 of 45)

> XLOOKUP can search from the bottom, round up to the next size, and match part of a name.

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

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Lookups"
- **Time:** about 10 minutes, 3 graded exercises
- **Functions:** `XLOOKUP`, `match_mode`, `search_mode`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=XLOOKUP(G2,B2:B9,C2:C9,"none",0,-1)`: Latest match: search from the bottom

## How it works

XLOOKUP takes two optional settings at the end: `match_mode` and `search_mode`. They turn it from "find this exact value" into three more useful lookups.

**Latest entry.** A lookup returns the first match from the top. Set `search_mode` to `-1` and it starts at the bottom, so a purchase log gives you the most recent cost for an item.

**Nearest value.** `match_mode` `1` returns the exact match or the next larger value; `-1` the next smaller. That is how you pick the smallest box that fits, or the tax band an income falls into, without sorting the table first.

**Wildcards.** `match_mode` `2` treats `*` as "any text" and `?` as "one character", so `"*"&G2&"*"` finds a row containing what someone typed.

## Syntax

| Formula | What it does |
|---|---|
| `=XLOOKUP(G2,B2:B9,C2:C9,"none",0,-1)` | Latest match: search from the bottom |
| `=XLOOKUP(G2,A2:A6,B2:B6,"Too big",1)` | Exact or next larger |
| `=XLOOKUP(G2,A2:A6,B2:B6,"Too small",-1)` | Exact or next smaller |
| `=XLOOKUP("*"&G2&"*",A2:A6,B2:B6,"No match",2)` | Contains the typed text |

## Watch out for

- The arguments are in a fixed order: if_not_found, then match_mode, then search_mode. To set match_mode, give if_not_found a value (or leave it as an empty argument).
- Wildcard matching only works with match_mode 2. In the default mode, * is just a star.
- "Exact or next larger" can return something far from your value. Check the table covers the range you expect.

## The exercises

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

1. **Latest cost.** In G3, return the most recent unit cost for the item in G2. The log is in date order, newest at the bottom.
2. **Smallest box that fits.** In G3, return the name of the smallest box whose capacity is at least the litres needed in G2. In G4, return its price. If nothing is big enough, show Too big.
3. **Find by part of a name.** In G3, return the price of the first item whose description contains the word typed in G2. If there is no match, show No match.

Previous: [Lookups with several criteria](https://spreadsheetpractice.com/learn/multi-criteria-lookups) · Next: [LEFT, RIGHT, MID & LEN](https://spreadsheetpractice.com/learn/left-right-mid-len)

---

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