INDEX MATCH with multiple criteria in Excel

Why a normal lookup fails here

A rate card has no single unique column. Northway appears four times, zone 2 five times and Express four times. VLOOKUP or a plain MATCH(B12,A2:A10,0) stops at the first Northway row, which is the zone 1 Standard rate of 1.10, and returns it without any warning. Only the combination of carrier, zone and service is unique, so the lookup has to test all three columns at once.

How MATCH(1, (…)*(…), 0) works

(A2:A10=B12) compares every carrier with Northway and returns nine TRUE/FALSE values. The zone and the service tests do the same. Multiplying the three arrays turns TRUE into 1 and FALSE into 0, so the product is 1 only in a row where carrier and zone and service match:

{0;0;0;0;0;0;0;1;0}

MATCH(1,that array,0) returns the position of the 1, here 8 (row 8 of the table, which is sheet row 9), and INDEX(D2:D10,8) returns the rate in that position: 2.75. A fourth criterion is simply another *(range=value). For OR logic between two values, add instead of multiply: ((C2:C10="Express")+(C2:C10="Overnight")>0).

Worked example: a freight rate card with three lookup criteria

Rows 2 to 10 are the rate card, rows 12 to 15 the inputs. Every method in rows 17 to 21 returns the same rate. Rows 27 to 29 look up a combination that does not exist. The carriers and rates are fictional.

Worked example: a freight rate card with three lookup criteria

	A	B	C	D
1	Carrier	Zone	Service	Rate per kg
2	Swiftline	1	Standard	1.20
3	Swiftline	1	Express	2.10
4	Swiftline	2	Standard	1.55
5	Swiftline	2	Express	2.60
6	Northway	1	Standard	1.10
7	Northway	1	Express	2.25
8	Northway	2	Standard	1.48
9	Northway	2	Express	2.75
10	Coastal	2	Standard	1.39
11
12	Carrier	Northway
13	Zone	2
14	Service	Express
15	Weight (kg)	340
16	Lookup method			Result
17	INDEX/MATCH, criteria multiplied			2.75
18	XLOOKUP on joined arrays			2.75
19	XLOOKUP(1, criteria multiplied)			2.75
20	INDEX/MATCH on a joined key, no Ctrl+Shift+Enter			2.75
21	SUMIFS (numbers only, see the traps)			2.75
22	Position found by MATCH			8
23	Matching rows (COUNTIFS, must be 1)			1
24	Freight = rate × weight			935.00
25
26	Not found: Coastal, zone 1, Express
27	XLOOKUP with if_not_found			No rate
28	SUMIFS returns 0, not an error			0.00
29	INDEX/MATCH wrapped in IFERROR			No rate

Excel 2019 and older: Ctrl+Shift+Enter, or the INDEX trick

In Excel 365 and Excel 2021 the formula works with a normal Enter, because arrays are calculated natively. In Excel 2019 and older it is an array formula: confirm it with Ctrl+Shift+Enter, and Excel shows it in curly braces. Forget that and the result is #N/A or a wrong value. A version that works with Enter in every version joins the criteria into one key and wraps the joined array in an extra INDEX:

=INDEX(D2:D10,MATCH(B12&"|"&B13&"|"&B14,INDEX(A2:A10&"|"&B2:B10&"|"&C2:C10,0),0))

The inner INDEX(…,0) forces Excel to evaluate the joined array. The "|" separator matters: without it, carrier "AB" with zone "12" and carrier "AB1" with zone "2" would both become "AB12".

The same lookup with XLOOKUP

XLOOKUP accepts the joined arrays directly and has a built-in if_not_found argument:

=XLOOKUP(B12&"|"&B13&"|"&B14,A2:A10&"|"&B2:B10&"|"&C2:C10,D2:D10,"No rate")

The Boolean version works too: =XLOOKUP(1,(A2:A10=B12)*(B2:B10=B13)*(C2:C10=B14),D2:D10,"No rate"). Both return 2.75. Choose XLOOKUP if everyone uses Excel 365, 2021 or later, and INDEX MATCH if the file must also open in older versions. The XLOOKUP vs VLOOKUP guide compares the two in more detail.

Why SUMIFS is a risky shortcut

For a number, =SUMIFS(D2:D10,A2:A10,B12,B2:B10,B13,C2:C10,B14) also returns 2.75, and many people use it as a multiple-criteria lookup. It has two traps. If the combination does not exist, SUMIFS returns 0 instead of an error: Coastal has no zone 1 Express rate, and the shipment would be priced at zero. If the combination appears twice, it adds both rates silently. Use SUMIFS to add up values, as in the SUMIFS examples guide, and a real lookup to return one. A COUNTIFS with the same criteria is a good uniqueness check: it must return exactly 1.

Helper column or array formula?

A helper column with =A2&"|"&B2&"|"&C2 makes the key visible, can be checked for duplicates with COUNTIF and lets you use a simple MATCH or VLOOKUP. It is the easiest version for colleagues to audit and it is fast on very large tables. The array version needs no extra column and is easier to extend with a new criterion. Both are correct. Pick one per workbook and use it consistently.

Common mistakes

• Looking up one column of a multi-column key. A lookup on the carrier alone returns the first Northway rate, 1.10, without any error. Test every column that makes the row unique.
• Forgetting Ctrl+Shift+Enter in Excel 2019 and older. The array version then returns #N/A or a wrong value. Use the INDEX(…,0) variant or upgrade the formula to XLOOKUP.
• Joining keys without a separator. "AB"&"12" and "AB1"&"2" are both "AB12". Always put a character such as | between the parts.
• Numbers stored as text. If the zones are "2" as text and the input is the number 2, B2:B10=B13 is FALSE in every row. Make both sides the same type.
• Ranges of different sizes. A2:A10 with B2:B11 returns #VALUE! or a shifted result. Use the same rows everywhere, or an Excel Table.
• Using SUMIFS as a lookup. A missing combination returns 0 and a duplicate is added twice. Check uniqueness with COUNTIFS and use INDEX MATCH or XLOOKUP for the value.