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 6 formulas to match case-sensitive values in Excel with suitable examples and proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

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

**6 Formulas to Find Case-sensitive Match in Excel**

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 6 different formulas.

**1. Using the EXACT Function to Find Case Sensitive Match in Excel**

The shortest formula uses the** EXACT** function. This function matches cells with exactness.

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 be false for this one as well. Observe the result below.

**Formula Explanation:**

The syntax of the formula:

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False.**

**Read More:**** How to Match Data in Excel from 2 Worksheets**

**2. Using Nested OR and EXACT Functions ****to Find Case Sensitive Match in Excel**

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

Here, we 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(D5,C8))`

Notice that 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 syntax of the individual formula from the nested one is:

`=OR(logical1,[logical2],..)`

Here, it 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**.

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False.**

**Read More:** **Excel VBA to Match Value in Range (3 Examples)**

**3. Using ISNUMBER with FIND and IF Functions ****to Find Case Sensitive Match ****in Excel**

You can use the nested **ISNUMBER** and **FIND** formula to match whether a cell value contains a particular character or not.

Letâ€™s check for â€ś**T**â€ť and â€ś**j**â€ť in dataset cell **C7**.

The formula are:

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

or

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

The result shows â€ś**TRUE**â€ť and â€ś**FALSE**â€ťÂ respectively.

Moreover, you can get results as you want instead of Boolean ones using the **IF **function along with the above formula.

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 syntax of the individual formula:

`=IF(logical_text, [value_if_true],[value_if_false])`

**logical_text**= condition to check.

**value_if_true**= text to show if the logical test is true.

**value_if_false**= text to show if the logical test is false.

`=ISNUMBER(value)`

**value**=the particular data which is searched.

It takes the value and checks whether the value is number or not and returns a boolean value on that basis.

`=FIND(find_text, within_text, [start_num])`

**find_text**=the particular lookup text.

**within_text**= the character inside the find text.

**start_num**= the starting number of that character within the find text.

It finds a particular character in a text and boolean **TRUE** or **FALSE** accordingly.

**Similar Readings**

**Sum All Matches with VLOOKUP in Excel (3 Easy Ways)****How to Match Names in Excel Where Spelling Differ (8 Methods)**

**4. Using Nested SUMPRODUCT and EXACT ****to Find Case Sensitive Match****Â in Excel**

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)`

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.

**Formula Explanation:**

The syntax of individual formula:

`=SUMPRODUCT(array1,[array2],..)`

Here, it takes **arrays** to give the arithmetic sum of the corresponding arrays.

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False.**

*Note: **The double minus (â€“) sign is used for the forced conversion of Boolean values to numerical ones.*

**5. Using the INDEX and MATCH Functions ****to Find Case Sensitive Match****Â in Excel**

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))`

or

`=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.

**Formula Explanation:**

The syntax of individual formula:

`=INDEX(array, row_num, [column_num])`

here, **array**=range of the data.

**row_num**=row index of the particular data in the range.

**column_num**=column index of the particular data in the range.

`=MATCH(lookup_value, lookup_array,[match_type])`

**lookup_value**=the searched value in the range.

**lookup_array**=the range of data.

**match_type**=0,1,-1 (0 for exact match, 1 for less than the searched value, -1 for greater than the matched value).

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False**.

**Read More:** **Excel Find Matching Values in Two Columns**

**6. Using the LOOKUP Function Nested with EXACT and IF Functions ****to Find Case Sensitive Match****Â in Excel**

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.

**a. Using VLOOKUP**

Letâ€™s start with learning the use of **VLOOKUP**.

We 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")`

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.

**Formula Explanation:**

The syntax of the individual formula:

`=IF(logical_text, [value_if_true],[value_if_false])`

**logical_text**= condition to check.

**value_if_true**= text to show if the logical test is true.

**value_if_false**= text to show if the logical test is false.

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False.**

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

**lookup_value**= the searched value.

**table_array**= the table where the searched value exists.

**col_index_num**=the index number of the particular column in the range.

**range_array**=the range of the data.

**b. Using HLOOKUP**

Subsequently, the **HLOOKUP** function can also be used for the purpose in a similar manner.

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.

**Formula Explanation:**

The syntax of the individual formula:

`=IF(logical_text, [value_if_true],[value_if_false])`

**logical_text**= condition to check.

**value_if_true**= text to show if the logical test is true.

**value_if_false**= text to show if the logical test is false.

`=EXACT(text1, text2)`

It takes two texts to compare and give the result as Boolean **True **or **False.**

`=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`

**lookup_value**= the searched value.

**table_array**= the table where the searched value exists.

**row_index_num**=the index number of the particular row in the range.

**range_array**=the range of the data.

*Note:** Both the functions 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.*

**Read more:** **How to Vlookup and Pull the Last Match in Excel (4 Ways)**

**Things to Remember**

It is always better to place the cursor at the end of the formula and from the keyboard press **CTRL+SHIFT+ENTER** for the array-related formula. Although the formulas above work perfectly, in case it doesnâ€™t work try to follow this to get accurate results.

**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 for you. Anyway, if you have any query you can ask in the comment section.