INDEX/MATCH with SUMIF in Excel: two patterns that solve different problems

INDEX/MATCH with SUMIF sounds like one formula pattern, but people usually mean one of two different tasks:

1. sum rows that match a condition while choosing the sum column from a header; or
2. return one value from the row where two conditions match.

Those tasks need different formulas. Separating them first prevents a formula that looks plausible but answers the wrong question.

Pattern 1: choose the SUMIF column from a header

Suppose:

• column A contains a region;
• columns B:D contain monthly amounts;
• cell E2 contains the region to sum;
• cell F1 contains the month header to select.

Use:

=SUMIF($A$2:$A$100,$E2,INDEX($B$2:$D$100,0,MATCH(F$1,$B$1:$D$1,0)))

Enter fullscreen mode Exit fullscreen mode

Read it from the inside out:

• MATCH(F$1,$B$1:$D$1,0) finds the exact month column.
• INDEX($B$2:$D$100,0,...) returns that complete column as the sum range.
• SUMIF($A$2:$A$100,$E2,...) adds values only for rows whose region matches E2.

The final 0 in MATCH matters. It requests an exact header match. Without it, an unsorted header row can produce a believable but incorrect result.

If you want a compact comparison of SUMIF, SUMIFS, XLOOKUP, VLOOKUP, and INDEX/MATCH, use this formula-pattern guide. It keeps the examples, assumptions, and common argument-order trap together.

Assumptions to make visible

• $A$2:$A$100 and the selected sum column cover the same rows.
• The month header in F1 appears once in B1:D1. MATCH returns the first duplicate.
• Amount cells are numeric rather than numbers stored as text.
• Full-column array ranges are avoided so recalculation stays bounded.

If the sum has two row conditions — for example region and product — use SUMIFS instead of forcing both conditions through INDEX/MATCH:

=SUMIFS($D$2:$D$100,$A$2:$A$100,$F2,$B$2:$B$100,$G2)

Enter fullscreen mode Exit fullscreen mode

SUMIF puts the optional sum range last. SUMIFS puts the sum range first. Mixing those argument orders is a common source of incorrect formulas.

Pattern 2: return one row with two criteria

Now suppose:

• column A contains account IDs;
• column B contains months;
• column D contains the value to return;
• F2 and G2 contain the requested account and month.

This is a lookup, not an aggregation. In current Excel, XLOOKUP can express it directly:

=XLOOKUP(1,($A$2:$A$100=$F2)*($B$2:$B$100=$G2),$D$2:$D$100,"Not found")

Enter fullscreen mode Exit fullscreen mode

For an older-workbook INDEX/MATCH pattern:

=INDEX($D$2:$D$100,MATCH(1,($A$2:$A$100=$F2)*($B$2:$B$100=$G2),0))

Enter fullscreen mode Exit fullscreen mode

Each comparison produces TRUE or FALSE. Multiplication turns the row where both tests are true into 1, and the lookup searches for that exact value.

Older perpetual Excel releases may require confirming the INDEX/MATCH version as an array formula. Current Microsoft 365 evaluates it directly.

Check duplicates before trusting the result

Both lookup formulas return the first matching row. They do not warn that the same account-and-month pair appears twice.

If duplicates are valid, decide whether the task should:

• return all matches with FILTER;
• add them with SUMIFS; or
• reject the duplicate key in the source data.

That decision belongs in the task definition, not in a hidden formula assumption.

A short decision rule

• One condition and a sum: SUMIF.
• Several conditions and a sum: SUMIFS.
• One matching record in current Excel: XLOOKUP.
• One matching record with older-version compatibility: INDEX/MATCH.
• A header chooses which column to sum: SUMIF with an INDEX/MATCH sum range.

Before filling any formula down, test one known match, one known miss, and one duplicate case. A formula is only useful when its assumptions are as visible as its result.