VLOOKUP with Multiple Criteria in Excel: 3 Working Methods
VLOOKUP can only look for one value in the first column of a table. To match on two or more fields (for example Region and Product), you have three practical options:
| Method | Needs a helper column? | Works in |
|---|---|---|
| 1. VLOOKUP with a helper column | Yes | All Excel versions, Google Sheets |
| 2. INDEX + MATCH array formula | No | Excel (Ctrl+Shift+Enter before 365), Google Sheets with ARRAYFORMULA |
| 3. XLOOKUP with joined arrays | No | Excel 365/2021 |
Method 1: VLOOKUP with a helper column
The helper column joins the fields into one key. It must be the leftmost column of the range you give to VLOOKUP, so insert it before your data.
Sample data (column A is the helper key, columns B to D hold the data):
| A (Key) | B (Region) | C (Product) | D (Sales) |
|---|---|---|---|
=B2&"-"&C2 gives East-Widget |
East | Widget | 1200 |
=B3&"-"&C3 gives West-Gadget |
West | Gadget | 950 |
=B4&"-"&C4 gives East-Gadget |
East | Gadget | 800 |
=B5&"-"&C5 gives West-Widget |
West | Widget | 1100 |
Look up East and Gadget:
=VLOOKUP("East-Gadget", A2:D5, 4, FALSE)
Result: 800
To read the criteria from cells instead of typing them, put Region in F2 and Product in F3:
=VLOOKUP(F2&"-"&F3, A2:D5, 4, FALSE)
Tips:
- Use a separator that never appears in your data (here a hyphen) so "A-BC" and "AB-C" cannot collide.
- Use
FALSE(exact match) as the fourth argument.TRUEneeds sorted data and can return the wrong row. - Wrap the formula in
IFERROR(..., "Not found")to replace#N/Afor missing combinations.
Method 2: INDEX and MATCH (no helper column)
Sample data in A1:C5 (headers in row 1):
| A (Region) | B (Product) | C (Sales) |
|---|---|---|
| East | Widget | 1200 |
| West | Gadget | 950 |
| East | Gadget | 800 |
| West | Widget | 1100 |
=INDEX(C2:C5, MATCH(1, (A2:A5="East")*(B2:B5="Gadget"), 0))
Result: 800
How it works: each test returns TRUE/FALSE, and multiplying them turns them into 1/0. Only the row where both tests are TRUE gives 1, and MATCH(1, ..., 0) finds that row.
- Excel 365/2021: press Enter as normal.
- Excel 2019 and earlier: confirm with Ctrl + Shift + Enter.
- Google Sheets: wrap it as
=ARRAYFORMULA(INDEX(C2:C5, MATCH(1, (A2:A5="East")*(B2:B5="Gadget"), 0))).
Method 3: XLOOKUP (Excel 365/2021)
=XLOOKUP(1, (A2:A5="East")*(B2:B5="Gadget"), C2:C5, "Not found")
Result: 800
The fourth argument is the text to show when nothing matches, so you do not need IFERROR. Google Sheets does not have XLOOKUP in every account, so use Method 2 there.
Example: look up a commission by salesperson and region
| A (Key) | B (Salesperson) | C (Region) | D (Commission) |
|---|---|---|---|
=B2&"-"&C2 gives Alice-East |
Alice | East | 5% |
=B3&"-"&C3 gives Bob-West |
Bob | West | 7% |
=B4&"-"&C4 gives Alice-West |
Alice | West | 6% |
=B5&"-"&C5 gives Carol-East |
Carol | East | 4% |
Type Alice in G2 and West in G3, then:
=VLOOKUP(G2&"-"&G3, A2:D5, 4, FALSE)
Result: 6%
Common problems
| Problem | Cause | Fix |
|---|---|---|
#N/A although the values look the same |
Extra spaces or different spelling | Wrap fields in TRIM() when building the key |
| Wrong column returned | The column number counts from the helper column | In A2:D5, Sales is column 4, not 3 |
| Helper column is not on the left | VLOOKUP searches only the first column of its range | Insert the helper column before your data |
| Leading zeros disappear (007 becomes 7) | The ID was stored as a number | Use TEXT(B2,"000") in the key and in the lookup |
| Formula returns the first match only | All three methods return the first matching row | Use FILTER (Excel 365) to return every match |
| Case does not matter | VLOOKUP, MATCH and XLOOKUP ignore case | For case-sensitive checks, use EXACT() inside the array test |
FAQ
Can VLOOKUP look up two values at once?
Not directly. It takes one lookup value and searches one column. Join the fields into one key in a helper column, or use INDEX/MATCH or XLOOKUP instead.
What is the best alternative to VLOOKUP with multiple criteria?
In Excel 365 or 2021, XLOOKUP with multiplied conditions is the shortest. In older Excel or Google Sheets, INDEX and MATCH with multiplied conditions works without a helper column.
Why do I need FALSE as the last argument?
FALSE forces an exact match. If you omit it, VLOOKUP assumes an approximate match, which requires the first column to be sorted and can return a wrong row.
How do I return all matches instead of the first one?
Use FILTER in Excel 365/2021, for example =FILTER(C2:C5, (A2:A5="East")*(B2:B5="Gadget")). In Google Sheets, FILTER works the same way.