Compare & combine files · 5 min read

XLOOKUP with Multiple Criteria in Excel: Match Two Columns

A product can have different rates in different regions. Looking up the product alone may return a valid-looking rate from the wrong region. Test both fields on the same source row, then decide whether the result must be unique.

JHSOFT editorialUpdated September 9, 2026

WORKED EXAMPLESynthetic data

Enter in A1:C7
region	product_id	rate
East	P101	12
West	P101	13
East	P102	0
East	P101	15
[empty]	P101	99
East	P103	[empty]

Selectors: F1=region, G1=product_id
F2=East, G2=P101

East + P101 has two matches: rates 12 and 15. The basic XLOOKUP returns 12; the guarded result returns Multiple matches. West + P101 returns 13, East + P102 returns 0, East + P103 returns Missing rate, and an empty selector requests both criteria.

Download regional-rates.csv ↓

Prepare the sample and check your Excel version

Use Excel for Microsoft 365, Excel 2021, or Excel 2024. XLOOKUP is unavailable in Excel 2016 and 2019. A workbook can contain a formula created by another user without your version being able to calculate it. The formulas here use commas as argument separators.

On an empty worksheet, enter the six records under region, product_id, and rate in A1:C7. Leave the marked cells empty; do not enter placeholder text. Enter region in F1, product_id in G1, matches in H1, and result in I1. Put East in F2 and P101 in G2. Keep rates numeric, including the genuine zero for P102.

Enter the sample in A1:C7, with no extra header rows inside the data.
Use F2 and G2 as the two selector cells; they are separate from the source table.
Leave an empty area below row 10 for the optional multirow result.

Build a row-by-row AND test

Enter the basic formula in I2. The region comparison and product comparison each produce a six-item array. Their positions refer to the same six records. Multiplying them gives zero when either condition fails and one when both succeed.

For East and P101, the mask is 1, 0, 0, 1, 0, 0. XLOOKUP searches that mask for 1 and returns the rate at the first such position: 12. The last argument, 0, states the intended exact match explicitly. Keep all three ranges the same length and aligned to the same first and last rows.

Basic formula in I2

=IF(OR(F2="",G2=""),"Enter both criteria",XLOOKUP(1,($A$2:$A$7=F2)*($B$2:$B$7=G2),$C$2:$C$7,"Not found",0))

Both East P101 records qualify while the West record fails the region criterion. — Illustrated example. Synthetic selection diagram. Two rows qualify, so the key pair is not unique.

Count the candidates instead of trusting the first result

Enter the count formula below in H2. It returns 2 for the starting selectors. Before joining rates into a report, decide whether two matching records are allowed. They might represent effective dates, contract tiers, or an accidental duplicate; none of those meanings is encoded in the formula.

To require one source row, replace I2 with the guarded formula in the next section. It uses H2 as a visible audit value. This makes a duplicate exception easy to filter and avoids silently choosing whichever record happens to be first after someone sorts the data.

Candidate count in H2

=IF(OR(F2="",G2=""),"",SUMPRODUCT(--($A$2:$A$7=F2),--($B$2:$B$7=G2)))

Distinguish duplicates, missing records, and missing rates

The guarded formula returns a rate only when the pair has exactly one matching record. A source cell that is empty or evaluates to an empty string becomes Missing rate. A recorded zero remains numeric zero. This is useful when zero is a valid rate and an absent value needs investigation.

Neither IF nor the match count cleans source errors. Correct error cells and confirm matching data types before using this formula across a large report. Blank criteria are treated as incomplete input in this workflow, even though a source record itself may have a blank region.

F2 region	G2 product	H2 matches	Guarded I2
East	P101	2	Multiple matches
West	P101	1	13
East	P102	1	0
East	P103	1	Missing rate
West	P999	0	Not found
[empty]	P101	[empty]	Enter both criteria

Replace I2 with this formula after adding H2

=IF(OR(F2="",G2=""),"Enter both criteria",IF(H2=0,"Not found",IF(H2>1,"Multiple matches",XLOOKUP(1,($A$2:$A$7=F2)*($B$2:$B$7=G2),IF($C$2:$C$7="","Missing rate",$C$2:$C$7),"Not found",0))))

Return every match when duplicates are meaningful

For a detail report, put the FILTER formula in A10, outside an Excel Table, with A10:C15 empty. For the starting selectors it returns the two East/P101 records and their rates, 12 and 15. A spill error means something occupies the output area; inspect it before clearing anything.

FILTER answers a different question from a scalar lookup: which records qualify, rather than which single value should be assigned. Its raw output can also display a blank numeric source cell as zero, so review missing values separately before interpreting that detail output.

Optional all-match report in A10

=IF(OR(F2="",G2=""),"Enter both criteria",FILTER(A2:C7,(A2:A7=F2)*(B2:B7=G2),"Not found"))

A guarded scalar lookup flags duplicates while FILTER lists both qualifying rows. — Illustrated example. Synthetic output comparison. A lookup and a detail report serve different purposes.

Use meaningful matching rules

Separate comparisons avoid ambiguous concatenations: AB joined to C and A joined to BC both create ABC if no separator is used. They also avoid having to guarantee that a chosen separator never appears in the source. Add another parenthesized comparison for a third criterion, and extend the audit count consistently.

Ordinary text equality is case-insensitive. If case distinguishes identifiers, replace the relevant comparison with EXACT and apply the same rule to the audit. Spaces and text-versus-number differences still need a data decision. A date criterion that means a whole day needs a start-inclusive, next-day-exclusive interval when the source contains times.

Reverse search finds the last matching position; it does not calculate the newest date. If your records have effective dates, define that rule first and resolve tied dates. Keep ranges bounded for this array calculation, and update every source range together when adding records.

Common questions

Does XLOOKUP with two criteria return two results?

No. Two criteria describe which row qualifies. The basic formula still returns one rate from the first qualifying row. FILTER returns all qualifying records; the guarded formula flags a pair that is not unique.

Why do I get #VALUE! or #NAME?

Check that the source ranges have the same dimensions and contain no errors. #NAME? can indicate an unsupported XLOOKUP version, a misspelled function, or invalid formula syntax. Use straight quotation marks and your local argument separator.

Can an empty selector mean all regions?

Not in this example: it means incomplete input. Optional criteria require different logic and should be labeled clearly so a blank cannot accidentally broaden a lookup. Keep the required-field rule unless your report explicitly needs an all-regions selection.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →