How to use the EXACT function

Author: Oscar Cronquist Article last updated on February 10, 2022

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:

=EXACT(B3,C3)

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

EXACT function Count cells case sensitive

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:

=SUMPRODUCT(EXACT(D3,B3,B12)*1)

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

EXACT function average

Array formula in cell G3:

=AVERAGE(IF(EXACT(E3,B3:B12),C3:C12,""))

5.1 How to enter an array formula

EXACT function average array formula 1

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

EXACT function SUM

Array formula in cell G3:

=AVERAGE(IF(EXACT(E3,B3:B12),C3:C12,""))

How to enter an array formula

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

EXACT function MEDIAN

Array formula in cell G3:

=MEDIAN(IF(EXACT(E3, B3:B12), C3:C12, ""))

How to enter an array formula

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

EXACT function 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:

=SUMPRODUCT(NOT(EXACT(D3,B3,B12))*1)

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 8 articles contain the EXACT function.

5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.

Case sensitive lookup and return multiple values
The array formula in cell F5 returns adjacent values from column C where values in column B matches the search […]

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

How to extract a case sensitive unique list from a column
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]

INDEX MATCH – Case sensitive
The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […]

Match two columns and return another value on the same row
This article demonstrates formulas that match two conditions in a column each and return another value on the same row […]

Functions in this article

EXACT SUMPRODUCT AVERAGE IF MEDIAN NOT

Functions in 'Text' category

The EXACT function function is one of many functions in the 'Text' category.

ASC function

Converts full-width (double-byte) characters to half-width (single-byte) characters.

CHAR function

Converts a number to the corresponding ANSI character determined by your computers character set.

CLEAN function

Deletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.

CODE function

Returns the corresponding number for the first character based on your computers character set. (PC- ANSI)

CONCAT function

Concatenates values from multiple cells.

CONCATENATE function

Joins text strings.

EXACT function

Checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.

FIND function

Returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

LEFT function

Extracts a specific number of characters always starting from the left.

LEN function

Returns the number of characters in a cell value.

LOWER function

Converts a value to lower case letters.

MID function

Returns a substring from a string based on the starting position and the number of characters you want to extract.

REPLACE function

Substitutes a part of a text string based on the number of characters and length with a text string you provide.

REPT function

Repeats a specific text a chosen number of times.

RIGHT function

Extracts a specific number of characters always starting from the right.

SEARCH function

Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SUBSTITUTE function

Replaces a specific text string in a value. Case sensitive.

T function

Returns a text value if the argument is a text value.

TEXT function

Converts a value to text in a specific number format.

TEXTAFTER function

Extracts a string after a specific substring in a given value.

TEXTBEFORE function

Extracts a string before a specific substring from a given value.

TEXTJOIN function

Combines text strings from multiple cell ranges.

TEXTSPLIT function

Splits a string into an array based on delimiting values.

TRIM function

Deletes all blanks or space characters except single blanks between words in a cell value.

UNICHAR function

Calculates a character based on a Unicode number.

UNICODE function

Returns a Unicode number based on a character.

UPPER function

Converts a value to upper case letters.

VALUE function

Converts a text string that represents a number to a number.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

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

How to use the EXACT function

Table of Contents

1. Exact Function Syntax

2. EXACT Function Arguments

3. Does the EXACT function ignore cell formatting?

4. Case sensitive count

Explaining formula in cell F3

Step 1 - Identify cells equal to condition case sensitive

Step 2 - Multiply with 1

Step 3 - Sum values

5. Calculate average based on a case sensitive condition

5.1 How to enter an array formula

5.2 Explaining formula

Step 1 - Compare condition to values also considering capital and lower letters

Step 2 - Replace TRUE with corresponding value on the same row

Step 3 - Calculate the average

6. Calculate a sum based on a case sensitive condition

6.1 Explaining formula

Step 1 - Compare condition to values also considering capital and lower letters

Step 2 - Replace TRUE with corresponding value on the same row

Step 3 - Calculate the sum

7. Calculate median based on a case sensitive condition

7.1 Explaining formula

Step 1 - Compare condition to values also considering capital and lower letters

Step 2 - Replace TRUE with corresponding value on the same row

Step 3 - Calculate the median

8. Case sensitive condition not equal to

Explaining formula in cell F3

Step 1 - Identify cells equal to condition case sensitive

Step 2 - Change TRUE to FALSE and vice versa

Step 3 - Multiply with 1

Step 4 - Sum values

'EXACT' function examples

Functions in this article

Functions in 'Text' category

Excel function categories

Excel categories

Leave a Reply

How to comment