Vlookup with multiple criteria?

Yes. In Excel, use VLOOKUP with multiple criteria by joining the criteria into a helper key, then looking up the same joined values with an exact match. With source criteria in A and B, a helper key in C, return values in D, and the lookup criteria in E2 and F2, enter =A2&"|"&B2 in C2 and fill down; then use =VLOOKUP(E2&"|"&F2,$C$2:$D$100,2,FALSE). The second formula returns the value from column D for the first row matching both criteria.

Why VLOOKUP Needs a Combined Key

VLOOKUP is designed to search one lookup value in the first column of a table and return a value from another column in the same row. It does not have separate argument slots for, say, a region and a product. To make it test both, turn those two values into one lookup value, such as East|Cable, on both the data side and the search side.

The helper key is simply a new column that combines the fields you want to match. In the opening example, column A might contain a region, column B a product, column C the combined key, and column D a sales figure. VLOOKUP searches column C, then returns the corresponding figure from D. The table range must start with the key column because VLOOKUP only searches the first column of its table array.

This approach is useful in older and current Excel workbooks because it relies on familiar functions. It also makes the matching logic visible: you can inspect the helper column to see which values Excel is comparing. The trade-off is that the workbook needs an extra column, and the key must be constructed consistently in the source data and the lookup formula.

Build and Use a Helper Column

For a concrete example, suppose rows 2 through 4 contain East / Widget / 120, West / Widget / 95, and East / Cable / 40 in columns A, B, and D. Column C will hold the key. To create the key for each source row, with the source region in A2 and product in B2, enter this in C2 and copy it down through C4: =A2&"|"&B2.

If E2 contains East and F2 contains Cable, with the lookup table in C2:D4 and its first column holding the combined key, enter =VLOOKUP(E2&"|"&F2,$C$2:$D$4,2,FALSE). It returns 40, because East|Cable is the key on the third source row and the value in that row’s second column of the lookup range is 40.

The final argument, FALSE, requests an exact match. That matters for keys: an approximate match can return an unintended row, and it depends on sorted data. For a lookup based on categories or identifiers, exact matching is normally the appropriate choice. The dollar signs in the table reference keep the search range fixed if you copy the formula to other lookup rows; E2 and F2 remain relative so they can change by row.

Insert a helper column immediately to the left of the return column, or otherwise arrange the lookup range so the helper key is its first column.
Join the source criteria in the helper column and fill the formula down for every data row.
Join the corresponding lookup criteria in the VLOOKUP formula, select the key-and-result range, and use FALSE for an exact match.
Check a known record and confirm that the lookup range and column index point to the intended return field.

Adding Criteria and Choosing a Separator

You can combine more than two fields by joining another field into the key and lookup value in the same order. For example, a key could contain region, product, and year. The number of fields is not itself the obstacle; the important requirements are that both sides use the same order and that the combined key distinguishes the records you mean to distinguish.

A separator such as a vertical bar makes joined values easier to read and helps prevent ambiguous combinations. Without a separator, East plus Cable and Eas plus tCable could produce the same text. A separator also helps distinguish combinations such as AB plus C from A plus BC. However, if the actual field values can contain the separator, different combinations can still produce the same key. Choose a character that is excluded from the data, or clean and standardize the source fields before building the key.

Concatenation converts values into text for the key. This can matter when one source field contains the number 7 and another contains text that looks like 7: the joined results may be indistinguishable. Decide how each field should be represented, especially for dates, codes with leading zeros, and numeric identifiers. If leading zeros are meaningful, store or format the identifier consistently as text rather than allowing Excel to treat it as a number in some rows and text in others.

Duplicates, Missing Matches, and Data Quality

VLOOKUP returns the first matching key it encounters, reading from the top of the lookup range down. If the combined criteria occur on multiple rows, the formula does not report that the key is duplicated; it returns the first matching row’s result. That is appropriate when the key is meant to be unique, but it can conceal a data problem when each combination should identify exactly one record. Sort order can change which duplicate appears first, so do not rely on row order to select a particular duplicate.

Before trusting the result, check that the criteria really define one record. For example, region plus product may not identify a unique sales entry if the source has a separate row for every month. Adding month or year to the key may be necessary. Also check for leading or trailing spaces, inconsistent spelling, and different date or number representations. Two cells that look alike on screen may not contain identical underlying values.

