30 Excel functions in 30 days: EXACT

Welcome to the collection of activities 30 Excel functions in 30 days (30XL30D). Thank you for taking part in the poll about the most interesting features. In this compilation, we will review the top 30 Excel functions (according to the results of the survey), from the following categories:

• Text
• Information
• Search and links

At the end of this 30-day marathon, a list of all mentioned features will be published in order according to the number of votes received in the poll. However, the top 30 features will be reviewed in random order, so check back every day to make sure you don’t miss anything.

Let’s start our marathon and take as the first function EXACT (SOVPAD). You’ll see 7 examples of how this feature can be used, so get ready to be amazed at what it can do. If you have your own tricks or examples of using this feature, please share them in the comments.

Function 01: EXACT

Function EXACT (EXACT) can check two text strings for an exact match, moreover, case sensitive. Formatting does not affect the result. If the strings match exactly, then the function returns TRUE (TRUE), if there is no exact match, then FALSE (FALSE).

How can you use the EXACT function?

In addition to checking two cells for an exact match, you can use the function EXACT (EXACT) the following actions:

• Use for data validation to block changes to a cell.
• Set the condition to enter only uppercase characters.
• Check for an exact match in a list of values.
• Find an exact match when looking up a table.
• Count the number of exact matches in a list.
• Find differences between two cells.

Syntax EXACT (EXACT)

Function EXACT (EXACT) has the following syntax:

EXACT(text1,text2)

СОВПАД(текст1;текст2)

• text1 (text1) is the first text string.
• text2 (text2) is the second text string.

Arguments text1 и text2 can be entered as text strings or as links to other cells. In Excel, the maximum string length for EXACT (EXACT) is 32767 characters.

Traps EXACT (SOVPAD)

In Excel 2007 Help, under Notes for function EXACT (EXACT) there is a line like this:

It is also possible to use double equal sign (==) instead of the function EXACT (EXACT) to make an accurate comparison. For example, =A1==B1 will return the same result as =EXACT(A1,B1).

This is not true. There is no operator in Excel − double equals (==), and already in the help for Microsoft Excel 2010 this note was removed.

Example 1: Password Check

Imagine that you entered a secret password into one of the cells in your Excel workbook and named this cell pwd.

• In the picture below, the secret password is in cell C2 named pwd. It is on the sheet AdminData, which can be hidden from users.

On another sheet, users will enter the password, and you will check it using the function EXACT (SOVPAD).

• To the list Ex01 the user will enter the password in cell C3.
• In cell C5, using the equals operator (=), we compare the values ​​in the cells C3 и pwd:

  =C3=pwd

• In cell C6, the function EXACT (EXACT) compares cells C3 и pwd case sensitive:

  =EXACT(C3,pwd)

  =СОВПАД(C3;pwd)

If the contents of the two cells match, including the case, then the result in both cases will be TRUE (TRUE). Any differences in formatting (such as bold) will be ignored.

If at least one letter is entered in a different case, then the result in cell C6 will be FALSE (FALSE).

Example 2: Permission to change a cell

After the user has entered the correct password, you can open access to change some cells of the table. For example, by creating a formula to validate the data in cell C5, you can control the changes in values ​​in it.

Using this formula in the dialog box Data Validation (Data Validation), users can only enter a value in cell C5 if the password entered in cell C3 exactly matches the secret password stored in cell pwd. Also, the value in cell C5 must be greater than 0 and less than 0,1.

=AND(EXACT(C3,pwd),C5>0,C5<0.1)

=И(СОВПАД(C3;pwd);C5>0;C5<0,1)

Example 3: Condition for entering only uppercase characters

You can use the function EXACT (EXACT) to check that only uppercase letters are entered in the cell. For example, a Canadian postal code has a fixed format with alternating numbers and upper case letters, e.g. L9L 9L9.

In cell C2, data validation was set using the formula:

=EXACT(C2,upper(C2))

=СОВПАД(C2;ПРОПИСН(C2))

If lowercase letters are entered in the cell, an error warning will appear. Of course, this will not save you from all possible errors when writing the postal code, but be sure that all letters will be in uppercase.

Example 4: Find an exact match in a list

Instead of simply comparing cells, sometimes you want to find an exact match in a list of values. For example, if someone writes a product code in a cell, how do you check if that code matches a product on your list?

In the following example, the product codes are written in cells B2:B5. The customer can order a product by entering its code in cell E2.

The formula in cell F2 uses the function EXACT (EXACT) to check the code entered in cell E2 for an exact match with our list.

The formula is entered as an array formula by pressing Ctrl + Shift + Enter.

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

{=ИЛИ(СОВПАД($B$2:$B$5;E2))}

Example 5: Return title by exact match in list

When searching in a table, the function EXACT (EXACT) can distinguish AA1 from Aa1, which allows you to find the correct product name separately for each code. Other search functions such as VLOOKUP (VLOOKUP) will treat such codes as identical and return the result that appears first in the list with such a code.

In the following example, the product codes are in the range B2:B5, the customer can order a product by entering its code in cell D2.

The formula in cell E2 uses 3 functions − INDIAX (INDEX), MATCH (MORE EXPOSED) and EXACT (SOVPAD)

• Function EXACT (EXACT) checks the code entered in cell D2 and looks for an exact match in the product list.
• Function MATCH (MATCH) returns the row number of the table where the previous function found a match. In our case, the code bG8943TO found in the second line of the range.
• Function INDEX (INDEX) returns the value from the second row of the range A2:A5, i.e. Sat.

The formula is entered as an array formula by pressing Ctrl + Shift + Enter.

{= INDEX($A$2:$A$5,MATCH(TRUE,EXACT($B$2:$B$5,D2),0))}

{=ИНДЕКС($A$2:$A$5;ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$5;D2);0))}

Example 6: Count exact matches in a list

When searching in a table, the function EXACT (EXACT) can distinguish Aa1 from AA1, which allows you to calculate the correct amount separately for each code. Other features like COUNTIF (COUNTIF), will consider such codes as identical and count their total number.

In the following example, the list of duplicate values ​​is in the range A2:A11 and the list of unique values ​​is in column C.

The formula in column D uses two functions − SUMPRODUCT (SUMPRODUCT) and EXACT (SOVPAD)

• Function EXACT (EXACT) looks at what is entered in column C and checks if there is an exact match in the list of elements A2:A11.
• Function SUMPRODUCT (SUMPRODUCT) returns the total that is the sum of the results TRUE

=SUMPRODUCT(--EXACT($A$2:$A$11,C2))

=СУММПРОИЗВ(--СОВПАД($A$2:$A$11;C2))

Two minus signs (double negation) in front of a function EXACT (EXACT) turn the values ​​TRUE (TRUE) and FALSE (FALSE), respectively, into 1 and 0.

Example 7: Check every character in a cell

The simplest thing a function can do for you EXACT (EXACT) – tell if there is an exact match between two text strings. But sometimes when strings don't match, you need to determine which characters don't match. In the following example, each text line contains 6 characters. There are also 6 columns whose headings are numbered from 1 to 6.

The formula in cell C2 is made up of three functions, among which MID (PSTR) and EXACT (SOVPAD).

• Function MID (PSTR) returns a specific character from cells A2 and B2. The position of a character in the text is selected by the number of the column containing the formula. For example, the formula in cell C2 checks the first characters of each text string because that column is number 1.
• Function EXACT (EXACT) compares two characters extracted from text using two functions MID (PSTR).

=EXACT(MID($A2,C$1,1),MID($B2,C$1,1))

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