How to Perform Exact Match Lookup in Excel with SUMPRODUCT

The EXACT function works as a lookup tool. However, it is case-sensitive. This function compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. In this post, EXACT will be used with SUMPRODUCT function to illustrate how EXACT function works.

Exact match lookup with SUMPRODUCT

Generic Formula

=SUMPRODUCT(--(EXACT( value , lookup_array)), amount_array)

Although SUMPRODUCT works with arrays, it does not require using array shortcut (CTRL + SHIFT + ENTER). You can complete a SUMPRODUCT formula by simply pressing ENTER after typing the formula.

Example

Considering the example below, the formula in D5 is:

=SUMPRODUCT(--(EXACT(E4,B2:B8)),C2:C8)

Figure 1 – Exact match lookup with SUMPRODUCT

In this case, standard lookups like VLOOKUP of HLOOKUP will ignore case and return the first match, which is “Apple”. However, the EXACT function will match the exact cases in the lookup_array. The result will be 29:

Figure 2– Exact match lookup with SUMPRODUCT

Explanation of formula

How this formula works:

EXACT(E4,B2:B8)

: this portion produces an array like this

{FALSE; FALSE; FALSE; FALSE;TRUE; FALSE;TRUE}

–       The double unary operator (–) is used to convert the array to 1 and 0. A new array is returned like this: {0;0;0;0;1;0;1}

You can also produce 1 and 0 arrays in this way: EXACT(E4,B2:B8)*1

–       SUMPRODUCT then simply multiples the items in two array: {0;0;0;0;1;0;1} and {16;17;12;13;18;14;11}. The returned array is: {0;0;0;0;18;0;11}. Finally, it sums all the values and returns 28.

Note:

  • This formula will only work with numeric values. SUMPRODUCT doesn’t retrieve text.
  • All arrays in SUMPRODUCT formula must have the same number of rows and columns otherwise you will get the #VALUE! error

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar