Excel Exact Function – compare case sensitive text / case sensitive lookup

This Excel tutorial explains how to compare case sensitive text using Excel Exact Function and how to perform case sensitive lookup / vlookup.

Excel Exact Function to Compare case sensitive text / case sensitive lookup (vlookup)

In Microsoft Excel, you cannot perform case sensitive comparison directly using equal sign. For example, “Peter” is considered same as “peter” in Excel.

Vlookup also shows matching result regardless of case sensitivity.

In order to compare case sensitive text, we need to use Excel Exact Function.

Syntax of Excel Exact Function

EXACT(text1, text2)
Text1 Required. The first text string to compare
Text2 Required. The second text string to compare

The EXACT Function performs case sensitive comparison and returns TRUE if two texts are the same, otherwise it returns FALSE.

Example of Excel Exact Function

The below example is self-explanatory.

Example of Excel Exact Function – case sensitive Lookup

By default Vlookup Function is not case sensitive. In the below example, Vlookup fails to lookup peter’s salary of 2000 as Peter is considered as the first match.

In order to perform case sensitive Lookup, use Index Function and Match Function combined with Exact Function, which is similar to a Vlookup but case sensitive.

The below formula is an Array ( the brackets {} ), make sure you press CTRL+SHIFT+ENTER after you type the formula in F2. Afterwards autofill the formula for the cells below and you don’t have to worry about the Array for the autofill.

To explain briefly how the formula in F2 works, “peter” is compared with all the Names in column A to see if there is a case sensitive match (therefore we need an Array), then locate the row number (3). Finally the INDEX Function returns the value of the cell in the 2nd column and in the returned row number.

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *