How to use the EXACT function
What is the EXACT function?
The EXACT function allows you to check if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters contrary to the equal sign.
What other functions consider upper and lower letters?
FIND function performs a case sensitive operation, however, it looks for a substring in another string and returns the position of the first instance found reading left to right.
The SUBSTITUTE function performs a case sensitive substitution.
What other functions compare values?
The equal sign is a logical operator that compare value to value. You can use this operator in a formula but it is not performing case sensitive comparisons.
The COUNTIF function lets you compare values, however, it is not a case sensitive comparison.
MATCH function / XMATCH function can also be used to compare values but not case sensitive.
Table of Contents
- EXACT Function Syntax
- EXACT Function Arguments
- EXACT function example
- Does the EXACT function ignore cell formatting?
- How to perform a case sensitive count based on a condition
- Calculate an average based on a case sensitive condition
- Calculate a sum based on a case sensitive condition
- Calculate the median based on a case sensitive condition
- Case sensitive condition not equal to
- Get Excel *.xlsx file
1. Exact Function Syntax
EXACT(text1, text2)
2. EXACT Function Arguments
| text1 | The first value to compare. Required. |
| text2 | The second value to compare. Required. |
3. EXACT function example
The EXACT function compares value to value and returns boolean values TRUE or FALSE, the above image shows the EXACT function column D comparing corresponding values on the same row in columns B and C.
Formula in cell D3:
Cells B3 and C3 contain the exact same value and the EXACT function returns TRUE. Cell B4 contain "zombie" and cell C4 contain "Zombie", the first letters have a different case. The EXACT function returns FALSE.
4. Does the EXACT function ignore cell formatting?
This example demonstrates that cell formatting is ignored. The image above shows two different dates in cells B3, C3, B4, and C4. Cell B3 contains "1/1/2018" and C3 contains "01/01/2018", it is the exact same date, however the latter date shows day and month in two digits whereas the former shows day and month in single digits. It is obviously not an exact match, why is the EXACT function returning TRUE?
The reason is that the cell value contains 43101 in both cells and they are therefore identical but they are also formatted differently. The answer to the question is: Yes, the EXACT function ignores cell formatting differences.
Excel uses whole numbers as dates, in other words, dates are stored as serial numbers. Each date is assigned a sequential integer value starting from 1/1/1900 as serial #1.
Serial numbers represent elapsed days, the integer values represent the number of elapsed days since January 1st, 1900. This allows easy date calculations in Excel.
Excel formats cells as dates automatically if you type valid dates, however, they are stored as numbers. You can verify this by changing formatting from date to general, this shows the number representing the date.
5. How to perform a case sensitive count based on a condition
The formula in cell F2 counts cells in cell range B3:B12 based on the condition specified in cell D3, the count is case sensitive.
The result is 2, cells B3 and B11 equal the condition in cell D3.
Formula in cell F3:
Explaining formula in cell F3
Step 1 - Identify cells equal to condition case sensitive
EXACT(D3,B3,B12)
becomes
EXACT("R",{"R"; "W"; "r"; "I"; "r"; "W"; "H"; "W"; "R"; "O"})
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Multiply with 1
The SUMPRODUCT function can't add boolean values, we need to convert them to their numerical equivalents.
The asterisk character lets you multiply values in Excel formulas.
EXACT(D3,B3,B12)*1
becomes
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}*1
and returns
{1; 0; 0; 0; 0; 0; 0; 0; 1; 0}.
Step 3 - Sum values
The SUMPRODUCT function adds values and returns a total, the reason we don't use the SUM function is that it requires us to enter the formula as an array formula.
SUMPRODUCT(EXACT(D3,B3,B12)*1)
becomes
SUMPRODUCT({1; 0; 0; 0; 0; 0; 0; 0; 1; 0})
and returns 2.
6. Calculate average based on a case sensitive condition
Array formula in cell G3:
6.1 How to enter an array formula
The image above shows a leading and trailing curly bracket. They appear automatically when you follow the steps below.
- Copy the array formula above.
- Double press with the left mouse button on cell G3, a prompt appears.
- Paste the formula to cell G3, shortcut keys are CTRL + v.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The formula bar shows a beginning and ending curly bracket, don't enter these characters yourself.
6.2 Explaining formula
Step 1 - Compare condition to values also considering capital and lower letters
EXACT(E3, B3:B12)
becomes
EXACT("R", {"R"; "W"; "r"; "I"; "r"; "R"; "H"; "W"; "R"; "O"})
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Replace TRUE with corresponding value on the same row
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(EXACT(E3,B3:B12), C3:C12, "")
beomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, C3:C12, "")
becomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {69; 67; 81; 16; 80; 17; 98; 37; 16; 80}, "")
and returns
{69; ""; ""; ""; ""; 17; ""; ""; 16; ""}.
Step 3 - Calculate the average
The AVERAGE function calculates the average based on a given set of numbers.
AVERAGE(number1, [number2], ...)
AVERAGE(IF(EXACT(E3,B3:B12),C3:C12,""))
becomes
AVERAGE({69; ""; ""; ""; ""; 17; ""; ""; 16; ""})
and returns 34. 69 + 17 + 16 = 102. 102/3 = 34.
7. Calculate a sum based on a case sensitive condition
Array formula in cell G3:
7.1 Explaining formula
Step 1 - Compare condition to values also considering capital and lower letters
EXACT(E3, B3:B12)
becomes
EXACT("R", {"R"; "W"; "r"; "I"; "r"; "R"; "H"; "W"; "R"; "O"})
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Replace TRUE with corresponding value on the same row
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(EXACT(E3,B3:B12), C3:C12, "")
beomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, C3:C12, "")
becomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {69; 67; 81; 16; 80; 17; 98; 37; 16; 80}, "")
and returns
{69; ""; ""; ""; ""; 17; ""; ""; 16; ""}.
Step 3 - Calculate the sum
The SUM function calculates a total.
SUM(number1, [number2], ...)
SUM(IF(EXACT(E3,B3:B12),C3:C12,""))
becomes
SUM({69; ""; ""; ""; ""; 17; ""; ""; 16; ""})
and returns 102. 69 + 17 + 16 = 102.
8. Calculate median based on a case sensitive condition
Array formula in cell G3:
8.1 Explaining formula
Step 1 - Compare condition to values also considering capital and lower letters
EXACT(E3, B3:B12)
becomes
EXACT("R", {"R"; "W"; "r"; "I"; "r"; "R"; "H"; "W"; "R"; "O"})
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Replace TRUE with corresponding value on the same row
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
IF(EXACT(E3,B3:B12), C3:C12, "")
beomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, C3:C12, "")
becomes
IF({TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, {69; 67; 81; 16; 80; 17; 98; 37; 16; 80}, "")
and returns
{69; ""; ""; ""; ""; 17; ""; ""; 16; ""}.
Step 3 - Calculate the median
The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
MEDIAN(number1, [number2], ...)
MEDIAN(IF(EXACT(E3,B3:B12),C3:C12,""))
becomes
MEDIAN({69; ""; ""; ""; ""; 17; ""; ""; 16; ""})
and returns 17.
9. Case sensitive condition not equal to
The formula in cell F2 counts cells in cell range B3:B12 if not equal to the condition specified in cell D3, the count is case sensitive.
Formula in cell F3:
Explaining formula in cell F3
Step 1 - Identify cells equal to condition case sensitive
EXACT(D3,B3,B12)
becomes
EXACT("R",{"R"; "W"; "r"; "I"; "r"; "W"; "H"; "W"; "R"; "O"})
and returns
{TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.
Step 2 - Change TRUE to FALSE and vice versa
The NOT function returns the boolean opposite to the given argument.
NOT(EXACT(D3,B3,B12))
becomes
NOT({TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE})
and returns
{FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}.
Step 3 - Multiply with 1
The SUMPRODUCT function can't add boolean values, we need to convert them to their numerical equivalents.
The asterisk character lets you multiply values in Excel formulas.
NOT(EXACT(D3,B3,B12))*1
becomes
{FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}*1
and returns
{0; 1; 1; 1; 1; 1; 1; 1; 0; 1}.
Step 4 - Sum values
The SUMPRODUCT function adds values and returns a total, the reason we don't use the SUM function is that it requires us to enter the formula as an array formula.
SUMPRODUCT(EXACT(D3,B3,B12)*1)
becomes
SUMPRODUCT({0; 1; 1; 1; 1; 1; 1; 1; 0; 1})
and returns 8.
Useful links
EXACT function - Microsoft support
EXACT Function in Excel: Explained
'EXACT' function examples
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
This post explains how to lookup a value and return multiple values. No array formula required.
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Functions in 'Text' category
The EXACT function function is one of 30 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form