When you have a database with case-sensitive values, you might need to match those. Excel has some amazing formulas to match case-sensitive values. The article will describe the possible ways to match case-sensitive values in Excel with suitable examples and proper illustrations.

**How to Find Case Sensitive Match in Excel: ****7 Ways **

Assuming that we have the following sample dataset which we are going to use in the next sections.

You can see that there are two columns containing **ID** and **Name **of people working in a company. In addition, notice that the database contains some case-sensitive values as well. Now, we will demonstrate how to match case-sensitive values in Excel through **7** different formulas.

**1. Using EXACT Function**

The shortest formula uses the the** EXACT **function. This function matches cells with exactness. It takes two texts to compare and give the result as Boolean **True **or **False.**

Let’s say we want to match values in the cell** C8** and cell** C10** of the dataset.

The formula for the given dataset will be:

`=EXACT(C8,C10)`

The result shows **FALSE** though the value matches due to the difference in the case.

You can also check for other values. Let’s say we want to compare the cells, **C5 **and **C7. **

The formula will look like:

`=EXACT(C5,C7)`

Due to case-sensitive issues, the result will also be false for this one. Observe the result below.

**2. Applying Nested OR and EXACT Functions**

Furthermore, you can use nested **OR **and **EXACT **formulas to match values in a range.

Here, we have created a new column for checker values. Then compare the values in this new column with values in the dataset.

The formula for this will be:

`=OR(EXACT(D5,C10))`

**or**,

`=OR(EXACT(D6,C8))`

**Notice**: *The checker value does not match with any of the values in the range and so shows FALSE.*

*Now if we change the checker value with exact cases you can observe that the result shows TRUE. The results are shown below.*

**🔎**** Formula Explanation**

The **TEXT f**unction takes two texts to compare and gives the result as Boolean **True **or **False.**

And the **OR **function takes the logic from where any of the logic needs to be fulfilled to get a result as Boolean **TRUE**. Unless it shows Boolean **False**.

**3. Using ISNUMBER with FIND and IF Functions**

You can use the nested **ISNUMBER** and **FIND** formula to match whether a cell value contains a particular character or not. The** FIND **function returns the starting position of one text string with another text string. It is case-sensitive by nature. And the **ISNUMBER **function returns whether a value is a number and returns **TRUE **or **FALSE**.

Let’s check for **T** and **j** in dataset cell **C7**.

The formula is:

`=ISNUMBER(FIND("T",C7))`

**or**

`=ISNUMBER(FIND("j",C7))`

The result shows **TRUE** and **FALSE** respectively.

**4. Combining IF with ISNUMBER and FIND Functions**

You can add the** IF **function with the formula stated in **Method 3** if you want to get result as a remark instead of Boolean ones.

Now, let’s check for **R** and **a** in Dataset cell **C10**.

Then, the formula becomes,

`=IF(ISNUMBER(FIND("R",C10)),"MATCHED","Not MATCHED")`

**or**

`=IF(ISNUMBER(FIND("a",C10)),"MATCHED","Not MATCHED")`

The result is perfectly working as you can observe in the above pictures.

**🔎**** Formula Explanation**

The **FIND **function returns the starting position of one text string with another text string. It is case-sensitive by nature. And **ISNUMBER f**unction returns whether a value is a number and returns **TRUE** or **FALSE**.

Finally, the **IF** function checks whether the condition is met and returns **MATCHED**, else it returns **Not MATCHED**.

**5. Using Nested SUMPRODUCT and EXACT Functions**

We can combine The **SUMPRODUCT** and **EXACT** functions to create a nested formula to match case-sensitive values. Nevertheless, it is worth mentioning that the **SUMPRODUCT** function can only return numbers and not text.

Let us modify our dataset to have only numbers in the** ID** column.

The formula for this will be:

`=SUMPRODUCT(--EXACT(C7,C5:C10),B5:B10)`

**🔎**** Formula Explanation**

The **EXACT **function takes two texts to compare and gives the result as Boolean **True **or **False**.

**EXACT(C7,C5:C10) **returns => **{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}**

The **SUMPRODUCT **function takes arrays to give the arithmetic sum of the corresponding arrays.

**SUMPRODUCT(–EXACT(C7,C5:C10),B5:B10) = SUMPRODUCT(–{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{1100;1101;1102;1103;1104;1105}) = 1102**

So, Final Output => **1102**

**or**,

`=SUMPRODUCT(--EXACT(C5,C5:C10),B5:B10)`

for getting results for cells **C7** and **C5** in the dataset.

You can see the result shows for **JANET** and **Janet** correctly. Hence, it works for case-sensitive values.

*Note:**The*

**double minus (–)**sign is used for the forced conversion of Boolean values to numerical ones.**6. Applying INDEX and MATCH Functions**

Apart from these, the formula with nested** INDEX** and **MATCH** functions can be used to match case-sensitive values in Excel. This will return the value if matched exactly.

Let us check for “JANET” and “Zara” from the dataset.

The formula is:

`=INDEX(C5:C10, MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0))`

**🔎**** Formula Explanation**

**EXACT(C7, C5:C10)** returns => **{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}**

**INDEX(EXACT(C7, C5:C10), )** returns => **{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}**

**MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0)** returns => **MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE})** => **3**

**INDEX(C5:C10, MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0))** =** INDEX(C5:C10, 3)** = **JANET**

So, Final Output =>** JANET**

Similarly,

`=INDEX(C5:C10, MATCH(TRUE, INDEX(EXACT(C8, C5:C10), ), 0))`

The result finds the exact match for both the values and so provides the values in return.

**7. Utilizing LOOKUP Function**

Last but not least, the **LOOKUP** function also works to match values. But those do not work for case-sensitive issues. However, the** LOOKUP** function can be nested with** EXACT** and **IF** functions to get results for the match of case-sensitive values. There are mainly two types of** LOOKUP **functions that worked fine in this case. Those are **VLOOKUP** and **HLOOKUP** functions. We will learn how to use them in this case from below.

**7.1. Using VLOOKUP Function**

Let’s consider the value **ZARA** to match using the **VLOOKUP** function.

The formula will be:

`=IF(EXACT("ZARA",VLOOKUP("ZARA",C5:C10,1)) = TRUE, "Found", "Not Found")`

**🔎**** Formula Explanation**

The **VLOOKUP **function looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. By default, the table must be in ascending order.

**VLOOKUP(“ZARA”,C5:C10,1))** returns => **“ZARA”**

**EXACT(“ZARA”,VLOOKUP(“ZARA”,C5:C10,1)) = TRUE **becomes **EXACT(“ZARA”,”ZARA”) = TRUE **and returns => **TRUE**

Finally,** IF(EXACT(“ZARA”,VLOOKUP(“ZARA”,C5:C10,1)) **becomes** TRUE, “Found”, “Not Found”)IF(TRUE, “Found”, “Not Found”) **and returns =>** Found**

So, the result shows “**Found**” since the value is matched in the range of data.

Now, if we change the case form in the value the result will show “**Not Found**”. The result is shown below.

**7.2. Using HLOOKUP Function**

Subsequently, the **HLOOKUP** function can also be used for the purpose in a similar manner. Both **VLOOKUP **and **HLOOKUP **work similarly. The only difference is that **VLOOKUP** looks up in columns and **HLOOKUP** in rows. Thus, the letters H and V stand for Horizontal and Vertical respectively.

The formula for suppose **Janet** :

`=IF(EXACT("Janet",HLOOKUP("Janet",C5:C10,1)) = TRUE, "Found", "Not Found")`

The result works perfectly.

Again, if we change the case form of the text the result will be the opposite. See the picture below for matching **JaNet**

You won’t find the result as the case doesn’t match.

**Download Practice Workbook**

For practice, you can download the workbook from the link below.

**Conclusion**

The article demonstrated 6 amazing formulas to match case-sensitive values in Excel. The formulas use functions like **EXACT**,** IF**, **SUMPRODUCT**, **VLOOKUP, **and so on. Hopefully, the article was helpful to you. Anyway, if you have any queries you can ask in the comment section.

**<< Go Back to | Excel Match | Learn Excel**