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.
