Joining Two Sheets on Two Keys in Excel and in SQL

A price depends on product and region. An exchange rate depends on currency and date. A tax rule depends on state and category. Whenever a value is identified by a pair of fields, a lookup on one of them returns the wrong row or the first of several. Matching on two keys is a routine task, and the spreadsheet and SQL versions look different enough that the same mistake is easy to make twice. This article shows both, with the uniqueness check that prevents silent errors. It is one of the worked pairs in moving between Excel and SQL, and the single-key version is in XLOOKUP examples that replace VLOOKUP pain.

Sample data

An Orders sheet (and table orders) with product and region per order line, and a Prices sheet (and table prices) with one price per product and region.

Order 4 asks for a Gadget price in East, which the price table does not have. Any correct method has to say what happens there.

Excel: a helper key column

The simplest approach that works in every version is to build one key from the two fields in both tables. In Prices, add =A2&"|"&B2 (product, separator, region) and in Orders the same. Then look up on the combined key:

=XLOOKUP(B2&"|"&C2, Prices!D:D, Prices!C:C, "no price")

The separator prevents false matches such as "AB" plus "C" equal to "A" plus "BC". Choose a character that never appears in the data. The helper column also makes the logic visible: you can see the keys, sort them and check them. Its cost is an extra column in each table, and the need to keep both formulas the same.

For versions without XLOOKUP, use INDEX and MATCH on the helper key: =INDEX(Prices!C:C, MATCH(B2&"|"&C2, Prices!D:D, 0)).

Excel: XLOOKUP over two conditions

Without a helper column, multiply two comparisons to build an array of ones and zeros, and look up the 1:

=XLOOKUP(1, (Prices[product]=B2)*(Prices[region]=C2), Prices[price], "no price")

Each comparison produces an array of TRUE and FALSE over the whole price table; multiplying turns them into 1 and 0 and keeps rows where both are true. XLOOKUP finds the first 1. This works in Microsoft 365 and Excel 2021 as typed. It is compact, and it evaluates the full table for each order row, so it is slower than a keyed lookup on large tables. Use the helper-key version for big sheets.

Excel: SUMIFS for numeric lookups

If the value you want is a number and each pair occurs once, SUMIFS works with any number of criteria:

=SUMIFS(Prices[price], Prices[product], B2, Prices[region], C2)

It works in every version. The danger is that it adds duplicates: if the price table accidentally holds two rows for Widget in North (4.00 and 4.20), SUMIFS returns 8.20 with no warning. A missing pair returns 0, not an error, so a missing price looks like a free item. Guard both:

=IF(COUNTIFS(Prices[product], B2, Prices[region], C2)=1,
    SUMIFS(Prices[price], Prices[product], B2, Prices[region], C2),
    NA())

This returns #N/A for zero or more than one match, so the problem is visible.

Excel: Power Query merge

For recurring work, merge queries in Power Query. Load both tables, then Home, Merge Queries, select the Orders table, click product then Ctrl+click region, select the same two columns in Prices (in the same order) and pick the join kind. A left outer merge keeps all orders and adds the matching price columns, with null for missing pairs. Expand the merged column to bring in price. The steps are recorded and refresh with the data (splitting and merging columns in Power Query covers the column operations around it).

The selection order of the key columns must be the same in both tables, or Excel pairs product with region. Power Query also warns when the merge multiplies rows, by showing the match counts in the dialog; read that message.

SQL: a two-column join

SELECT o.order_id, o.product, o.region, o.qty, p.price,
       o.qty * p.price AS line_total
FROM orders o
LEFT JOIN prices p
  ON p.product = o.product
 AND p.region  = o.region;

Both conditions go in ON. A left join keeps every order, and the price is NULL for the unmatched order 4. With an inner join (JOIN), order 4 would disappear, which changes the row count and the total silently; use an inner join only when missing matches should drop the row. The result for the sample: orders 1 to 3 get prices 4.00, 4.50 and 12.00, and order 4 has NULL.

If you want the missing match to be a hard error, check after the join: SELECT count(*) FROM orders o LEFT JOIN prices p ON ... WHERE p.price IS NULL, and fail when the count is above zero.

The uniqueness check

A join returns every matching row, so if the lookup table has two rows for the same pair, the order appears twice and totals double. This is the most common SQL mistake when replacing a spreadsheet lookup, because the spreadsheet silently took the first match. Test the lookup table before joining:

SELECT product, region, count(*)
FROM prices
GROUP BY product, region
HAVING count(*) > 1;

No rows means the pair is unique. Better, make it impossible: ALTER TABLE prices ADD CONSTRAINT prices_pk PRIMARY KEY (product, region); so a duplicate is rejected on insert. In Excel, the equivalent check is =COUNTIFS(Prices[product], A2, Prices[region], B2) in a helper column, with conditional formatting for values above 1 (spotting data entry errors with conditional formatting).

Dirty keys

Matches fail for boring reasons. A trailing space, a different case, a number in one table and text in the other, a region spelled "N. America" in one place. Excel's = is case-insensitive and XLOOKUP ignores case by default; PostgreSQL's = on text is case-sensitive, so 'north' does not match 'North'. Clean both sides the same way before the join: lower(trim(region)) in SQL, LOWER(TRIM(...)) in a helper column. A value with a non-breaking space survives TRIM in Excel; replace CHAR(160) first (trimming spaces and non breaking spaces).

NULL keys never match in SQL: NULL = NULL is unknown, so a row with a blank region does not join to a price row with a blank region. In Excel, a blank matches a blank. If blanks are meaningful (a price that applies to all regions), express that as a separate rule, such as a row with region "ALL" and a fallback lookup.

Fallback keys

Real price tables often have a default: a price for a product that applies unless a region-specific one exists. Express the fallback explicitly. In SQL:

SELECT o.*, coalesce(pr.price, pd.price) AS price
FROM orders o
LEFT JOIN prices pr ON pr.product = o.product AND pr.region = o.region
LEFT JOIN prices pd ON pd.product = o.product AND pd.region = 'ALL';

In Excel, nest two lookups: =XLOOKUP(key1, ..., XLOOKUP(key2, ..., "no price")). The two-step lookup states the rule in the formula, where a reader can see it.

When two keys are not enough to identify a row

If the pair is not unique, a third field is hiding in the data: a date range, a currency, a customer tier. A price that changes over time has a valid-from date, and the lookup must find the latest row not after the order date, which is an approximate match or a range join (lookup with approximate match versus SQL range join). If keys are text with inconsistent spellings, the right answer is a mapping table that normalizes them once. And if the join is one that people do every month, move it to the database and publish the result (when SQL beats formulas once the data grows). The PostgreSQL manual describes joined tables in detail, and Microsoft documents merging queries in Power Query.