VLOOKUP with Multiple Criteria
VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.
Introduction
A classic complaint about VLOOKUP is that it can only search one column. In practice you often need to match on two keys at once — for example, find the price for Product A in Region North. This guide walks through four techniques, from a quick helper column to the modern XLOOKUP approach, so you can pick the one that fits your Excel version.
Prerequisites
- Basic VLOOKUP syntax
- Understanding of absolute vs relative references
- Microsoft 365 (only needed for the XLOOKUP method)
1Why VLOOKUP Alone Falls Short
VLOOKUP takes a single lookup_value and searches the first column of your table. If your match depends on two columns — say Product and Region — there is no second lookup_value to pass. The fix is to combine the keys into one value VLOOKUP can match.
Keep the combined key in the FIRST column of the lookup table.
Both the search value and the table key must use the same join order.
2Method 1: Helper Column (Most Compatible)
Add a column that concatenates the two keys, then VLOOKUP that combined value. This works in every version of Excel and Google Sheets.
Build the key
In a new first column of your table, join the two criteria.
=A2&B2Lookup the joined key
Concatenate your two search cells the same way in the VLOOKUP.
=VLOOKUP(G2&H2, A:E, 4, FALSE)Example
=VLOOKUP(G2&H2, Table1, 4, FALSE)Because the first column is A&B, only rows matching BOTH criteria produce a hit.
Use a delimiter like A2&"|"&B2 if keys could run together (e.g. 'AB'+'C' vs 'A'+'BC').
3Method 2: CHOOSE + VLOOKUP (No Helper Column)
CHOOSE({1,2}, range1&range2, returnRange) builds a virtual two-column table on the fly, letting VLOOKUP match the concatenated key without editing your sheet.
Example
=VLOOKUP(G2&H2, CHOOSE({1,2}, A:A&B:B, D:D), 2, FALSE)In legacy Excel this is an array formula — confirm with Ctrl+Shift+Enter. In Microsoft 365 it spills automatically.
Whole-column references (A:A) in array formulas can be slow; restrict to your data range.
4Method 3: INDEX + MATCH (Recommended Classic)
INDEX returns a value from a range; MATCH finds the row. Multiplying two TRUE/FALSE conditions inside MATCH gives an exact two-criteria match without any helper column.
Example
=INDEX(D:D, MATCH(1, (A:A=G2)*(B:B=H2), 0))MATCH(1, ...) finds the first row where both conditions are TRUE (1). Array-enter in legacy Excel.
INDEX + MATCH also looks LEFT, something VLOOKUP can never do.
5Method 4: XLOOKUP (Microsoft 365 / Google Sheets)
XLOOKUP accepts array conditions directly, so multiple criteria are just AND-ed inside the lookup_value. This is the cleanest solution if your version supports it.
Example
=XLOOKUP(1, (A:A=G2)*(B:B=H2), D:D)No array-entry, no helper column, and you can return multiple columns by widening the return array.
Add a 4th argument, like "Not found", to handle missing combinations gracefully.
Functions Used
VLOOKUP
Looks up a value in the first column of a table and returns a value from another column
INDEX
Returns the value of a cell in a range based on row and column numbers
MATCH
Returns the relative position of an item in a range
XLOOKUP
Modern lookup that searches any column and returns any column, with built-in error handling
SUMIFS
Sums values that meet multiple criteria — useful when you want an aggregated match
Summary
VLOOKUP can't natively match two columns, but you have options: a helper column for maximum compatibility, CHOOSE for a no-edit workaround, INDEX + MATCH for a robust classic formula, and XLOOKUP when your version allows. Pick the helper column or XLOOKUP for day-to-day work, and keep INDEX + MATCH in your back pocket for leftward lookups.
Next Steps
- Try the INDEX + MATCH version on a dataset where the result is to the LEFT of the key
- Switch to XLOOKUP if you are on Microsoft 365
- Explore SUMIFS when you need an aggregated (summed) match instead of a single cell