Runs this calculation on the values.
- Left side
(A2:A8="Widget")- Right side
(B2:B8="West")
INDEX MATCH with multiple criteria
A two-criteria INDEX MATCH finds the row where Product is Widget and Region is West. Multiplying the two Boolean arrays leaves a single 1 for MATCH to find.
How Excel works this out
Inner to outerFind the position of a value in a range or array with Excel's MATCH function.
- Lookup value
1The value to search for. Can be a number, text, logical value, or a cell reference.- Lookup array
(A2:A8="Widget")*(B2:B8="West")The single row or column of cells to search in.- Match type
0How to match: 0 for an exact match, 1 (default) for the largest value less than or equal to lookup_value, or -1 for the smallest value greater than or equal to it.
Return the value at a given row and column position in a range with Excel's INDEX function.
- Range
C2:C8- Row number
MATCH(1,(A2:A8="Widget")*(B2:B8="West"),0)The row position within the array to return. Counts from the first row of the array. Set it to 0 to return the entire column.
Formula sample data
How this INDEX formula works
The product and region tests each produce an array. Their product is 1 only at row position 3, so MATCH returns 3 and INDEX selects C4 = 125.
The rows above are ordered from the innermost calculation to the final result. Excel has to finish an inner function before the function around it can use that value. Edit a cell in the grid to see that dependency chain update for real.
Try your own formula and data
The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.
Practice with an exercise
Here is an exercise that uses INDEX. Type the formula into a real spreadsheet and we will check your answer as you go.
INDEX-MATCH left lookup
IntermediateFind a customer's email address where email is to the LEFT of customer ID.
Open exerciseCommon mistakes with INDEX
If this formula is not doing what you expect, one of these is usually why.
Why does my multiple-criteria MATCH return #N/A?
No row satisfies every condition, so the multiplied mask contains no 1 for MATCH to find. Check each comparison on its own to see which criterion removes the expected row.
Why does this formula return the first matching row only?
MATCH stops at the first 1 in the combined mask. Use FILTER when you need every row that meets the conditions rather than one corresponding value.
Do the ranges need to be the same size?
Yes. The product, region, and return ranges must start and end on matching rows so every Boolean result lines up with the value INDEX can return.
Do I need Ctrl+Shift+Enter?
Modern Excel handles this array calculation normally. Older pre-dynamic-array versions may require confirming the formula with Ctrl+Shift+Enter.