XLOOKUP essentials
Retrieve one matching record while keeping missing data visible.
By the end, you’ll be able to:
- Choose matching lookup and return ranges
- Use an explicit missing-result message
- Check uniqueness and data types before trusting a lookup
A lookup retrieves, rather than totals
The requested SKU in E2 is P200. Search the SKU column and return the price from the same row. The answer is 8. A lookup joins a selected key to an attribute; SUMIF instead summarizes every qualifying row.
Decide whether the SKU should be unique. XLOOKUP normally returns the first matching record, so duplicate product keys can hide an issue rather than produce an obvious error.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Price | Stock | Requested SKU | |
| 2 | P100 | 12 | 25 | P200 | |
| 3 | P200 | 8 | 0 | ||
| 4 | P300 | 20 | 9 | ||
| 5 | P400 | 5 | 40 |
Know the required and optional arguments
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])lookup_value is the key to find; lookup_array contains keys; return_array contains aligned results. if_not_found replaces a missing-match #N/A. match_mode controls exact (0, default), exact or next smaller (-1), exact or next larger (1), or wildcard (2). search_mode controls first-to-last (1, default), last-to-first (-1), or binary search (2 ascending, -2 descending).
This lesson uses exact matching and first-to-last search. Binary searches require correctly sorted lookup data and are not an ordinary SKU lookup default.
Worked example: price and stock
=XLOOKUP(E2,A2:A5,B2:B5,"Not found") returns 8. Change E2 to P300 and it returns 20. With P999 it returns Not found.
Use C2:C5 as the return range to retrieve stock instead. P200 returns numeric 0, which means a known product with no stock. That differs from an unknown SKU. Do not use zero as the missing-product message if it would blur this distinction.
Check matching assumptions
Lookup and return ranges must describe corresponding records. Keep the same start and end rows. Exact text matching normally ignores letter case, but leading or trailing spaces can still prevent a match. Numeric 100 and text "100" can also represent different keys.
Validate the key in the source, inspect duplicates, and check one returned value independently. If multiple order lines should contribute to an amount, a conditional sum is usually the appropriate tool instead of taking the first line.
Choose a formula recipients can use
XLOOKUP is available in Microsoft 365, Excel for the web and Excel 2021/2024, but not as a native function in Excel 2016 or 2019. If recipients use an older version, use VLOOKUP for an appropriate rightward lookup or INDEX with MATCH.
In the practice workbook, try a known SKU, an unknown SKU and a product with zero stock. The browser checks supported exact-lookup cases; it is a teaching interpreter rather than a full Excel workbook engine.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The bounded practice checker accepts equivalent supported formulas, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
XLOOKUP returns an aligned result for a key. Preserve missing-data meaning, check duplicates and confirm version compatibility.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Reviewed 2026-10-16.