How to Find a Case Sensitive Match in Excel (7 Methods)

Dataset Overview

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.

Method 1 – Using the EXACT Function

The EXACT function is the shortest way to compare two texts for exactness. It returns a Boolean value (True or False) based on whether the texts match exactly. Let’s say we want to compare values in cell C8 and C10 of our dataset. The formula would be:

However, even if the values match, the result might be FALSE due to case differences. For example, “John” and “john” would yield FALSE.

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

The formula will look like:

Due to case-sensitive issues, the result will also be false.

Method 2 – Nested OR and EXACT Functions

To compare values in a range, create a new column for checker values. Then use nested OR and EXACT formulas. For instance:

or,

Method 3 – ISNUMBER with FIND and IF Functions

Use the nested ISNUMBER and FIND formula to check if a cell value contains a specific character. The FIND function returns the starting position of one text string within another (case-sensitive). For example:

This formula checks if T exists in cell C7 and returns TRUE if it does.

or

The result shows TRUE and FALSE respectively.

Method 4 – Combining IF with ISNUMBER and FIND Functions

To get a remark instead of Boolean results, combine the IF function with the Method 3 formula. For instance:

This formula checks if R exists in cell C10. If it does, it returns MATCHED; otherwise, it returns Not MATCHED.

or

Method 5 – Using Nested SUMPRODUCT and EXACT Functions

We can combine the SUMPRODUCT and EXACT functions to create a nested formula that matches case-sensitive values. However, keep in mind that the SUMPRODUCT function returns numbers, not text. Here’s how it works:

1. Modify the dataset to have only numbers in the ID column.
2. Enter the following formula:

Alternatively, you can enter the formula:

to get results for cells C7 and C5 in the dataset. This approach works for case-sensitive values.

You can see the result shows for JANET and Janet correctly.

Method 6 – Applying INDEX and MATCH Functions

The nested INDEX and MATCH functions can also handle case-sensitive matches in Excel. This method returns the value if it matches exactly. Let’s check for JANET and Zara from the dataset:

• Enter the formula:

Similarly, enter the formula:

to find the exact match for both values.

Method 7 – Utilizing LOOKUP Function

The LOOKUP function can also be used to match values, but it doesn’t handle case-sensitive issues directly. However, you can nest LOOKUP with EXACT and IF functions to achieve case-sensitive matches. There are two main types of LOOKUP functions: VLOOKUP and HLOOKUP.

7.1. Using VLOOKUP Function

Let’s consider the value ZARA and use the VLOOKUP function:

• Formula:

If you change the case of the value, the result will be “Not Found.” The result is shown below.

7.2. Using HLOOKUP Function

Similarly, the HLOOKUP function can be used:

• Formula (for Janet):

The result works perfectly. If you change the case, the result will be the opposite.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to | Excel Match | Learn Excel