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

Dataset Overview

We have the following sample dataset which we are going to use to demonstrate the 7 methods. The dataset contains columns for ID and Name. It is the information of people working in a company. In addition, the database contains some case-sensitive values.

Method 1 – Using the EXACT Function

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

=EXACT(C8,C10)

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:

=EXACT(C5,C7)

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(EXACT(D5,C10))

=OR(EXACT(D6,C8))

Notice: This formula checks if the checker value matches any of the values in the range. If the checker value has the exact case, it will return TRUE.

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

Formula Explanation

The TEXT function takes two sets of text 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.

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:

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

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

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

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:

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

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

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

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.

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

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:

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

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

Formula Explanation

The EXACT function compares two sets of texts and returns a Boolean (True or False).
EXACT(C7, C5:C10) produces the array {FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.
The SUMPRODUCT function multiplies corresponding elements of arrays and then sums the results.
The result is 1102.

Alternatively, you can enter the formula:

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

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.

Note: The double minus (–) sign is used to force the conversion of Boolean values to numerical ones.

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:

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

Formula Explanation

EXACT(C7, C5:C10) produces the array {FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.
INDEX(EXACT(C7, C5:C10), ) gives the same array.
MATCH(TRUE, INDEX(EXACT(C7, C5:C10), ), 0) returns 3.
INDEX(C5:C10, 3) equals “JANET.”

Similarly, enter the formula:

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

to find the exact match for both values.

Method 7 – Utilizing the 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(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)) evaluates to TRUE.
Result: “Found.”

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