Excel VLOOKUP with Two Criteria: How-To

Formulas & functions · Updated November 22, 2026

To look up a value using two criteria in Excel, use XLOOKUP with a helper column that concatenates the two criteria, or use the classic INDEX/MATCH array formula. For example, =XLOOKUP(A2&"|"&B2, Table1[Col1]&"|"&Table1[Col2], Table1[ReturnCol]) finds a match where both Col1 equals A2 and Col2 equals B2. The native VLOOKUP function alone cannot handle two separate criteria columns, so you must either add a helper column or switch to a more flexible function.

Why VLOOKUP Fails with Two Criteria

VLOOKUP searches for a single lookup value in the leftmost column of a table and returns a value from a column to the right. It has no built-in way to match two different columns at once. If you try to use two criteria, you get a #N/A error or an incorrect match because VLOOKUP only sees one lookup value. The workaround is to combine the criteria into a single lookup value, or to use a function that supports multiple conditions.

Method 1: Helper Column with Concatenation

The simplest approach is to add a helper column that joins the two criteria with a separator (like a pipe | or a space). Then use VLOOKUP with a similarly joined lookup value.

  1. Insert a new column (e.g., column C) next to your data. In C2, enter =A2&"|"&B2 and fill down.
  2. In your lookup cell, enter the two criteria (e.g., in E2 and F2).
  3. Use the formula: =VLOOKUP(E2&"|"&F2, C:D, 2, FALSE) — assuming the return value is in column D.

For example, with product names in A2:A20 and colors in B2:B20, and prices in D2:D20, the helper column C contains A2&"|"&B2. To find the price of a red widget, enter “widget” in E2 and “red” in F2, then use =VLOOKUP(E2&"|"&F2, C2:D20, 2, FALSE). This returns the price from column D. The separator prevents false matches like “redapple” vs “red|apple”.

Method 2: INDEX and MATCH Array Formula

If you prefer not to add a helper column, use an array formula with INDEX and MATCH. This works in all Excel versions, but in older versions you must press Ctrl+Shift+Enter (CSE) to enter it. In Excel 365 and 2021, it works as a dynamic array formula without CSE.

With lookup values in E2 and F2, and data in A2:C20 (criteria in A and B, return in C), use:

=INDEX(C2:C20, MATCH(1, (A2:A20=E2)*(B2:B20=F2), 0))

In Excel 365, just press Enter. In Excel 2019 and earlier, press Ctrl+Shift+Enter. The multiplication (A2:A20=E2)*(B2:B20=F2) creates an array of 1s and 0s, and MATCH finds the position of the first 1. This method is powerful but can be slower on very large datasets because it calculates the entire array.

Method 3: XLOOKUP with Concatenation (Recommended)

If you have Excel 2021 or Microsoft 365, XLOOKUP is the cleanest solution. It supports arrays natively, so you can concatenate the lookup arrays and the lookup value directly. No helper column needed.

With criteria in E2 and F2, and data in A2:C20 (criteria in A and B, return in C), use:

=XLOOKUP(E2&"|"&F2, A2:A20&"|"&B2:B20, C2:C20)

This returns the value from C where both A and B match. If no match is found, XLOOKUP returns #N/A by default. You can add a fourth argument for a custom message, e.g., =XLOOKUP(E2&"|"&F2, A2:A20&"|"&B2:B20, C2:C20, "Not found"). XLOOKUP is faster and easier to read than the INDEX/MATCH array formula.

Choosing the Right Method

  • Helper column + VLOOKUP: Works in all Excel versions, easy to understand, but requires modifying your data layout.
  • INDEX/MATCH array: No helper column, works in all versions (with CSE in older ones), but can be confusing and slower on big tables.
  • XLOOKUP: Cleanest syntax, no helper column, dynamic arrays, but only in Excel 2021 and Microsoft 365.

For most users, the helper column is the safest if you share files with people using older Excel. If you control the version, XLOOKUP is the best choice.

Common Problems and Pitfalls

  • #N/A error: No exact match. Check for extra spaces, non-printable characters, or different data types (text vs number). Use =TRIM() and =CLEAN() to clean data.
  • False matches: If you concatenate without a separator, “red apple” and “redapple” could collide. Always use a separator like "|" or "-".
  • Array formula not working: In older Excel, if you forget to press Ctrl+Shift+Enter, you get a #VALUE! error. In Excel 365, ensure you don’t have implicit intersection issues.
  • Case sensitivity: VLOOKUP, XLOOKUP, and MATCH are case-insensitive by default. If you need case-sensitive matching, use EXACT in an array formula or XLOOKUP with the match_mode argument set to 0 (exact) — but that still ignores case. For case-sensitive, use INDEX/MATCH with EXACT.

Frequently Asked Questions

Can VLOOKUP return multiple columns with two criteria?

Yes, but you need to specify the column index for each column you want. With a helper column, you can use multiple VLOOKUP formulas, each with a different column index. With XLOOKUP, you can return an array by specifying multiple return columns, e.g., =XLOOKUP(E2&"|"&F2, A2:A20&"|"&B2:B20, C2:D20) returns both C and D values side by side.

How do I handle duplicates when using two criteria?

Both VLOOKUP and XLOOKUP return the first match found. If you have duplicate combinations, you’ll get the first one. To get all matches, use the FILTER function in Excel 365: =FILTER(C2:C20, (A2:A20=E2)*(B2:B20=F2)).

Is there a way to use two criteria without a helper column in older Excel?

Yes, the INDEX/MATCH array formula works without a helper column. Just remember to press Ctrl+Shift+Enter in Excel 2019 and earlier. It’s a bit advanced, but it’s the standard solution before XLOOKUP existed.

Why does my VLOOKUP return the wrong value even though the criteria match?

This often happens when the lookup column isn’t the leftmost column in the table array, or when there are leading/trailing spaces. Ensure your table array starts with the column that contains the concatenated lookup value, and clean your data with TRIM and CLEAN.

Can I use two criteria with VLOOKUP in Google Sheets?

Yes, the same helper column method works in Google Sheets. For a no-helper solution, use FILTER or INDEX/MATCH. Google Sheets also has XLOOKUP as of 2022, so the XLOOKUP method works there too.

Alternatives to VLOOKUP for Two Criteria

Besides the methods above, you can use the FILTER function (Excel 365) to return all matching rows, or the SUMIFS/COUNTIFS functions if you need to aggregate values. For example, =SUMIFS(C2:C20, A2:A20, E2, B2:B20, F2) sums all C values where both criteria match. If you need to pull a single value and your data is sorted, you could also use LOOKUP with concatenation, but it’s less reliable. The key is to choose the tool that fits your Excel version and your need for speed versus simplicity.

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.