VLOOKUP with Multiple Criteria: 3 Methods That Work

VLOOKUP cannot natively match on two or more columns — its first argument accepts exactly one lookup value. But you can work around that limitation three reliable ways: build a helper column that concatenates your criteria, switch to XLOOKUP with joined arrays, or use an INDEX/MATCH array formula. Here is each method with a working formula and the pitfalls that break them.

Method 1: How do I use a helper column with VLOOKUP?

Insert a new column at the left of your lookup table that joins the criteria columns into one key, then VLOOKUP against that key. This is the only method that works in every Excel version, and it is also the fastest to calculate on large tables because it avoids array math.

' In the lookup table, insert column A with a combined key:
A2: =B2&"|"&C2        ' e.g. "North|Widget"

' Then look up with the same combined key:
=VLOOKUP(F2&"|"&G2, A:D, 4, FALSE)

Use a delimiter like | that never appears in your data. Without one, "AB"&"C" and "A"&"BC" both produce "ABC" and you get false matches. The obvious downside: you have to modify the source table, which is not always allowed (shared files, external data connections).

Method 2: How does XLOOKUP handle multiple criteria?

XLOOKUP accepts arrays as its lookup arguments, so you can concatenate entire columns on the fly — no helper column, no changes to the source data. This is the cleanest solution if you have Excel 365 or Excel 2021, and it is why we recommend XLOOKUP over VLOOKUP in VLOOKUP vs XLOOKUP.

=XLOOKUP(F2&"|"&G2,
         A2:A500&"|"&B2:B500,
         C2:C500,
         "Not found")

The fourth argument gives you a friendly result when nothing matches instead of #N/A. You can chain as many criteria as you need — just keep adding &"|"&column segments to both the lookup value and the lookup array. For boolean-multiplication alternatives and performance notes, see XLOOKUP with multiple criteria.

One caution: concatenation converts everything to text. The date 2024-01-05 and the number 45296 concatenate identically, so if a criterion is a date or number, wrap it in TEXT with an explicit format on both sides:

=XLOOKUP(TEXT(F2,"yyyy-mm-dd")&"|"&G2,
         TEXT(A2:A500,"yyyy-mm-dd")&"|"&B2:B500,
         C2:C500, "Not found")

Method 3: How does the INDEX/MATCH array method work?

INDEX/MATCH can test multiple criteria by multiplying boolean arrays: each comparison returns TRUE/FALSE (1/0), the multiplication yields 1 only where every criterion holds, and MATCH finds the first 1. This works in older Excel versions without touching the source table — the classic pattern from INDEX MATCH explained.

=INDEX(C2:C500,
       MATCH(1, (A2:A500=F2)*(B2:B500=G2), 0))

In Excel 2019 and earlier you must confirm this with Ctrl+Shift+Enter (it becomes {=INDEX(...)}); in Excel 365 it just works as a normal formula. Because it compares native values rather than concatenated text, it avoids the date/number ambiguity of methods 1 and 2 entirely.

What are the common pitfalls?

Most "multiple criteria lookup returns wrong result" problems trace back to one of four causes: a missing delimiter creating accidental key collisions, numbers stored as text on one side of the comparison, forgetting FALSE/exact-match mode so Excel silently returns a near match, and full-column references in array formulas. That last one hurts: A:A&"|"&B:B concatenates a million rows on every recalculation. Limit ranges to the actual data, or convert the source to an Excel Table so ranges grow automatically.

Also note that all three methods return the first match only. If multiple rows satisfy your criteria and you want all of them, you need FILTER, not a lookup:

=FILTER(C2:C500, (A2:A500=F2)*(B2:B500=G2), "No match")

Which method should I pick?

If you have Excel 365/2021, use XLOOKUP with concatenated arrays — no source changes, readable, with built-in not-found handling. If you are on an older version and can edit the table, the helper column is the fastest and most robust. If you cannot touch the source data on an older version, INDEX/MATCH with boolean multiplication is your tool.