How does the SQL version of XLOOKUP work?

The join on p.id = i.product_id is the lookup_array and lookup_value pair. COALESCE supplies the 'if not found' value that XLOOKUP takes as its fourth argument. Unlike VLOOKUP, a join can look up any column and in any direction, because columns are named rather than counted.

Why can SQL give a different result from XLOOKUP?

XLOOKUP can search from the last match with a search-mode option; SQL needs ROW_NUMBER ordering to choose the last match.

XLOOKUP in SQL

XLOOKUP searches one column for a value and returns the matching item from another column, with a default for no match; SQL uses a LEFT JOIN with COALESCE.

The Excel version

=XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

The SQL equivalent

This example assumes: order_items(id bigint, product_id bigint, quantity numeric), products(id bigint, name text, category text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT i.id,
       i.quantity,
       COALESCE(p.category, 'Not found') AS category
FROM order_items i
LEFT JOIN products p ON p.id = i.product_id
ORDER BY i.id;

How it works

The join on p.id = i.product_id is the lookup_array and lookup_value pair.
COALESCE supplies the 'if not found' value that XLOOKUP takes as its fourth argument.
Unlike VLOOKUP, a join can look up any column and in any direction, because columns are named rather than counted.

Where the results differ from Excel

XLOOKUP can search from the last match with a search-mode option; SQL needs ROW_NUMBER ordering to choose the last match.
Duplicate keys in the lookup table multiply rows in SQL, whereas XLOOKUP just returns one.
Approximate and wildcard match modes are not joins; use a range join or LIKE for those.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Show each order line with its product category, or 'Not found' if the product is missing.
How many order lines have no matching product?