Find Missing Order Prices with Excel XLOOKUP

Before you start

• Use an Excel version with XLOOKUP, such as Microsoft 365, Excel 2021, or Excel 2024. Microsoft says XLOOKUP is unavailable in Excel 2016 and 2019.
• Open the downloaded workbook in Excel. Orders has six data rows in rows 2–7. Catalog has five unique SKU keys in A2:A6 and their numeric unit prices in C2:C6.
• Keep the supplied catalog unchanged for this exercise. Both sheets contain fictional data.

Swipe or scroll sideways to see every table column →

Look up the unit prices

1. On Orders, select D2. Enter:

   =XLOOKUP(B2,Catalog!$A$2:$A$6,Catalog!$C$2:$C$6,"Missing SKU")

B2 is the SKU to find. The first fixed Catalog range contains the keys; the second contains the aligned prices. "Missing SKU" is the fourth, if-not-found argument. Microsoft documents an exact match by default, which is what an identifier needs. D2 should show `$24.00`.

2. Fill D2 down through D7. Check that D5 says `Missing SKU`, because NO-999 is intentionally absent from the catalog. Do not replace that result with a made-up price or zero.

3. In E2, enter:

   =IF(D2="Missing SKU","Review SKU",C2*D2)

Fill E2 through E7. This IF formula marks the expected missing-key case and multiplies quantity by price for matched rows. Other unexpected Excel errors remain visible for investigation.

4. Compare all six rows with the answer check. The five numeric line totals are $48.00, $18.00, $50.00, $82.50, and $78.00. Add only those five numbers: $276.50. The fourth order, in worksheet row 5, is a review item rather than a priced order.

Common mistakes

• D2 returns `#NAME?`. Check whether your Excel version supports XLOOKUP and whether the function name is spelled correctly. Excel 2016/2019 do not support it.
• The first row works but later rows fail. Both catalog ranges must keep their dollar-sign anchors when D2 is filled down.
• A valid key appears missing. Inspect leading/trailing spaces and whether the Orders and Catalog values use the same exact SKU text. Do not change the data just to make the expected answer appear.
• Two catalog rows share one SKU. XLOOKUP's first match could hide an ambiguous price. Resolve duplicate keys in source data before trusting the result.
• Your Excel uses semicolons in formulas. Some locales use a different argument separator. Follow Excel's local formula prompt while keeping the four arguments in the same order; the shown formula uses US English comma separators.

FAQ

Why not return zero for an unknown SKU? Zero looks like a real free item. The explicit label keeps the order open until its source key or catalog entry is reviewed.

What happens when the catalog grows? These fixed practice ranges stop at row 6. Extend both the key and price ranges to cover new catalog rows, or redesign the source as a structured Excel Table for an ongoing workbook.

Does this work in Google Sheets? This lesson is for Excel. Google Sheets has its own XLOOKUP support and formula/localization behavior; do not assume this exact workbook is a verified Sheets tutorial.

Source and visual note

Primary references checked 9 October 2026: Microsoft XLOOKUP documentation and Microsoft IF documentation. The workbook contents and arithmetic were checked independently. The same fictional exercise was also run in English Excel for the web: D2:D7 returned 24, 18, 12.5, Missing SKU, 27.5, and 39; E2:E7 produced five numeric totals summing to $276.50 plus one Review SKU. Two genuine app captures are recorded for scenes 4 and 5 in the capture manifest; the companion video includes them. Other panels are illustrative diagrams. This check covers the fictional workbook in the tested account, not every Excel version or locale.