30 Excel Functions in 30 Days: Day One – EXACT

Take part in the “30 Excel functions in 30 days” challenge. Here you will find information about the top 30 Excel functions from the categories:

1. Text data.
2. Working with information.
3. Search and links.

This challenge starts with the EXACT() function. If you haven’t used it before, you’ll be surprised how many useful things you can do with it.

Function One: EXACT

This function is needed to check if text data in strings matches. It should be noted that the case may affect the result, but italics, size, etc. (in other words, formatting) – no. If the data is identical, you will end up with TRUE, otherwise you will get FALSE.

In what cases you can not do without this function

It is ideal for comparing data in a file. In addition, it can be used to do the following:

1. Validate data for subsequent permission to make changes to the document.
2. Set the required data entry in upper case.
3. Determine if the product code matches.
4. Find an exact match in the list.
5. Find out the number of exact matches.
6. Identify differences between cells.

Syntax of the EXACT function

To use the function in the formula bar, you need to enter:

EXACT(argument1,argument2)

Argument1 is the first line.

Argument2 is the second line.

Function variables can be either individual cells or entire text strings. 

Pitfalls when using the EXACT function

The Excel 2007 help topic says that the function can be activated in another way. “Enter a double equal sign (==) to compare data. So, =C1==D1 will give the same result as EXACT(C1;D1).”

This statement is absolutely false. Double equals cannot produce the same result as using the EXACT function. That is why this note has been removed in newer versions of Excel.

Example #1: Checking if the password is correct

You have created a secret password and entered it in the box called “pwd”. When other users enter a certain combination, it can be compared with the contents of this cell.

In the screenshot below, the password is entered in cell C2. It can be found on a sheet called AdminData that other people don’t see.

On sheet Ex01, another user enters a combination in cell C3.

In C4, you need to compare the data from C3 with the value from the “pwd” field:

=C3=pwd

By means of EXACT() functions, compare the data from C3 with the value from the “pwd” field:

=EXACT(C3;pwd)

If they are identical, then the word will appear in C5 TRUE. Again, note that formatting (size, color, etc.) will be ignored. 

However, if the values ​​in cell C5 do not match, the result will be displayed LYING.

Example #2: Allow changes to data 

After the other user enters the correct combination of characters, you can allow him to change certain cells on the sheet. For example, using the EXACT() function, you can watch changes in the daily rate.

First, the user needs to enter the correct password in cell C3. If it matches the data from the “pwd” field, then it will be possible to enter new values ​​​​in C5. In addition, you can set additional conditions for this cell. In this case, the value must be above zero and below 0,1:

=И(СОВПАД(С3;PWD);С5>0;С5<0,1)

Example #3: Mandatory writing data in capital letters only 

The function is also great for checking that only uppercase letters are written in a cell. For example, this will be especially useful when dealing with a Canadian postal code, as it is a combination of numbers and capital letters.

C2 uses a value check using this formula:

EXACT(C2,UPPER(C2))

In the event that the field contains lowercase letters, an error will appear. It should be noted that the use of this formula does not exclude the appearance of incorrect indexes. However, it ensures that only capital letters are used.

Example #4: Check Exact Code Match

Sometimes it may be necessary not just to compare 2 cells, but to find matching values ​​in a large list. For example, you need to make sure that the entered combination matches a certain product code. 

Cells B2:B5 show which product has which code. The user will be able to purchase a specific product by entering the value corresponding to it in cell E2.

To find matching data, in cell F2 you need to activate EXACT() function.

To use an array formula, press the keyboard shortcut Ctrl + Shift + Enter:

{=OR(EXACT($B$2:$B$5,E2))}

Example No. 5: Return the required name according to the provided data

When using the function, values ​​such as Ci3 and CI3 are not considered identical. That is why it is best to use it to search for a product by its code. Similar functions, such as VLOOKUP(), are case insensitive, treat given values ​​the same way, and return the result that appears first in the search.

Cells B2:B5 contain codes corresponding to products. The user can purchase the required product by entering a certain combination in field D2.

In cell B2, the following functions interact simultaneously:

• EXACT() checks the value entered in field D2. It then looks for a cell with exactly the same code.
• Then EXPRESS() defines a string with a valid result.
• INDEX() returns the value in the second line of A2:A5.

Example #6: Determining the Number of Matches in a List

Again, it should be noted that the EXACT() function distinguishes between Ci3 and CI3, which will help you calculate the correct number of matches. Other similar functions, such as COUNTIF(), will treat these codes as the same and display their total number. 

In this case, the duplicate data is located in cells A2:A11. Also, unique values ​​can be found in column C. 

To achieve the goal, you need to enter 2 functions in column D:

• Function EXACT() will help you check the data that was entered in column C. After that, it will find values ​​that fully match them.
• Thereafter SUMPRODUCT() returns the number that counts against the correct results.

Attention! If you precede the EXACT() function with two minus signs, then the TRUE and FALSE values ​​will be replaced by 1 and 0.

Example #7: Check every character in a cell

Sometimes you may need to find out which characters in two strings don’t match. The EXACT() function will also help with this. In this example, there are 6 characters per line, and the numbers 1 to 6 are used as headings. 

Cell E2 uses 2 functions at the same time:

• Function PSTR() returns any character from column A or B, while relying on the number specified in the header. In this case, in the formula in cell C2, the first character in all lines must be checked for a match, since the number in field C1 corresponds to one. 
• EXACT() will help you compare the characters defined by the MID() function. 

=СОВПАД(ПСТР($A2;C$1;1);ПСТР($B2;C$1;1))

Try experimenting with each function in your own Excel files. Then put in the hard work and teach a loved one or colleague how to use EXACT() at work. With the application of the acquired knowledge, the result will be fixed better.