How to lookup exact match using SUMPRODUCT function in Excel

In this article, we will learn How to look up exact matches using the SUMPRODUCT function in Excel.

Scenario:

In simple words, while working with data tables, sometimes we need to lookup values with case sensitive letters In Excel. Lookup functions like VLOOKUP or MATCH functions don't find the exact match as these are not case sensitive functions. Assume working on data where 2 names are differentiated on the basis of case sensitivity i.e Jerry & JERRY are two different first names.  You can perform tasks like operations over multiple ranges using the formula explained below.

How to solve the problem?

For this problem, we will be required to use the SUMPRODUCT function & EXACT function. Now we will make a formula out of the function. Here we are given a table and we need to find values corresponding to the exact match in the table in Excel. SUMPRODUCT function returns the SUM of corresponding TRUE values (as 1) and ignores values corresponding to FALSE values (as 0) in the returned array

Generic formula:

= SUMPRODUCT ( -- ( EXACT ( lookup_value , lookup_array ) ) , (return_array ) )

lookup_value : value to lookup.

lookup_array : lookup array for lookup value.

return_array : array, returned value corresponding to lookup array.

-- : Negation (--) char changes values, TRUEs or 1s to FALSEs or 0s and FALSEs or 0s to TRUEs or 1s.

Example:

All of these might be confusing to understand. So, let's test this formula via running it on the example shown below. Here we have data having Quantity & Price of products received on the dates. If any product is bought Twice it has 2 names. Here we need to find the count of items for all essentials items. So for that we have assigned 2 lists as the received list and essentials required in a column for the formula. Now we will use the below formula to get the Quantity of the "milk" from the table.

Use the Formula:

= SUMPRODUCT ( -- ( EXACT ( F5 , B3:B11 ) ) , ( C3:C11 ) )

F6 : look up value in F6 cell

B3:B11 : look up array is Items

C3:C11 : return array is Quantity

-- : Negation (--) char changes values, TRUEs or 1s to FALSEs or 0s and FALSEs or 0s to TRUEs or 1s.

Here the range is given as cell reference. Press Enter to get the Quantity.

As you can see, 58 is the quantity corresponding to the value "milk" in B5 cell not the "Milk" in B9 cell. You will have to look for value"Milk" if you need the quantity "54" in C9 cell.

You can look up price corresponding to the value "milk" just by using the formula shown below.

Use the Formula:

= SUMPRODUCT ( -- ( EXACT ( F5 , B3:B11 ) ) , ( D3:D11 ) )

F6 : look up value in F6 cell

B3:B11 : look up array is Items

D3:D11 : return array is Price

Here we have the 58 is the Price corresponding to the value "milk" in B5 cell not the "Milk" in B9 cell. You will have to look for value"Milk" if you need the Price "15.58" in D9 cell.

Unique values in look up array

Similarly, you can find the unique values using the formula. Here the lookup value "EGGS" used as example.

In the image shown above, we got the values adjusting the same formula. But the problem occurs if it has a unique value and you are not looking up the proper lookup value. Like here using the value "Bread" in formula to lookup in table.

The  formula returns 0 as quantity and Price but this doesn't mean that the quantity and Price of Bread is 0. Its just that the formula returns 0 as value when value you are looking for is not found. So use this formula only to look up the exact match.

You can also perform lookup exact matches using INDEX and MATCH function in Excel. Learn more about How to do Case Sensitive Lookup using INDEX & MATCH function in Excel. You can also look up for the partial matches using the wildcards in Excel .

Here are some observational notes shown below.

Notes:

  1. The formula only works with only to look up the exact match.
  2. The SUMPRODUCT function considers non-numeric values as 0s.
  3. The SUMPRODUCT function considers logic value TRUE as 1 and False as 0.
  4. The argument array must be the same size else the function returns an error.
  5. SUMPRODUCT function returns the value corresponding to the TRUE values in the returned array after taking individual products in the corresponding array.
  6. Operators like equals to ( = ), less than equal to ( <= ), greater than ( > ) or not equals to ( <> ) can be performed within a formula applied, with numbers only.

Hope this article about How to lookup exact match using SUMPRODUCT function in Excel is explanatory. Find more articles on Counting formulas here. If you liked our blogs, share it with your fristarts on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Related Articles

How to use the SUMPRODUCT function in Excel: Returns the SUM after multiplication of values in multiple arrays in excel.

Use INDEX and MATCH to Lookup Value : The INDEX-MATCH formula is used to lookup dynamically and precisely a value in given table.  This is an alternative to the VLOOKUP function and it overcomes the shortcomings of the VLOOKUP function.

Sum by OFFSET groups in Rows and Columns : The OFFSET function can be used to sum group of cells dynamically. These groups can be anywhere in the sheet.

How to Retrieve Every Nth Value in a Range in Excel : Using the OFFSET function of Excel we can retrieve values from alternating rows or columns. This formula takes help of the ROW function to alternate through rows and COLUMN function to alternate in columns.

How to use OFFSET Function in Excel : The OFFSET function is a powerful Excel function that is underrated by many users. But experts know the power of the OFFSET function and how can we use this function to do some magical tasks in Excel formula. Here are the basics of the OFFSET function.

How to use wildcards in excel : Count cells matching phrases using the wildcards in excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets in Excel. 

How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values in Excel. COUNTIF function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values with specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube