VLOOKUP and XLOOKUP Versus SQL JOIN With Worked Examples

A lookup is the most common formula in a business workbook: find this key in that table and return a column. SQL has had the same operation for fifty years under the name join, and translating one into the other is mostly mechanical, with three places where it is not. This post works through VLOOKUP and XLOOKUP on a small example, then the traps that change the answer. It is a spoke of translating Excel formulas into SQL.

The data

WITH orders(order_no, customer_id, amount) AS (
  VALUES (1, 10, 120.00), (2, 10, 80.00), (3, 11, 45.50), (4, 12, 200.00), (5, 99, 15.00)
),
customers(id, name, tier) AS (
  VALUES (10, 'Acme', 'gold'), (11, 'Globex', 'silver'), (12, 'Initech', NULL)
)

In Excel the orders are on one sheet with the customer id in column B, and the customers are on another with the id in column G and the name in H. Order 5 refers to customer 99, who is not in the table.

Exact match: VLOOKUP becomes LEFT JOIN

=VLOOKUP(B2, Customers!G:I, 2, FALSE)
=XLOOKUP(B2, Customers!G:G, Customers!H:H, "unknown")

Both return the name for the id in B2. The SQL:

SELECT o.order_no, o.customer_id, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_no;

 order_no | customer_id |  name
----------+-------------+---------
        1 |          10 | Acme
        2 |          10 | Acme
        3 |          11 | Globex
        4 |          12 | Initech
        5 |          99 |

The last row shows the not-found case. VLOOKUP would display #N/A; the join returns NULL. XLOOKUP's fourth argument supplies a default, which is COALESCE(c.name, 'unknown'). Use LEFT JOIN when the orders are the rows you care about and the customer is extra information. An INNER JOIN would remove order 5 from the result, which is a different answer, and the choice should be deliberate. In the spreadsheet the row is always there, because the formula sits in the order's row.

The column index in VLOOKUP (2) is a position in the lookup range, which breaks when someone inserts a column. In SQL you name the column, which is the main reason a translation is sturdier than the original. The same lookup written with two functions has its own page: INDEX and MATCH versus a join on a key.

The first match versus every match

If the key repeats in the lookup table, VLOOKUP and XLOOKUP stop at the first one. A join returns them all. Suppose the customers table has a second row for Acme:

customers(id, name, tier) AS (
  VALUES (10, 'Acme', 'gold'), (10, 'Acme Ltd', 'gold'), (11, 'Globex', 'silver'), (12, 'Initech', NULL)
)

The join now returns two rows for each of orders 1 and 2, and any total over the result double counts them. The spreadsheet gave Acme once. This is the most common cause of totals that differ after a translation. Three defenses:

1. Check uniqueness before joining. SELECT id, count(*) FROM customers GROUP BY id HAVING count(*) > 1; lists the offenders. If there are any, decide which row is the right one.

2. Enforce it. A primary key or unique constraint on customers.id makes the problem impossible in the future.

3. Pick the first on purpose. To reproduce VLOOKUP's behavior on a table with duplicates, choose one row per key by an explicit rule. In PostgreSQL:

SELECT o.order_no, c.name
FROM orders o
LEFT JOIN (SELECT DISTINCT ON (id) id, name FROM customers ORDER BY id, name) c
       ON c.id = o.customer_id;

DISTINCT ON (id) keeps the first row per id in the stated order. Other engines use ROW_NUMBER() OVER (PARTITION BY id ORDER BY name) = 1 in a subquery. Note that "first" in a spreadsheet means first in sheet order, which SQL tables do not have; give the rule an explicit order, such as a date or a sequence column. If you were relying on sheet order, you were relying on an accident.

Approximate match: ranges and bands

=VLOOKUP(D2, Bands!A:B, 2, TRUE), or with the last argument omitted, looks up the largest value in a sorted first column that does not exceed the key. It is the formula behind tax tables, discount tiers and grades. A band table:

