Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles LOOKUP How to Perform Exact Match Lookup in Excel with SUMPRODUCT

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. 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc