INDEX and MATCH Versus a Join on a Key in SQL Compared

INDEX and MATCH are the pair that people reach for when VLOOKUP is not enough: a lookup to the left of the key, a lookup in a grid with two headers, a lookup that survives a column insertion. In SQL the same jobs are joins. This post translates each shape on a small example, including the two-way lookup, which looks the hardest and turns out to be a join on two columns once the grid is stored properly. It is a spoke of translating Excel formulas into SQL, and it extends VLOOKUP and XLOOKUP versus SQL JOIN.

One-way lookup

MATCH(key, range, 0) returns the position of the key in a column, and INDEX(range, position) returns the value at that position of another column. Together:

=INDEX(Customers!H:H, MATCH(B2, Customers!G:G, 0))

finds the name (column H) of the customer whose id (column G) equals B2. In SQL:

WITH orders(order_no, customer_id) AS (VALUES (1, 10), (2, 11), (3, 12), (4, 99)),
     customers(id, name) AS (VALUES (10, 'Acme'), (11, 'Globex'), (12, 'Initech'))
SELECT o.order_no, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
ORDER BY o.order_no;

 order_no |  name
----------+---------
        1 | Acme
        2 | Globex
        3 | Initech
        4 |

The return column can be on either side of the key, which is why people prefer this pair to VLOOKUP: a lookup to the left works because INDEX takes any range. In SQL, every column of the joined table is available, so there is no left-right restriction. The not-found case is #N/A in Excel and NULL in SQL. People wrap the formula in IFERROR; the SQL counterpart is COALESCE.

Two-way lookup in a grid

The pattern that does not look like a join is the grid lookup. A price list laid out with products down the side and sizes across the top:

The formula finds the price for a product and a size:

=INDEX(B2:C3, MATCH("notebook", A2:A3, 0), MATCH("L", B1:C1, 0))

The first MATCH finds the row (2), the second the column (2), and INDEX returns the cell where they cross: 4.00. In a database, a grid like this is not how the data should be stored. Store one row per cell:

WITH price_list(product, size, price) AS (
  VALUES ('pen','S',1.00), ('pen','L',1.50), ('notebook','S',3.00), ('notebook','L',4.00)
),
orders(order_no, product, size) AS (
  VALUES (1,'notebook','L'), (2,'pen','S'), (3,'pen','L'), (4,'stapler','S')
)
SELECT o.order_no, o.product, o.size, p.price
FROM orders o
LEFT JOIN price_list p ON p.product = o.product AND p.size = o.size
ORDER BY o.order_no;

 order_no | product  | size | price
----------+----------+------+-------
        1 | notebook | L    |  4.00
        2 | pen      | S    |  1.00
        3 | pen      | L    |  1.50
        4 | stapler  | S    |

The two MATCH calls became two equality conditions in the join. The long form has one row per product and size, which is the unpivoted grid. If the data arrives as a grid, unpivot it on load: in Python with melt, in SQL with UNPIVOT (SQL Server, DuckDB, Oracle) or a LATERAL over a VALUES list (PostgreSQL):

SELECT g.product, v.size, v.price
FROM grid g
CROSS JOIN LATERAL (VALUES ('S', g.s), ('L', g.l)) AS v(size, price);

Once unpivoted, the grid never has to be a formula again, and adding a size means adding rows, not columns (unpivoting columns into rows with Power Query does the same step inside Excel).

MATCH types: exact, next smaller, next larger

MATCH(x, range, 0) is exact. MATCH(x, range, 1), the default, finds the largest value less than or equal to x in an ascending range. MATCH(x, range, -1) finds the smallest value greater than or equal to x in a descending range. These are the range lookups behind commission tiers and shipping bands. With the tiers

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

the rate for an amount is the one with the largest floor not above it:

SELECT a.amount, t.rate
FROM (VALUES (45.5), (120.0), (500.0)) AS a(amount)
LEFT JOIN LATERAL (
  SELECT rate FROM tiers WHERE floor <= a.amount ORDER BY floor DESC LIMIT 1
) t ON true;

 amount | rate
--------+------
   45.5 | 0.00
  120.0 | 0.05
  500.0 | 0.10

For type -1, flip the comparison and the order: WHERE ceiling >= a.amount ORDER BY ceiling ASC LIMIT 1. In Excel the sort order of the range is a precondition that the formula does not check, and a range in the wrong order returns wrong results with no error. The SQL does not care how the table is ordered. A better table has floor and ceiling columns, so the join is a.amount >= floor AND a.amount < ceiling and a test can prove there are no gaps or overlaps.

Wildcards and partial matches

MATCH with type 0 accepts wildcards in text: MATCH("ab*", range, 0) returns the first cell that starts with ab. In SQL that is a LIKE, and a join on LIKE returns every match, not the first:

SELECT c.id FROM customers c WHERE c.name LIKE 'Ab%' ORDER BY c.id LIMIT 1;

Add ORDER BY with LIMIT 1 to reproduce "the first". Note again that Excel ignores case, so use ILIKE in PostgreSQL to match it.

Row numbers and positions

Sometimes the formula uses only MATCH, to find a position: "which row is this in?". In SQL the position of a row is not a property of the data; there is no such thing as the third row of a table. If the workbook uses positions to express order (the order the user typed the rows), translate it into an explicit sort column, or use row_number() OVER (ORDER BY ...) when the order has a rule. A MATCH used to detect existence (ISNUMBER(MATCH(x, list, 0))) is EXISTS (SELECT 1 FROM list WHERE list.id = x) or x IN (SELECT id FROM list):

SELECT o.order_no, o.customer_id IN (SELECT id FROM customers) AS known_customer
FROM orders o ORDER BY o.order_no;

which returns true for orders 1 to 3 and false for order 4.

Duplicates and the first match

MATCH finds the first occurrence; a join finds all of them. If the key repeats in the lookup table, the join duplicates rows and totals double. Check with SELECT id, count(*) FROM customers GROUP BY id HAVING count(*) > 1, add a unique constraint, or choose the first row on purpose with DISTINCT ON or row_number() (VLOOKUP and XLOOKUP versus SQL JOIN). A two-key lookup has the same issue: the pair (product, size) must be unique in the price table.

Mismatched types and stray spaces break MATCH in the same way as VLOOKUP: a number against a text, a trailing space. In SQL the join may raise an error for incompatible types, which is helpful, and silently fail to match for stray spaces, which is not. Normalize keys on load (keeping leading zeros in Excel and when importing).

Performance

MATCH with type 0 scans the column, so a column of 100,000 formulas each scanning 100,000 rows does up to ten billion comparisons in the worst case. Excel has optimized some lookups in recent versions, and the cost is still visible in large workbooks. A database builds a hash or uses an index and does the whole job in time roughly proportional to the sizes of the two tables, not their product (why SQL beats formulas once the data gets large). I have not measured a specific case here.

When INDEX and MATCH should stay as formulas

The grid is small and edited by hand. A price grid with six cells, maintained by the person who uses it, is a good spreadsheet.

The user picks the row and column from drop-downs. An interactive lookup is the interface.

The formula is one of three in a small model. Translating it adds a database to a problem that does not need one.

The grid is the output. If the point is to present a matrix, build the matrix in the reporting layer from long-form data.

Excel documents the functions in its pages on INDEX and MATCH, and PostgreSQL describes LATERAL in its table expressions reference. To check a small extract as JSON, use the CSV to JSON converter.