Excel Lookup Value in Two Columns for Match

Formulas & functions · Updated November 22, 2026

To look up a value based on a match in two columns, use INDEX and MATCH with array syntax (pressing Ctrl+Shift+Enter in older Excel) or use XLOOKUP with a concatenated lookup array (Excel 2021/Microsoft 365). For example, to find a price where column A equals “Widget” and column B equals “Large”, the formula =XLOOKUP("Widget"&"Large", A:A&B:B, C:C) returns the matching price.

Why a Two-Column Lookup Is Different

A normal VLOOKUP or XLOOKUP searches one column for a unique key. When your data has duplicate values in one column, or the identifier is split across two columns (like first name and last name, or product code and size), a single-column lookup returns the first match, which may be wrong. A two-column lookup combines both columns into a single composite key, so the match is only successful when both conditions are true.

Method 1: XLOOKUP with Concatenation (Excel 2021 and Microsoft 365)

XLOOKUP is the simplest modern method. It allows you to build a lookup array by joining the two columns with the & operator. For example, with product names in A2:A100, sizes in B2:B100, and prices in C2:C100, to find the price for “Widget” size “Large”:

  1. In a cell, enter: =XLOOKUP("Widget"&"Large", A2:A100&B2:B100, C2:C100)
  2. Press Enter. The result is the price from the first row where A equals “Widget” and B equals “Large”.

If no match is found, XLOOKUP returns #N/A by default. You can add a custom message: =XLOOKUP("Widget"&"Large", A2:A100&B2:B100, C2:C100, "Not found"). This method works in Excel 2021 and Microsoft 365. It does not require array entry (no Ctrl+Shift+Enter).

Method 2: INDEX and MATCH with Array Formula (All Excel Versions)

For older Excel versions (2019 and earlier), use INDEX and MATCH with an array formula. The MATCH function compares a concatenated lookup value against a concatenated array of the two columns. For the same example:

  1. Select a cell and type: =INDEX(C2:C100, MATCH("Widget"&"Large", A2:A100&B2:B100, 0))
  2. Press Ctrl+Shift+Enter on Windows (Cmd+Shift+Enter on Mac) to enter it as an array formula. Excel will enclose it in curly braces { } automatically.

The MATCH part finds the row number where the concatenation equals “WidgetLarge”, and INDEX returns the price from that row. This works in all Excel versions, but remember the special key combination.

Method 3: SUMPRODUCT for Numeric Results

If the value you want to return is numeric (like a price, quantity, or score), you can use SUMPRODUCT to avoid array entry. The formula multiplies conditions and sums the matching result:

With the same layout, enter: =SUMPRODUCT((A2:A100="Widget")*(B2:B100="Large")*C2:C100)

This returns the sum of all prices where both conditions are true. If there is only one match, it equals that price. If there are multiple matches, it sums them, which may not be what you want. Use it only when you expect a single match or you actually want the sum.

How to Choose the Right Method

  • Use XLOOKUP if you have Excel 2021 or Microsoft 365 – it’s the easiest and most flexible.
  • Use INDEX/MATCH if you need compatibility with older Excel versions and want to return any column (text or numbers).
  • Use SUMPRODUCT only for numeric results and when you are certain there is exactly one match, or you want to sum multiple matches.
  • Consider a helper column if your data is large or you need to do many lookups – create a new column with =A2&B2 and use a simple VLOOKUP or XLOOKUP on that helper column.

Common Problems and How to Fix Them

Problem: The formula returns #N/A even though you see a match. Check for extra spaces in the data. Use =TRIM(A2) to remove leading/trailing spaces, or use =XLOOKUP("Widget"&"Large", TRIM(A2:A100)&TRIM(B2:B100), C2:C100) (Microsoft 365 only).

Problem: The array formula doesn’t work. You must press Ctrl+Shift+Enter, not just Enter. If you forget, it returns #VALUE! or an incorrect result.

Problem: Case sensitivity. By default, Excel lookups are case-insensitive. If you need case-sensitive matching, use EXACT in an array formula, or use XLOOKUP with the match_mode argument set to 1? Actually, XLOOKUP does not have a case-sensitive option; use INDEX/MATCH with EXACT: =INDEX(C2:C100, MATCH(TRUE, EXACT(A2:A100,"Widget")*EXACT(B2:B100,"Large"), 0)) entered as an array formula.

Problem: Duplicate matches. If two rows have the same values in both columns, XLOOKUP and INDEX/MATCH return the first match. To get the last match, use XLOOKUP with the search_mode argument set to -1 (search last to first).

Alternatives: Helper Columns and Power Query

Helper column: Add a new column (e.g., D) with =A2&B2 and drag down. Then use a regular VLOOKUP: =VLOOKUP("Widget"&"Large", D2:C100, 3, FALSE) – but note the table array must include the helper column as the first column. This is simple and works in all versions.

Power Query: If you need to merge tables based on two columns, use Data > Get Data > From Other Sources > Blank Query, then merge queries using two columns as key. This is more advanced but handles large datasets and refreshes automatically.

Frequently Asked Questions

Can I use VLOOKUP with two columns? No, VLOOKUP only looks in the first column of a table. Use XLOOKUP or INDEX/MATCH as described above, or create a helper column.

Why does my XLOOKUP return #VALUE! instead of #N/A? This happens when the lookup array contains errors, or when you use concatenation with arrays that have different sizes. Ensure both columns have the same number of rows.

How do I do a two-column lookup in Google Sheets? Google Sheets has similar functions. You can use =INDEX(C2:C100, MATCH("Widget"&"Large", A2:A100&B2:B100, 0)) as an array formula (press Ctrl+Shift+Enter in Sheets too), or use FILTER to return all matches.

Is there a way to return multiple matches? Yes, use the FILTER function in Excel 365: =FILTER(C2:C100, (A2:A100="Widget")*(B2:B100="Large")) returns all prices that match both criteria.

What if my columns are not adjacent? That’s fine – the formulas reference ranges directly. Just adjust the range references to point to the correct columns, even if they are far apart.

Can I use wildcards in a two-column lookup? Yes, with XLOOKUP you can use wildcards by setting the match_mode argument to 2. For example, =XLOOKUP("Wi*"&"Lar*", A2:A100&B2:B100, C2:C100, , 2) matches any product starting with “Wi” and any size starting with “Lar”.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.