About this lesson
XLOOKUP: lookups with if-not-found and search from the bottom
XLOOKUP finds a value in one range and returns the matching value from another: =XLOOKUP(B13,B2:B11,D2:D11). It's the newer function that replaces VLOOKUP, HLOOKUP and most INDEX and MATCH, looks left, takes an if-not-found value, and with search mode -1 finds the last match: =XLOOKUP(B13,B2:B11,D2:D11,,0,-1). It needs Microsoft 365 or Excel 2021, and older versions show #NAME?.
Using Google Sheets? Do this lesson in Sheets.
=XLOOKUP(B13,B2:B11,C2:C11)What you'll practise
Look up a product's first and latest cost in a supplier price log, look left for the date it last changed, and handle a code that isn't there, all with XLOOKUP.
4 steps · 40 points · the cells outlined in gold below
- XLOOKUP
- if not found
- search from the bottom
- left lookup
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Effective from | SKU | Product | Unit cost ($) |
| 2 | 01/07/2025 | BG-1042 | Deck screws 500pk | $18.40 |
| 3 | 01/07/2025 | BG-2207 | Pine sleeper 2.4m | $22.95 |
| 4 | 15/08/2025 | BG-3318 | Exterior primer 4L | $41.60 |
| 5 | 01/09/2025 | BG-1042 | Deck screws 500pk | $19.15 |
| 6 | 06/10/2025 | BG-4471 | Garden hose 30m | $34.80 |
| 7 | 03/11/2025 | BG-2207 | Pine sleeper 2.4m | $24.10 |
| 8 | 12/01/2026 | BG-3318 | Exterior primer 4L | $43.25 |
| 9 | 02/02/2026 | BG-1042 | Deck screws 500pk | $19.85 |
| 10 | 16/03/2026 | BG-5590 | Wheelbarrow 100L | $87.50 |
| 11 | 01/04/2026 | BG-2207 | Pine sleeper 2.4m | $25.40 |
| 12 | ||||
| 13 | SKU | BG-2207 | ||
| 14 | Product | |||
| 15 | First cost ($) | |||
| 16 | Latest cost ($) | |||
| 17 | Last changed | |||
| 18 | ||||
| 19 | New SKU | BG-6012 | ||
| 20 | Product |