How to join two tables on two columns at once

When you need a composite key

A single-column join works when that column identifies one row, as in joining on a common column. Stock by store, targets by rep and month, or invoice lines by invoice and line number all repeat each value of either column on its own. Join on just SKU and every store's stock attaches to every store's sales.

Method 1: a helper key column

Add a column to both tables that joins the parts with a separator, then do an ordinary lookup on it. The separator matters: without it, “AB” + “1” and “A” + “B1” both become “AB1”.

Stock!D2:  =A2&"|"&B2
Sales!F2:  =XLOOKUP(A2&"|"&B2, Stock!D:D, Stock!C:C, "no stock row")

Helper keys are easy to audit (you can see the key), work in every version including VLOOKUP and COUNTIF, and are fast on big sheets. Clean the parts first: TRIM spaces and make sure numbers aren't stored as text on one side, or “Leeds |A-100” won't match “Leeds|A-100”.

Method 2: XLOOKUP with boolean arrays

In Microsoft 365, Excel 2021 and Google Sheets you can skip the helper column. Multiplying two TRUE/FALSE arrays gives 1 only where both conditions hold, and XLOOKUP looks for the first 1:

=XLOOKUP(1, (Stock!A2:A500=A2) * (Stock!B2:B500=B2), Stock!C2:C500, "no stock row")

Add a third condition with another * (range=value). Use bounded ranges rather than whole columns, because the formula evaluates every row of every array. In older Excel, INDEX(C:C, MATCH(1, (A:A=x)*(B:B=y), 0)) does the same as an array formula (Ctrl+Shift+Enter). If you want a total rather than a single value, SUMIFS already takes several criteria and needs no trick.

Both methods return the first match only. If the key pair isn't unique in the lookup table, check with =COUNTIFS(Stock!A:A, A2, Stock!B:B, B2) and fix duplicates before trusting the result.

Method 3: Power Query multi-column merge

1. Load both tables into Power Query (Data → From Table/Range).
2. Home → Merge Queries. Pick the Sales and Stock queries.
3. In the top table, click Store, then Ctrl-click SKU. In the bottom table, click Store, then Ctrl-click SKU. Small numbers (1, 2) appear in the headers to show the pairing order.
4. Choose the join kind, click OK, then expand the On hand column.

The order you click defines the pairs, so the columns must be selected in the same order in both tables. Power Query's default comparison is case-sensitive. The join kinds themselves are covered in Power Query merge join kinds.

Method 4: SQL composite key

SELECT s.store, s.sku, s.sold, k.on_hand,
       ROUND(k.on_hand / NULLIF(s.sold, 0), 1) AS weeks_of_cover
FROM sales s
LEFT JOIN stock k
  ON k.store = s.store
 AND k.sku = s.sku;

Both conditions go in the ON clause. Putting the second in a WHERE clause turns a LEFT JOIN into an inner join for rows where stock is missing. Some databases also accept USING (store, sku) when the column names are identical. Watch for NULLs: NULL = NULL is not true in SQL, so rows with a blank store never match. In Analistable you just ask “weeks of cover by store and SKU” and the generated SQL joins on both columns; you can read the query before trusting the answer.

Choosing a method

For more join patterns, see the join spreadsheets guide.

Checking the result

After any multi-key join, run three quick checks before using the numbers. First, row count: a lookup or LEFT JOIN should leave the sales table with the same number of rows it started with. More rows means the key pair repeats in the stock table. Second, unmatched rows: count the "no stock row" results or the NULLs, and look at a few. They are usually a store spelled differently ("York " with a space, "YORK" in capitals) or a SKU stored as a number on one side.

Third, totals: the sum of a looked-up column should equal the source total for the keys that matched. In SQL, SELECT store, sku, count(*) FROM stock GROUP BY ALL HAVING count(*) > 1 lists duplicate pairs directly; in Excel, a COUNTIFS column on the lookup table does the same. Fix the data rather than the formula, so every later join benefits.

Frequently asked questions

Can I use SUMIFS instead of a lookup for two criteria?

Yes, when you want a number and each pair is unique, SUMIFS with both criteria returns it. It returns 0 rather than an error when nothing matches, so check for zeros.

How do I VLOOKUP with two criteria?

Add a helper column to the lookup table that concatenates both columns with a separator, put it first, and VLOOKUP the same combination, e.g. =VLOOKUP(A2&"|"&B2, Helper!A:C, 3, FALSE).

Why use a separator in the helper key?

Without one, different pairs can produce the same text, such as "AB"&"1" and "A"&"B1". A character that never appears in the data, like |, prevents that.

Can Power Query merge on more than two columns?

Yes. Ctrl-click as many columns as you need, in the same order in both tables.

Why does my multi-column join duplicate rows?

The combination isn't unique in one of the tables. Count each key pair with COUNTIFS or GROUP BY and remove or aggregate the duplicates first.

Related guides

• How to join two spreadsheets on a common column
• Power Query Merge Queries: every join kind explained
• How to join spreadsheets on a common column
• Lead to customer match template
• Left join vs XLOOKUP: when and why the results differ