XLOOKUP practice: connect your spreadsheet data

A lookup connects information using a shared identifier. A SKU can lead to a price; an employee name can lead to a rate. XLOOKUP separates the range you search from the range you return, making the relationship explicit.

Read the three core arguments

The inventory sheet has SKUs in A2:A7 and prices in E2:E7. To find the price for MN-03, use:

=XLOOKUP("MN-03",A2:A7,E2:E7)

The first argument is what you want to find. The second is where to look. The third contains the values you want back. XLOOKUP uses exact matching by default.

Handle a missing identifier

A missing match does not necessarily mean a formula is broken. The item may be new, misspelled, or absent from the source. Use the optional fourth argument when a clear fallback belongs in the report:

=XLOOKUP("XX-99",A2:A7,E2:E7,"Not found")

A useful fallback helps a person decide what to check next. Be careful with returning zero for every missing match: a real zero price and a missing record are different situations.

Look left, too

The return range can be to the left of the lookup range. To find a SKU by item name, search column B and return column A. Both ranges must still refer to the same rows and have matching dimensions.

Duplicates and versions

An exact lookup normally returns the first matching item. If identifiers repeat, decide whether the first match is enough or whether you actually need a conditional sum. XLOOKUP is available in Microsoft 365 and newer perpetual Excel versions such as Excel 2021; older versions may need INDEX/MATCH or VLOOKUP.

In this browser lab

The simulator supports the common match modes and forward or reverse linear search. It does not implement XLOOKUP’s binary search modes or spilled return arrays. Start with an exact-match price lookup, then try a missing SKU and a left lookup.

For the full function behavior, see Microsoft’s XLOOKUP reference.

Continue your adventure

Use the formula spellbook for syntax or explore the full quest map. You can practice in the browser and download workbooks without creating an account.