Excel Lookup with Multiple Criteria: Matching Two Columns at Once

By the XLsheetAI Team · Updated August 14, 2026 · 9 min read

The one-sentence answer

Excel has no lookup function that takes two criteria, so you either build one combined key out of your two columns and look that up, or use an array-based function like FILTER that can test both columns at once. Everything below is the detail behind that choice: which method suits which Excel version, the separator bug that silently returns wrong rows, and why the popular SUMIFS suggestion is a trap.

Why your lookup keeps returning the first row

The situation is always the same shape. Your price table has a row per product and per region, so "Widget" appears four times with four different prices. VLOOKUP and XLOOKUP both scan a single column and stop at the first match, so they hand back the price for whichever region happens to be listed first. Nothing errors. The number just quietly belongs to the wrong row.

This is not a bug to work around, it is the function's design: the lookup value is one value and the lookup array is one column. There is no argument to add. The fix is to change what you are matching against, not to find a better function.

Method 1: the helper column

The oldest answer and still the most robust. Add a column to your source table that glues the two keys together, do the same on the lookup side, and search that.

Source table, column D:
=A2&"|"&B2          → Widget|North

Your lookup:
=XLOOKUP(F2&"|"&G2, D2:D99, C2:C99, "Not found")

The separator is not optional. Joining "AB" and "1" gives AB1. So does joining "A" and "B1". Two genuinely different pairs collapse into the same key, and the lookup happily returns a row that has nothing to do with your question. Pick a character that cannot occur in your data — a pipe, a caret, or a tilde — and use it consistently on both sides.

The helper column has a reputation as the beginner's answer, which undersells it. It works in every version of Excel and in Google Sheets, it recalculates quickly because each row is a simple concatenation, and when a lookup returns nothing you can see the keys sitting in a column and spot the mismatch with your eyes. Array formulas give you none of that.

Method 2: FILTER, on Microsoft 365

FILTER takes a range and a condition, and returns every row where the condition is true. Multiplying two conditions together is how you express and:

=FILTER(C2:C99, (A2:A99="Widget")*(B2:B99="North"))

Each comparison produces an array of TRUE and FALSE. Multiplying them coerces both to 1 and 0, so a row survives only when both tests return 1. Use + instead of * and you get or — any row matching either condition.

If no row matches, FILTER returns #CALC! rather than #N/A. When an empty result is legitimate, supply the third argument rather than wrapping the formula in IFERROR:

=FILTER(C2:C99, (A2:A99="Widget")*(B2:B99="North"), "Not found")

The distinction matters. IFERROR catches everything, including the #VALUE! you would get from mismatched range sizes — a real bug you want to see rather than paper over.

Method 3: XLOOKUP with a multiplied array

Same idea, different function. Useful when you want exactly one value back rather than a range that spills:

=XLOOKUP(1, (A2:A99="Widget")*(B2:B99="North"), C2:C99, "Not found")

You are looking up the number 1 because TRUE*TRUE evaluates to 1 and every other combination multiplies out to 0. The lookup array is not a column of your sheet at all — it is an array computed on the fly, one element per row, containing a single 1 in the position you want.

On Excel 2019 and earlier, the same trick works with INDEX and MATCH, confirmed as an array formula with Ctrl+Shift+Enter:

=INDEX(C2:C99, MATCH(1, (A2:A99="Widget")*(B2:B99="North"), 0))

Which method to use

If the workbook is shared with anyone on Excel 2019 or older, use the helper column: FILTER and XLOOKUP simply do not exist there, and the file opens to a wall of #NAME? errors. If it is your own file on a current version, FILTER is the clearest to read six months later.

Why SUMIFS is the wrong tool here

SUMIFS accepts as many criteria pairs as you like, which is exactly why it gets suggested in every thread on this topic:

=SUMIFS(C2:C99, A2:A99, "Widget", B2:B99, "North")

When the product-and-region combination is unique, this returns the right number, and it is tempting to stop there. But it is adding, not looking up. The day a duplicate row appears — a corrected price, a second entry from a different import, a genuine second line — the formula returns the sum of both and nothing warns you. A lookup would have returned one of them; SUMIFS returns a number that exists nowhere in your data.

It also cannot return text. If the value you need is a status, a supplier name, or a date formatted as text, SUMIFS returns 0. Use it when you want a total. Use a lookup when you want a value.

Three things that break multi-criteria lookups

• Trailing spaces on one side. "North " and "North" build different keys, so the match fails with no clue as to why. Test with =LEN(B2) and clean with =TRIM(B2) before assuming the formula is wrong. This is the single most common cause, and it is invisible on screen.
• Mismatched range sizes. Every array in a FILTER or multiplied XLOOKUP must cover the same rows. Pairing A2:A99 with B2:B100 returns #VALUE!. Select the ranges by dragging from the same start row rather than typing them.
• Numbers stored as text. A region code imported as text never equals the same code typed as a number, and the comparison silently returns FALSE for every row. Left-aligned by default means text; right-aligned means number.

Stop hand-building array formulas. Describe the lookup you need in plain English and XLsheetAI writes it for your Excel version, explains each argument, and lets you practise the pattern on a real grid until it sticks.

Bottom line

There is no two-criteria lookup function, and waiting for one is the only approach that never works. Build a combined key when compatibility matters, use FILTER when it does not, and reserve SUMIFS for the times you actually want a total. Whichever you choose, check for trailing spaces first — it is the cause far more often than the formula is.

Related reading: XLOOKUP vs VLOOKUP covers which lookup function to standardise on, and removing duplicates in Excel deals with the duplicate rows that make multi-criteria lookups ambiguous in the first place.