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 top row above is the whole formula. Open any argument that holds a calculation of
its own to see the values it used. Excel works out those inner parts first, because the
function around them needs their results. Edit a cell in the grid to see every value
update.
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.
Common 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.