If a lookup returns #N/A, likely causes include a misspelled criterion, a missing source row, an inconsistent separator, or a number-versus-text mismatch. Inspect the generated helper key and the lookup criteria side by side. If you want a friendlier message instead of #N/A, wrap the lookup in IFNA. With the same lookup layout as above—criteria in E2 and F2, keys in C2:C100, and return values in D2:D100—use =IFNA(VLOOKUP(E2&"|"&F2,$C$2:$D$100,2,FALSE),"Not found"). This displays Not found only when the lookup produces #N/A; it does not conceal unrelated formula errors.

Common VLOOKUP Mistakes to Avoid

Putting the key outside the first column of the selected range: VLOOKUP searches only the leftmost column of its table array. If the range begins in A while the helper key is in C, it will search A, not C.
Using the wrong column index: The index counts columns within the selected range, not worksheet column letters. In C:D, the return field in D has index 2.
Leaving out FALSE: The fourth argument controls match type. Use FALSE when the combined key must match exactly.
Building the two keys differently: A different field order, separator, or text conversion means the lookup value will not equal the helper key.
Assuming the first result proves uniqueness: VLOOKUP does not identify or count duplicate keys. Validate uniqueness separately when duplicates would be an error.

VLOOKUP Compared with XLOOKUP

If you have Microsoft 365 or Excel 2021, XLOOKUP can test multiple criteria directly without adding a helper column. With source criteria in A2:A100 and B2:B100, the lookup values in E2 and F2, and return values in D2:D100, use =XLOOKUP(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),$D$2:$D$100,"Not found"). Each comparison produces TRUE or FALSE values; multiplying the comparisons makes a row that satisfies both criteria evaluate to 1. XLOOKUP finds that 1 and returns the value from column D.

XLOOKUP is often easier when the return column is to the left of a lookup column, or when you want to avoid maintaining a helper field. It also includes a not-found argument. It still returns the first match, however, so it does not solve duplicate-key ambiguity by itself. The formula above uses equal-length ranges, which is important: the criteria arrays and return array must cover corresponding rows.

VLOOKUP remains a reasonable choice when compatibility with older Excel versions matters, when a visible key is useful for auditing, or when a workbook already uses this structure. XLOOKUP is available in Microsoft 365 and Excel 2021, but not in some older perpetual versions. Excel for the web and Excel for Mac support many modern Excel functions, though available features can vary by update channel and license. The helper-column method is the more portable option across older editions.

Frequently Asked Questions

Can VLOOKUP use two criteria without a helper column?

Not as two independent criteria in its standard syntax. VLOOKUP accepts one lookup value, so the common compatible method is to combine the fields in a helper column. Newer Excel versions can use functions such as XLOOKUP to compare multiple columns directly.

Does VLOOKUP with multiple criteria return every matching row?

No. VLOOKUP returns the first matching row only. If you need all matching records, use a filtering approach such as FILTER in Microsoft 365 or Excel 2021, or use another method suited to your Excel version and data model.

Why does my multiple-criteria VLOOKUP show #N/A?

Most often, the combined lookup value is not identical to any helper key. Check the order of the criteria, separator, spaces, spelling, and whether the fields have consistent types and formatting. Confirm that the helper key is the first column of the selected range and that the formula uses exact matching.

Can I use a space instead of a separator?

Yes, provided the separator creates an unambiguous key for your data and is used identically on both sides. A visible character that does not occur in the field values is usually safer than a space, since text fields may already contain spaces.

What happens when two rows have the same combined key?

VLOOKUP returns the first one in the lookup range. If each combination should be unique, investigate the duplicate rather than assuming the returned row is the correct one. Add another criterion if it represents a meaningful distinction between records.

Choosing the Right Method

Use a helper key with VLOOKUP when you need broad version compatibility, want an auditable key column, or are working with an established lookup table. Use XLOOKUP with separate comparisons when your Excel version supports it and you prefer not to alter the source layout. In either case, define what makes a record unique, use exact matching, and test the formula against a known example. Those checks matter more than the particular lookup function: a formula can return a valid-looking value while still matching the wrong duplicate or an incorrectly constructed key.

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.