How to Use XLOOKUP in Excel

What you will build

We will build a simple order sheet for a small office-supply business. A price list holds eight products, a small table holds quantity discounts, and five order lines use XLOOKUP to fill in everything except the SKU and quantity you type.

XLOOKUP is available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. In older versions it shows #NAME?; use INDEX and MATCH instead. If you already know the basics and only need strict ID matching, the XLOOKUP exact match guide goes deeper on that one topic.

Understand the XLOOKUP syntax

XLOOKUP needs only three arguments. The other three are optional and you can leave them out until you need them.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

A simple way to remember it: find this, look here, return that. Microsoft’s XLOOKUP function reference lists every option.

Step 1: Set up the price list and order table

A lookup is only as reliable as the table behind it. Spend a minute on the source data before you write any formulas.

1. Enter the headers SKU, Product, Category and Unit Price in A1:D1, then the eight products in rows 2 to 9.
2. Enter the discount tiers in F1:G5: Min Qty 1, 10, 25, 50 and Discount 0%, 5%, 10%, 15%. Sort the Min Qty column from smallest to largest.
3. In A12:F12, enter the order headers Order SKU, Product, Unit Price, Qty, Discount and Line Total.
4. Type the five order SKUs in A13:A17 (OX-202, OX-101, OX-203, OX-999, OX-302) and the quantities in D13:D17 (12, 60, 4, 10, 25). OX-999 is a deliberate typo so we can test a missing code.
5. Make sure every SKU appears only once in the price list.

Step 2: Write your first XLOOKUP

Start with the product name. Click B13 and enter:

=XLOOKUP(A13,$A$2:$A$9,$B$2:$B$9,"SKU not found")

Excel takes the SKU in A13 (OX-202), searches for it in A2:A9, finds it in row 6, and returns the value from the same row of B2:B9: USB-C Hub. There is no column number to count, and the result will not break if you insert a column into the price list later.

Step 3: Copy the formula down and handle missing SKUs

The dollar signs in $A$2:$A$9 lock the price list so it stays put when you copy the formula, while A13 moves down to A14, A15 and so on. Press F4 after selecting a range to add the dollar signs quickly (see how F4 toggles references).

1. In C13, return the unit price from column D:
   Copy

   =XLOOKUP(A13,$A$2:$A$9,$D$2:$D$9,0)

2. Select B13:C13 and drag the fill handle down to row 17, or select B13:C17 and press Ctrl+D.
3. Look at row 16. OX-999 is not in the price list, so B16 shows SKU not found and C16 shows $0.00 instead of #N/A.

Why two different “not found” values? The Product column shows a visible warning so the row is easy to spot. The Unit Price column returns 0 so the line total and order total still calculate instead of spreading an error through the sheet.

Step 4: Use approximate match for discount tiers

Discount tiers do not list every possible quantity. You want “the highest tier the quantity qualifies for”, and that is what match_mode -1 does: it finds an exact match or, if there is none, the next smaller value. In E13 enter:

=XLOOKUP(D13,$F$2:$F$5,$G$2:$G$5,0,-1)

Copy it down to E17. Row 14 orders 60 units. There is no 60 in the tier list, so XLOOKUP falls back to the next smaller threshold, 50, and returns 15%. Row 16 orders exactly 10, which is an exact match, so it gets 5%. Row 15 orders 4, which falls back to the 1-unit tier at 0%.

Now add the line total in F13 with =C13*D13*(1-E13), copy it down to F17, and total the order in F18 with =SUM(F13:F17).

Step 5: Look up to the left

Sometimes you know the product name but need the SKU, and the SKU column sits to the left of the name. VLOOKUP cannot do this on its own. XLOOKUP does not care about direction because the lookup array and return array are separate arguments.

1. Type Laptop Stand in G8.
2. In G9, enter:
   Copy

   =XLOOKUP(G8,$B$2:$B$9,$A$2:$A$9,"Not found")

3. The result is OX-203, taken from column A of the matching row.

Text lookups are not case-sensitive, so “laptop stand” also works. They do need the same spelling and no extra spaces. To let users pick a name instead of typing it, add a drop-down list to G8.

Step 6: Verify the results

A formula that returns something is not the same as a formula that returns the right thing. Check at least one row by hand before you rely on the sheet.

1. Row 13: OX-202 is the USB-C Hub at $34.50. 12 units qualify for the 10-unit tier (5%), so 12 × $34.50 × 95% = $393.30.
2. Row 14: 60 × $4.50 × 85% = $229.50.
3. Row 15: 4 × $27.75 × 100% = $111.00.
4. Row 16: OX-999 is not in the price list, so the line is $0.00 and flagged in column B.
5. Row 17: 25 × $22.00 × 90% = $495.00.
6. Total: F18 should show $1,228.80.

XLOOKUP vs VLOOKUP at a glance

If a key can match several rows and you need all of them, XLOOKUP will only return the first. Use FILTER for multiple results instead.

Common mistakes and quick fixes

Frequently asked questions

Final checklist

• The price list has one row per product and no duplicate SKUs.
• Lookup and return ranges are locked with $ or use an Excel Table.
• Lookup and return ranges cover the same rows.
• Missing SKUs show a clear message instead of #N/A.
• Discount tiers use match_mode -1 and include the lowest threshold.
• At least one line total has been checked by hand, and the order total matches.

Once this works, you can reuse the same pattern in an invoice tracker, an inventory reorder sheet, a payroll rate table or a commission schedule. The idea stays the same: find this, look here, return that, and then check the answer against the source.