SUMPRODUCT case-sensitive lookup

To perform a case-sensitive lookup with the SUMPRODUCT function, you can use the EXACT function. In the example, the formula in G5, copied down, is: =SUMPRODUCT(--EXACT(F5,data[Color]),data[Qty]) Where "data" is an Excel Table in the range B5:D15. The result is the quantity associated with "Red" (5), excluding "RED". In cell G6, the same formula copied down returns 9, matching "RED". Note that if there is more than one match (i.e. exact match duplicate colors), SUMPRODUCT will sum matching results. Note: While this is an array formula, it does not need to be entered with Control + Shift + Enter in older versions of Excel, since SUMPRODUCT handles arrays natively. In modern versions of Excel that support dynamic array formulas, there is no advantage to using SUMPRODUCT to solve a problem like this. Instead, switch to XLOOKUP or INDEX and MATCH.

April 14, 2025 · 1 min · 114 words · Elizabeth Rodriguez

Explanation

TheSUMPRODUCT functionmultipliesarraystogether and returns the sum of products.

If only one array is supplied, SUMPRODUCT will simply sum the items in the array.

We can do this with theEXACT function, which is designed to perform a case-sensitive comparison of text strings.

Excel formula: INDEX and MATCH case-sensitive

Every other value is now a zero.

This will work perfectly as a filter.

Turning back to the formula in G5, we can see how this works.

Excel formula: VLOOKUP case-sensitive

The only values that survive are those associated with TRUE, and the final result is 5.

Remember, this formula only works for numeric values, because SUMPRODUCT doesn’t handle text.

The advantage of this approach is that we no longer need to use the double negative.

Excel formula: XLOOKUP case-sensitive

Excel SUMPRODUCT function

Excel EXACT function

Explanation#

Explanation