Are Excel Formulas Case Sensitive?

You might be wondering whether Excel treats "apple", "Apple", and "APPLE" as the same thing or as three different values when you use them in a formula.

Contents:

1. Quick Answer

2. What This Means in Practice

2.1 Text Comparisons

2.2 Lookup Functions

3. How to Do a Case-Sensitive Comparison in Excel

4. How to Do a Case-Sensitive Lookup in Excel

Quick Answer

No, almost all Excel formulas and functions are not case sensitive. Excel treats uppercase and lowercase letters as identical in the vast majority of functions, including IF, COUNTIF, VLOOKUP, and XLOOKUP. The one built-in exception is the EXACT function, which is specifically designed to compare two strings with case sensitivity.

What This Means in Practice

Text Comparisons

If you use an IF formula to check whether a cell equals a word, Excel does not care about the casing.

= IF(A1="apple","Match","No Match")

Whether A1 contains "apple", "Apple", or "APPLE", this formula returns "Match" every time. That is usually what you want, but it can catch you off guard if you are working with data where casing carries meaning, like product codes or passwords.

Lookup Functions

The same rule applies to lookup functions. XLOOKUP and VLOOKUP both treat "NYC", "nyc", and "Nyc" as the same lookup value.

= XLOOKUP(A2, B:B, C:C)

If A2 contains "nyc" and your lookup column has "NYC", XLOOKUP will still find it and return the result. If you need a case-sensitive lookup, see the section below. You can learn more about how XLOOKUP works generally in our post on how to perform a two-way XLOOKUP.

How to Do a Case-Sensitive Comparison in Excel

When case does matter, use the EXACT function. EXACT compares two text strings character by character and returns TRUE only when both the characters and their casing match exactly.

= EXACT(A1, "apple")

• A1 - the cell you want to check

• "apple" - the string to compare it against

"apple" returns TRUE. "Apple" and "APPLE" both return FALSE.

You can wrap EXACT inside an IF to act on the result.

= IF(EXACT(A1,"apple"),"Match","No Match")

How to Do a Case-Sensitive Lookup in Excel

There is no built-in case-sensitive version of VLOOKUP or XLOOKUP, but you can build one using EXACT inside a MATCH formula, then wrapping it in INDEX.

= INDEX(C2:C6, MATCH(TRUE, EXACT(B2:B6, E3), 0))

• C2:C6 - the return range (the column with the values you want to pull back)

• B2:B6 - the lookup column to search through

• E3 - the value you are looking for, with case mattering

EXACT(B2:B6, E3) checks every cell in the lookup column against the search value and returns an array of TRUE/FALSE results. MATCH then finds the position of the first TRUE, and INDEX pulls the corresponding value from the return range.

This is an array formula. In Excel 365 and Excel 2019 it will spill and calculate automatically. In older versions of Excel, press Ctrl+Shift+Enter instead of just Enter.

One thing worth noting: if your data has extra spaces hiding inside the cells, case-sensitivity will be the least of your problems. Make sure your data is clean first. Our post on how to remove extra spaces from text covers that.