# Case Sensitive Lookup – Excel & Google Sheets

## Case-sensitive Lookup – Excel

This tutorial will demonstrate how to perform a case-sensitive lookup in Excel using two different methods. ## Method 1 – LOOKUP Function ### LOOKUP Function

The LOOKUP Function is used to look up an approximate match for a value in a column and returns the corresponding value from another column.

### Case-sensitive Lookup

By combining LOOKUP and EXACT, we can create a case-sensitive lookup formula that returns the corresponding value for our case-sensitive lookup. Let’s walk through an example.

We have a list of items and their corresponding prices (notice that Item ID is case-sensitive unique): Supposed we are asked to price for an item using its Item ID like so: To accomplish this, we can use LOOKUP and EXACT in a formula like so:

``=LOOKUP(TRUE,EXACT(<Lookup range>,<lookup value>),<results range>)``
``=LOOKUP(TRUE,EXACT(\$B\$2:\$B\$7,\$E\$2),\$C\$2:\$C\$7)``

Limitation: for this method to work, the values must be sorted in descending order

### How does the formula work?

The EXACT function checks the Item ID in E2 (lookup value) against the values in B2:B7 (lookup range) and returns TRUE where there is an exact match. Then the LOOKUP function returns the corresponding match in C2:C7 (results range) when the nested EXACT returns TRUE.

## Method 2 – SUMPRODUCT Function ### SUMPRODUCT Function

The SUMPRODUCT Function is used to multiply arrays of numbers, summing the resultant array.

### Case-sensitive SUMPRODUCT

Unlike the LOOKUP method, the values do not need to be sorted for this to work. We still need to combine EXACT in a formula to get the results like so:

``=SUMPRODUCT(--(EXACT(<lookup value>,<lookup range>)),<results range>)``
``=SUMPRODUCT(--(EXACT(\$E\$2,\$B\$2:\$B\$7)),\$C\$2:\$C\$7)``

Limitation: The SUMPRODUCT method will only work when the return value (not the lookup value) is numeric.

### How does the formula work?

Like LOOKUP method, the EXACT function deals with finding the case-sensitive match and returns TRUE when there is an exact match or FALSE otherwise. The “–” (known as double unary) converts TRUE to 1 and FALSE to 0. This essentially creates the first array for SUMPRODUCT to multiply with our results array:

``{0,1,0,0,0,0}*{16,4,83,45,74,23} = 4``

## Case Sensitive Lookup in Google Sheets

The Case Sensitive Lookup Function works exactly the same in Google Sheets as in Excel: 