Excel EXACT Function

EXACT Function in Excel

Exact Function is a logical function in excel which is used to compare two strings or data with each other and it gives us the result whether the both data are exact match or not, this function is a logical function so it gives true or false as a result, this function is a case sensitive formula.

Syntax

EXACT function excel (syntax)

Compulsory Parameter:

  • text1: It is the first string that we wish to compare.
  • text2:  It is the second text string.

How to Use the EXACT Function in Excel? (Examples)

You can download this EXACT Function Excel Template here – EXACT Function Excel Template

Example #1

In the first example, we compare the two strings in column text 1 and text 2 and apply the Exact formula in the output column as shown in the function column, and the output will be as shown in the below table. The EXACT function returns the TRUE where text 1 and text 2 are the same and FALSE where text 1 and text 2 values are not exactly the same.

EXACT Example 1 

Example #2

Suppose we wish that users of our worksheet enter data in the PROPER case in the Proper text-only column. So we can achieve it by using a custom formula based on the EXACT formula and other functions.

Let’s consider the below table, and you have to apply the restriction in the Proper text the only column in the below table.

EXACT function excel Example 2

First, select the C18 cell, then click on data validation under the Data tab and select Data ValidationData ValidationData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs.read more. Change the validation criteria to custom and input the EXACT formula =AND(EXACT(D18,PROPER(D18)),ISTEXT(D18)).

That’s it; now if you type a NONproper word in the Proper text-only column, it will show you an error message like as follows:

EXACT Example 2-1

Example #3

The EXACT function is very useful when you have case-sensitive data.

In this example, we take a set of products where the case-sensitive products are available in the list.

EXACT function excel Example 3

As shown above, there are four products soft toys, two being in lowercase and others in uppercase. Heres we are looking out for numeric values, SUMPRODUCT + EXACT is an exciting and flexible way to do a case-sensitive lookup. We can use the EXACT excel formula to be used is

=SUMPRODUCT(–(EXACT(G14,G3:G12)),H3:H12).

EXACT Example 3-1

The output is: 300

EXACT Example 3-2

EXACT Function in Excel used as a VBA function.

In VBA, we can compare two string using the strcomp function; here is the example as follows:

Sub usefunction()

Dim Lresult as string  // declares the lresult as a string.

Lresult = StrComp (“Tanuj”, “tanuj”)

MsgBox(Lresult)  //Output will show in MsgBox

End sub”

The output will be 0 as String Tanuj and tanuj are not exactly matched.

Recommended Articles

This has been a guide to the EXACT Function in Excel. Here we discuss the EXACT Formula excel and how to use it along with an excel example and a downloadable template. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>