How to Find Case Sensitive Match in Excel (7 Ways)

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.

Sample Dataset

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 Function for Finding Case sensitive match in Excel

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 Function to Find Case Sensitive Match in Excel

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:


Apply Nested OR and EXACT Function to Find Case Sensitive Match in Excel



Excel Match Case Sensitive

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 function  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:


Excel Match Case Sensitive



Excel Match Case Sensitive with INUMBER and FIND Functions

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,


Excel Match Case Sensitive with I, ISNUMBER and FIND Functions



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 function 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:



🔎 Formula Explanation

The EXACT function takes two texts to compare and gives the result as Boolean True or 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



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

Case Sensitive Match

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

Excel Match Case Sensitive

🔎 Formula Explanation




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

So, Final Output => JANET


=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")

VLOOKUP Function

🔎 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.

VLOOKUP Functions

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

HLOOKUP Function to Find Match Sensitive Case

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

HLOOKUP Function

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.


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

Get FREE Advanced Excel Exercises with Solutions!


Syeda Fahima Nazreen
Syeda Fahima Nazreen

SYEDA FAHIMA NAZREEN is an electrical & electronics engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Proteus, MATLAB, Multisim, AutoCAD, Jupiter Notebook, and MS Office, going beyond the basics. With a B.Sc in Electrical & Electronic Engineering from American International University, Bangladesh, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF