How to Find Case Sensitive Match in Excel ( 6 Formulas)

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.

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.

Excel Match Case Sensitive: 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 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)

Use of EXACT formula to compare C8 and C10 cells in the dataset

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.

Result of using EXACT formula to compare cells C5 and C7 in the dataset

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

Using nested OR and EXACT to match with checker column

Changing values in checker column to check accuracy of nested OR and EXACT

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.

Comparing for exact matched values in checker

Result for uppercase values using OR and EXACT

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

Use of ISNUMBER and Find to match case sensitive values

Getting Boolean FALSE for not finding matched for case sensitive issue

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

Using IF along with nested ISNUMBER and FIND

Getting Not Matched for result being false

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


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.

Use of SUMPRODUCT and EXACT to get result in number

Getting different result based on case sensitiveness

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

Getting return matched value

Checking for another text to get return matched value

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

Use of VLOOKUP to get true result when matched

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 result for no finding match

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

HLOOKUP formula to get result for match

The result works perfectly.

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

HLOOKUP result for not finding match

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.


Related Articles

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo