Lookup with Multiple Criteria in Excel

VLOOKUP looks up one value in one column. Real spreadsheets rarely cooperate: you need the price for a product in a specific region, or the rate for an employee in a specific year. One key is not enough.

There are four practical ways to do this. They are not equally good, and the right one depends on which Excel version you have and whether you are allowed to add a column.

1. The helper column (works in every version)

The simplest approach is to build a single key out of your criteria and look that up.

Add a column to your data — say column A — containing:

=B2&"|"&C2

If B2 is “North” and C2 is “Widget”, A2 now reads North|Widget. Then look up the same combination:

=VLOOKUP(F2&"|"&G2, A:D, 4, FALSE)

The pipe character matters more than it looks. Without a separator, “North” & “West” and “Nor” & “thWest” both produce NorthWest — a false match that is very hard to spot later. Pick a character that cannot appear in your data.

The drawback: the helper column has to sit to the left of what you want to return, because VLOOKUP only looks right. Where the criteria come from dropdowns rather than typed cells, cascading validation lists keep the second list in step with the first. That often means inserting a column into someone else’s sheet.

2. INDEX/MATCH with boolean multiplication

This is the classic answer, and it needs no helper column and no particular column order. It is also a regular fixture in the Excel tests employers use for jobs.

=INDEX(D:D, MATCH(1, (B:B=F2)*(C:C=G2), 0))

In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. In Microsoft 365 and Excel 2021 it just works. If you have to support both, see running two versions of Excel side by side.

The trick is in the middle. (B:B=F2) produces an array of TRUE and FALSE values. Multiplying two such arrays coerces them to 1 and 0, and 1 × 1 is the only combination that yields 1. So MATCH looks for the single row where both conditions are true.

Use multiplication rather than AND(). AND collapses an entire array into one value and will silently give you the wrong row — one of the most common mistakes with this formula.

You can chain as many criteria as you need:

=INDEX(D:D, MATCH(1, (B:B=F2)*(C:C=G2)*(E:E=H2), 0))

One caveat: referencing whole columns like B:B makes Excel evaluate every row. On a large workbook this is noticeably slow. Restrict the ranges to the rows you actually use.

3. XLOOKUP (Microsoft 365 and Excel 2021)

XLOOKUP accepts arrays directly, so the same boolean trick becomes much easier to read:

=XLOOKUP(1, (B:B=F2)*(C:C=G2), D:D)

Or concatenate the criteria, which some people find clearer:

=XLOOKUP(F2&"|"&G2, B:B&"|"&C:C, D:D)

XLOOKUP has two advantages worth knowing. It takes an if_not_found argument, so you no longer need to wrap the whole thing in IFERROR:

=XLOOKUP(1, (B:B=F2)*(C:C=G2), D:D, "Not found")

And it can return an entire row or block, not just one cell, by pointing the return argument at several columns.

4. FILTER, when you expect more than one match

Every formula above returns the first match and quietly ignores the rest. If your criteria can legitimately match several rows, that is a bug waiting to happen.

=FILTER(D:D, (B:B=F2)*(C:C=G2), "No match")

FILTER spills all matching results down the sheet. It is the honest choice when you are not certain your combination is unique — and a quick way to discover that it is not.

Which one to use

• Excel 2016 or 2019, and you can add a column: the helper column. It is the fastest to calculate and the easiest for a colleague to understand six months later.
• Excel 2016 or 2019, and you cannot touch the layout: INDEX/MATCH with multiplication.
• Microsoft 365 or Excel 2021: XLOOKUP, for the readability and the built-in not-found handling.
• Several matches possible: FILTER.

Why it returns #N/A when the data looks right

Three causes account for almost every failure here.

Trailing spaces. “North ” and “North” are different strings. Wrap your criteria in TRIM() to check, and clean the source data if that turns out to be it.

Numbers stored as text. A lookup value of 2026 will not match a cell containing the text “2026”. They look identical on screen. Select the column and check whether the values align left (text) or right (number).

Array entry. In Excel 2019 and earlier, the INDEX/MATCH formula must be confirmed with Ctrl+Shift+Enter, because it is an array formula. If you pressed Enter, it returns #N/A or a single wrong result rather than an error, which is worse.

Gaps in a criteria column. Exported reports often write a region or a category once and leave the rows beneath it empty. Your concatenated key then becomes |Widget instead of North|Widget, and the lookup fails for every row in the gap while working perfectly on the first row of each group. Fill the blanks with the value above before you build the key.