bands(floor, rate) AS (VALUES (0, 0.00), (100, 0.05), (500, 0.10))

For an order of 120.00 the answer is 0.05, because 100 is the largest floor not above 120. In SQL:

SELECT o.order_no, o.amount, b.rate
FROM orders o
LEFT JOIN LATERAL (
  SELECT rate FROM bands WHERE floor <= o.amount ORDER BY floor DESC LIMIT 1
) b ON true
ORDER BY o.order_no;

 order_no | amount | rate
----------+--------+------
        1 | 120.00 | 0.05
        2 |  80.00 | 0.00
        3 |  45.50 | 0.00
        4 | 200.00 | 0.05
        5 |  15.00 | 0.00

LATERAL lets the subquery refer to the current order row. It finds the matching band and takes the highest floor that qualifies. Engines without LATERAL use a window: join every band whose floor is not above the amount, number them from the highest floor with ROW_NUMBER, and keep the first. Another approach is to store both ends of each band (floor and ceiling) and join on amount >= floor AND amount < ceiling, which is explicit, has no ordering requirement and is easier to validate: check that the bands cover the range without gaps or overlaps. A spreadsheet's approximate match silently gives wrong answers when the table is not sorted, and SQL does not care about the order of the table.

XLOOKUP features

XLOOKUP adds several options, each with a SQL counterpart:

The last row is a trap: Excel's lookups ignore case, while PostgreSQL's comparisons do not. A key ACME in one table and Acme in the other matches in Excel and not in SQL. Normalize both sides, or use a case-insensitive collation, and trim whitespace, because a trailing space makes keys differ in SQL and is invisible in a sheet.

Filters and the outer join

A common mistake in the translation is to add a filter on the looked-up table in the WHERE clause:

SELECT o.order_no, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.tier = 'gold';

This returns only orders 1 and 2. The WHERE condition is false for rows where c.tier is NULL, so the unmatched rows disappear and the left join behaves as an inner join. If you want all orders, with the name only for gold customers, put the condition in the join:

LEFT JOIN customers c ON c.id = o.customer_id AND c.tier = 'gold'

Order 3 now appears with a NULL name because Globex is silver. A filter on the main table (o) is safe in WHERE; a filter on the optional table belongs in ON.

Types and keys

Lookups in a workbook succeed or fail on what the cells look like. The key 10 in one sheet may be a number and "10" in the other a text, in which case Excel's exact match fails and shows #N/A, a bug people fix by adding VALUE() or TEXT(). In SQL, joining an integer column to a text column raises an error in some engines or compares after an implicit cast in others. Make the key types agree explicitly, ideally by loading both as the same type, and keep identifiers as text if they have leading zeros (keeping leading zeros in Excel and when importing).

Also look for keys with stray spaces and non-breaking spaces, which are the usual reason a lookup that "should" match does not. Normalize on load.

Check the translation

Compare the join's output with the workbook's cached lookup results, row by row, and see COUNTIF versus COUNT with a filter in SQL for counting the unmatched rows, (translating Excel formulas into SQL describes the method). Count the rows: the join should return exactly as many rows as the orders table, no more (duplicates in the lookup) and no fewer (an inner join by accident). Count the nulls in the looked-up column and compare with the number of #N/A cells in the workbook. A mismatch in either count localizes the problem.

When a lookup formula should stay a lookup

The table is tiny and fixed. A lookup into a ten-row list of codes inside a small workbook is fine as it is.

The lookup is part of an interactive model. If the user changes the key and watches the result, the formula is the interface.

The workbook does not leave the spreadsheet. If nothing else consumes the numbers, translating adds work and no value.

The lookup uses a feature with no clean equivalent. INDIRECT to choose a sheet by name builds the lookup range from text; the SQL equivalent is a parameter or a union of tables, and the design needs rethinking first.

Excel describes the functions in its pages on VLOOKUP and XLOOKUP, and PostgreSQL documents joins in its tutorial on joins. To check a small extract as JSON, use the CSV to JSON converter.