Excel Guides

Ignoring Case in a Comparison in Excel

When comparing text strings in Excel, you may want to ignore case. That is, you may want the comparison to be case-insensitive, so that "A" is considered equal to "a".

Excel provides a number of ways to do this. One way is to use the EXACT function. The EXACT function takes two arguments: the text string that you want to compare, and the text string that you want to compare it to. The function returns TRUE if the two strings are exactly equal, and FALSE if they are not.

For example, let's say you have a list of names in column A, and you want to see which ones match a list of names in column B. You can use the EXACT function like this:

=EXACT(A1,B1)

If the value in cell A1 is "John" and the value in cell B1 is "john", this formula will return FALSE. But if the value in cell A1 is "John" and the value in cell B1 is also "John", the formula will return TRUE.

You can use the EXACT function for an entire column of data by using an array formula. An array formula is a formula that operates on more than one value at a time. To enter an array formula, type the formula in a cell, then press CTRL+SHIFT+ENTER. (Do not just press ENTER; if you do that, Excel will enter the formula as a regular non-array formula.)

For example, let's say you have a list of names in column A, and you want to see which ones match a list of names in column B. You can use the EXACT function like this:

=EXACT(A:A,B:B)

(Remember to enter this as an array formula by pressing CTRL+SHIFT+ENTER.) This formula will compare each value in column A with each value in column B, and return an array of TRUE and FALSE values.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.