XLOOKUP with a default value

How it works, step by step

1. 01B2 holds the SKU on this order.
2. 02XLOOKUP searches Products!$A$2:$A$500 for an exact match. Exact match is the default, so no extra argument is needed.
3. 03When it finds the SKU, it returns the value from the same row of Products!$C$2:$C$500, the Unit price.
4. 04The fourth argument, 0, is the if_not_found value. A SKU missing from Products returns 0 instead of #N/A.

The same logic in four tools

=XLOOKUP(B2,Products!$A$2:$A$500,Products!$C$2:$C$500,0)

=XLOOKUP(B2,Products!$A$2:$A$500,Products!$C$2:$C$500,0)

SELECT o.order_id, o.sku, COALESCE(p.unit_price, 0) AS unit_price
FROM orders o
LEFT JOIN products p ON p.sku = o.sku;

import pandas as pd

result = orders.merge(
    products[["sku", "unit_price"]],
    on="sku",
    how="left",
)
result["unit_price"] = result["unit_price"].fillna(0)

Mistakes that break it

• !XLOOKUP needs Excel 365 or Excel 2021 or later. Excel 2019 and older show #NAME?, so use IFNA around INDEX/MATCH there.
• !The lookup range and the return range must be the same height. Mismatched sizes return #VALUE!.
• !A default of 0 makes missing prices look like free items in any total. Use a text flag such as "Missing" if someone will review the column by eye.
• !In SQL, COALESCE also replaces prices that exist in products but are NULL, so a NULL price and a missing SKU look the same. Test p.sku IS NULL to tell them apart.

Common questions

How do I make XLOOKUP return blank instead of #N/A?

Pass "" as the fourth argument: =XLOOKUP(B2,Products!$A$2:$A$500,Products!$C$2:$C$500,""). The cell looks empty, though it holds an empty text string.

Does Google Sheets have XLOOKUP?

Yes. Google Sheets added XLOOKUP in 2022 with the same arguments as Excel, including if_not_found, match_mode, and search_mode.

What is the difference between XLOOKUP and VLOOKUP?

XLOOKUP takes separate lookup and return ranges, defaults to exact match, can look left, and has a built-in not-found value. VLOOKUP needs a column number, defaults to approximate match, and only returns columns to the right.