How to use 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.
Formula in cell D3:
Table of Contents
- EXACT Function Syntax
- EXACT Function Arguments
- Does the EXACT function ignore cell formatting?
- Count cells based on a condition case sensitive
- 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. Does the EXACT function ignore cell formatting?
Yes, the EXACT function ignores cell formatting differences.
4. Case sensitive count
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.
5. Calculate average based on a case sensitive condition
Array formula in cell G3:
5.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.
5.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.
6. Calculate a sum based on a case sensitive condition
Array formula in cell G3:
6.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.
7. Calculate median 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 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.
8. 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.
'EXACT' function examples
The following 7 articles have formulas containing the EXACT function.
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.
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]
This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]
Functions in 'Text' category
The EXACT function function is one of many 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