Excel EXACT Function
Summary
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.
Purpose
Compare two text strings
Return value
A boolean value (TRUE or FALSE)
Syntax
=EXACT (text1, text2)
Arguments
- text1 - The first text string to compare.
- text2 - The second text string to compare.
Version
Usage notes
The EXACT function compares two text strings in a case-sensitive manner. If the two strings are exactly the same, EXACT returns TRUE. If the two strings are not the same (taking into account upper and lower case) EXACT returns FALSE.
Examples
Below are two examples of the EXACT function used with hardcoded strings. In the first example, the strings are identical, in the second example, the only difference is the capital "A":
In the example shown, the formula in D6, copied down the column, is:
=EXACT(B6,C6)
You can also use a normal equals sign (=) in a formula, but the comparison is not case sensitive:
=("Apple"="apple") // returns TRUE
EXACT formula examples
Data validation rules are triggered when a user adds or changes a cell value.
The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison.
The AND function takes...
This formula uses the EXACT formula to compare a range of cells to a single value:
=EXACT(B5:F5,B5)
Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE...
By default, Excel is not case-sensitive. For example, with "APPLE" in A1, and "apple" in A2, the following formula will return TRUE:
=A1=A2 // returns TRUE
To compare text strings in a case-sensitive way, you can...
Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to...
The EXACT function takes two arguments, text1 and text2. When text1 and text2 match exactly (respecting case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:
=EXACT("ayako","ayako") //...
Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to...
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 20...
At the core, this is an INDEX and MATCH formula, with the EXACT function used inside MATCH to perform a case-sensitive match.
Working from the inside-out, EXACT is configured to compare the value in E5 against names...
The AND function is designed to evaluate multiple logical expressions, and returns TRUE only when all expressions are TRUE.
In this case the we simply compare one range with another with a single logical expression...
By itself, the XLOOKUP function is not case-sensitive. A lookup value of "RED" will match "red", "RED", or "Red". We can work around this limitation by constructing a suitable lookup array for XLOOKUP with a logical...
SUMPRODUCT is designed to work with arrays, which it multiplies, then sums.
In this case, we are two arrays with SUMPRODUCT: B3:B8 and C3:C8. The trick is to run a test on the values in column B, then convert the...
The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...
Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that equal a specific value. However, for more flexibility, you can use your own...
When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12...
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
