# 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 signsUse 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 OscarYou can contact me through this contact form