By default, standard lookups in Excel are not case-sensitive. Both VLOOKUP and INDEX/MATCH will simply return the first match, ignoring case.
A direct way to workaround this limitation, is to use an array formula based on INDEX/MATCH with EXACT. However, if you're looking up numeric values only, SUMPRODUCT + EXACT also gives an interesting and flexible way to do a case-sensitive lookup.
In the example, we are using the following formula
Although this formula is an array formula, it does not need to be entered with Control + Shift + Enter, since SUMPRODUCT handles arrays natively.
SUMPRODUCT is designed to work with arrays, which it multiplies, then sums.
In this case, we are two arrays with SUMPRODUCT: B3:B8 and C3:C8. The trick is to run a test on the values in column B, then convert the resulting TRUE/FALSE values to 1's and 0's. We run the test with EXACT like so:
Note that the true value in position 3 is our match. Then we use the double negative (i.e. --, which is technically a "double unary") to coerce these TRUE / FALSE values into 1 and 0. The result is this array:
At this point in the calculation, the SUMPRODUCT formula looks like this:
SUMPRODUCT then simply multiples the items in each array together to produce a final array:
Which SUMPRODUCT then sums, and returns 775.
So, the gist of this formula is that the FALSE values are used to cancel out all other values. The only values that survive are those that were TRUE.
Note that because we are using SUMPRODUCT, this formula comes with a unique twist: if there are multiple matches, SUMPRODUCT will return the sum of those matches. This may or may not be what you want, so take care if you do expect multiple matches!
Remember, this formula only works for numeric values, because SUMPRODUCT does't handle text. If you want to retrieve text, use INDEX/MATCH + EXACT.
Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to a range of cells. Working from...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.