XLOOKUP practice exercises

XLOOKUP searches one range and returns the matching item from another. Unlike VLOOKUP it can look in any direction, uses an exact match by default and lets you choose what to show when nothing is found.

Syntax

XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
search_key
The value to find.
lookup_range
The single column (or row) to search.
result_range
The column to return from. It must be the same size as lookup_range.
missing_value
Optional. What to return instead of #N/A when nothing matches.

The practice data

A product list with SKU, name, price and stock. F2 holds a SKU to look up. Every exercise below uses this sheet, and your answer goes in H2.

ABCDEF
1SKUProductPriceStockSKU to find
2101Widget540103
3102Gadget2015
4103Gizmo500
5104Doohickey128
6105Sprocket3120

Worked example

=XLOOKUP("Gadget",B2:B6,C2:C6)

Searches the Product column for "Gadget" and returns the matching Price. Result:

20

Your turn: 3 exercises

Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.

1 Return the price of "Sprocket".

H2

2 Return the SKU of "Gizmo". The SKU column is to the left of the product names.

H2

3 Return the stock of SKU 999, showing Not found if it doesn't exist.

H2

Master XLOOKUP in the full course

Lesson 11 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.

Start free See pricing

Common XLOOKUP mistakes

FAQ

Is XLOOKUP available in Google Sheets?

Yes. Google Sheets added XLOOKUP in 2022 and it works the same way as in Excel 365.

Can XLOOKUP return several columns at once?

Yes. If result_range is several columns wide, the whole matching row spills to the right.

Keep practicing