INDEX MATCH With Multiple Criteria In Different Columns

To use INDEX MATCH with multiple criteria without building helper columns, use boolean array logic. Multiply your criteria ranges together to generate an array of 1s and 0s, and use MATCH to look up the value 1.

Excel

=INDEX(C2:C100, MATCH(1, (A2:A100=F2) * (B2:B100=G2), 0))

(Note: If using Excel 2019 or older, you must press Ctrl+Shift+Enter to execute this as an array formula).

The Syntax Breakdown

INDEX(C2:C100, ...): The return array. This is the column containing the final data you want to retrieve and display in your cell.
MATCH(1, ... , 0): The core engine. We are instructing Excel to search for the exact value of 1 within our generated boolean array, and 0 dictates an exact match.
(A2:A100=F2): Condition 1. This checks if the values in column A exactly match your first criterion in cell F2, creating an underlying memory array of TRUE and FALSE values.
* (Asterisk): The mathematical multiplication operator acting as an AND logic gate. Multiplying boolean arrays forces Excel to convert them into 1s and 0s (TRUE * TRUE = 1, TRUE * FALSE = 0).
(B2:B100=G2): Condition 2. This checks if the values in column B exactly match your second criterion in cell G2. The row where both conditions are TRUE equates to 1, which the MATCH function finds.

Real-World Example: Assigning Regional Shipping Tariffs

You are an Operations Manager calculating freight costs. Your third-party logistics provider charges different rates based on both the Destination Zone and the Transit Mode. You need to automatically pull the exact tariff rate for specific shipments.

Here is your raw Master Tariff data table:

Row	A (Destination Zone)	B (Transit Mode)	C (Tariff Rate)
2	Zone 1	Ground	$12.50
3	Zone 1	Express	$28.00
4	Zone 2	Ground	$18.75
5	Zone 2	Express	$45.00

The Application:

You have a shipment dashboard where cell F2 contains the target Zone (Zone 2) and G2 contains the target Mode (Express).

Apply the boolean INDEX MATCH formula in your dashboard’s cost column:

Excel

=INDEX(C2:C5, MATCH(1, (A2:A5=F2) * (B2:B5=G2), 0))

The Output:

The formula dynamically scans the arrays, identifies Row 5 as the only instance where both Zone 2 and Express evaluate to 1 (TRUE), and successfully returns $45.00.

Common Errors & How to Fix Them

#VALUE! Error: Your array ranges are asymmetrical, which breaks the array multiplication process. Fix: Ensure all criteria ranges and your return range have the exact same number of rows (e.g., do not mix A2:A100 with B2:B95).
Formula returns #N/A even though the data clearly matches: Invisible trailing spaces or formatting discrepancies (text vs. numbers) are causing the = check to fail. Fix: Use the TRIM() function on your raw data to remove accidental spaces, or ensure both columns are formatted as “Text” via Home > Number Format.
Formula returns an error in older Excel versions: Pre-365 Excel versions do not natively calculate dynamic arrays. Fix: Click into the formula bar and press Ctrl+Shift+Enter. Excel will automatically wrap your formula in curly brackets { }, forcing the array calculation.