Compare Cells with Excel EXACT Function ExamplesUse the Excel EXACT function to compare text strings for an exact match, including upper and lower case. There are 7 examples for how to use EXACT, with video and written steps. Author: Debra Dalgleish |
How Could You Use EXACT Function?Besides checking two cells to see if their contents match exactly, you can use the EXACT function to do the following:
There are step-by-step examples in the sections below. EXACT Function SyntaxThe EXACT function has the following syntax for its arguments:
You can enter the text1 and text2 arguments as cell references or text strings. The maximum string length for EXACT is 32767 characters. Example 1: Test a PasswordIn this example, there is a secret password entered in a cell in an Excel workbook, That cell has been named as “pwd”.
On another sheet, people will enter a password, and there is a cell with the EXACT function, to test if the password is an exact match for the correct password.
If the contents of the two cells are identical, including upper and lower case, cell C5 will show TRUE as the result. Any formatting differences, such as bold font, will be ignored. If there is a difference in the contents – if even one letter is a different case, the result in C5 will be FALSE. |
Example 2: Allow Changes to a CellAfter the user enters the correct password, you could allow changes to specific cells in the worksheet. For example, a custom data validation formula in cell C5 could control changes to the Daily Rate. With the following formula in the data validation dialog box, users can enter a value in cell C5 only if the password typed in C3 is an exact match for the secret password in the pwd cell. Also, the value typed in C5 must be higher than zero, and lower than 0.1. =AND(EXACT(C3,pwd),C5>0,C5<0.1) Example 3: Force Upper Case EntriesYou could also use the EXACT function in data validation to ensure that all upper case letters are typed in a cell. For example, a Canadian postal code is a set format, with alternating numbers, and upper case letters, e.g., L9L 9L9. In cell C2, data validation has been applied, with the formula: EXACT(C2,UPPER(C2)) If any lower case letters are entered, an error alert will appear. This won’t prevent all errors in the postal code, but will ensure that upper case letters are used. |
Example 4: Find Exact Match in ListInstead of simply comparing one cell to another, you might need to look for an exact match in a list of values. If someone types a product code in a cell, is that exact code in your product list? In this example, there is a product code list in cells B2:B5, and a customer can order a product, by typing its code in cell E2. The formula in cell F2 uses the EXACT function to check the code typed in cell E2, and see if there’s an exact match in the list of product codes. NOTE: The formula is array entered, by pressing Ctrl+Shift+Enter Example 5: Pull Name for Exact Match in ListIn a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct product name for each code. Other functions, like VLOOKUP, would treat those codes as identical, and return the product name for the first code it encounters in the table. In this example, there is a product code list in cells B2:B5, and a customer can order a product, by typing its code in cell D2. The formula in cell E2 uses 3 functions – INDEX, MATCH and EXACT:
NOTE: The formula is array entered, by pressing Ctrl+Shift+Enter |
Example 6: Count Exact Matches in ListIn a lookup table, the EXACT function can distinguish between AA1 and Aa1, and help return the correct count for each code. Other functions, like COUNTIF, would treat those codes as identical, and return the count for all variations of the code. In this example, there is a Item list in cells A2:A11, and a list of unique items in column C. The formula in column D uses 2 functions – SUMPRODUCT and EXACT:
=SUMPRODUCT(–EXACT($A$2:$A$11,C2)) Note: The two minus signs (double unary) in front of the EXACT function convert the TRUE and FALSE values to 1 and 0 values. Example 7: Check Each Character in CellThe EXACT function can tell you if two text strings match exactly, but you might want a quick way to see which characters are not identical. In this example, each string has 6 characters, and the numbers 1 to 6 are entered as column headings. The formula in cell E2 uses 3 functions – MID and EXACT:
=EXACT(MID($A2,C$1,1),MID($B2,C$1,1)) |
Download the WorkbookEXACT Function Examples: Download the sample file with EXACT examples from this tutorial. The zipped Excel file is in xlsx format, and does not contain macros. Related Tutorials |
Last updated: June 13, 2023 3:33 PM