✨ Part 1: Introduce
🌟 Definition: The EXACT function in Microsoft Excel compares two text strings and determines if they are exactly the same. It checks for case sensitivity and even the presence of spaces within the text.
🌟 Purpose: The purpose of the EXACT function is to provide an accurate comparison between two text strings, ensuring that the content and the formatting are identical. It is commonly used when checking if two values match exactly without any variations.
🌟 Syntax & Arguments: The syntax of the EXACT function is as follows:
EXACT(text1, text2) text1andtext2: These are the two text strings that you want to compare.
🌟 Return value: The EXACT function returns a logical value of either TRUE or FALSE. It returns TRUE if the two text strings are exactly the same, and FALSE if they differ in any way.
🌟 Remarks:
- The EXACT function is case-sensitive, so “apple” and “Apple” would be considered different.
- It also considers spaces, so “apple ” and “apple” would also be considered different.
- The function can be used with other functions, such as IF, to perform conditional operations based on the comparison result.
✨ Part 2: Examples
Let’s explore three examples that demonstrate the usage of the EXACT function:
1️⃣ Example 1: Checking Exact Match
| A | B | C | |
|---|---|---|---|
| 1 | Text 1 | Text 2 | Exact Match? |
| 2 | Apple | Apple | =EXACT(A2, B2) |
| 3 | Banana | Banana | =EXACT(A3, B3) |
| 4 | Orange | orange | =EXACT(A4, B4) |
This example shows two sets of text strings in columns A and B. We want to determine if they match exactly using the EXACT function. The result is displayed in column C.
- The formula
=EXACT(A2, B2)cell C2 compares the text strings “Apple” and “Apple”. Since they are exactly the same, the result is TRUE. - The formula in cell C3 compares the text strings “Banana” and “Banana”. Again, the result is TRUE.
- However, the formula in cell C4 compares the text strings “Orange” and “orange”. As they differ in case, the result is FALSE.
2️⃣ Example 2: Conditional Formatting
| A | B | |
|---|---|---|
| 1 | Product | Status |
| 2 | Apple | =IF(EXACT(A2, “Apple”), “In Stock”, “Out of Stock”) |
| 3 | Banana | =IF(EXACT(A3, “Apple”), “In Stock”, “Out of Stock”) |
| 4 | Orange | =IF(EXACT(A4, “Apple”), “In Stock”, “Out of Stock”) |
In this example, we have a list of products in column A, and we want to determine their status based on an exact match with the product “Apple” using the EXACT function. The status is displayed in column B.
- The formula
=IF(EXACT(A2, "Apple"), "In Stock", "Out of Stock")in cell B2 checks if the product in cell A2 is exactly “Apple”. If it is, it displays “In Stock”; otherwise, it displays “Out of Stock”. - The formula in cell B3 checks if the product in cell A3 is exactly “Apple” and provides the corresponding status.
- Similarly, the formula in cell B4 checks the exact match for the product in cell A4.
3️⃣ Example 3: Ignoring Leading and Trailing Spaces
| A | B | C | |
|---|---|---|---|
| 1 | Text 1 | Text 2 | Exact Match? |
| 2 | Apple | Apple | =EXACT(A2, B2) |
| 3 | Banana | Banana | =EXACT(A3, B3) |
| 4 | Orange | Orange | =EXACT(TRIM(A4), TRIM(B4)) |
In this example, we have text strings in columns A and B that may contain leading or trailing spaces. We want to compare the text strings while ignoring those spaces using the EXACT function. The result is displayed in column C.
- The formula
=EXACT(A2, B2)cell C2 compares the text strings “Apple” and “Apple” as is, resulting in a TRUE match. - The formula in cell C3 compares the text strings “Banana” and “Banana” without considering spaces, resulting in a TRUE match.
- However, the formula in cell C4 compares the text strings “Orange” and “Orange” after removing leading and trailing spaces using the TRIM function.
4️⃣ Example 4: Validating User Input
Suppose you have an Excel spreadsheet where users input their usernames and passwords. You want to validate if the entered password matches the expected password for each user. In this example, assume the expected passwords are stored in column B, and the user inputs are in column C.
| A | B | C | |
|---|---|---|---|
| 1 | User | Expected Password | User Input |
| 2 | John | 123456 | 123456 |
| 3 | Jane | password | Password |
| 4 | Bob | abc123 | abc123 |
You can use the EXACT function nested with an IF function to validate the user input against the expected password. In cell D2, enter the following formula and drag it down to apply it to the remaining cells:
=IF(EXACT(B2, C2), "Match", "Mismatch") The formula compares the expected password in cell B2 with the user input in cell C2. If they match exactly, it displays “Match”; otherwise, it displays “Mismatch”. The result will be displayed in column D.
5️⃣ Example 5: Data Cleansing
You may have a dataset with inconsistent or duplicate entries in a data cleansing scenario. You can use the EXACT function to identify and remove duplicate values.
| A | |
|---|---|
| 1 | Data |
| 2 | Apple |
| 3 | Banana |
| 4 | apple |
| 5 | Banana |
To identify and remove duplicate values, enter the following formula in cell B2 and drag it down to apply it to the remaining cells:
=IF(COUNTIF($A$2:A2, A2) > 1, "Duplicate", "Unique") The formula checks if the value in cell A2 exists more than once in the range A2:A2. If it does, it marks it as “Duplicate”; otherwise, it marks it as “Unique”. The result will be displayed in column B.
6️⃣ Example 6: Data Validation
Data validation is important to ensure the accuracy and integrity of data. You can use the EXACT function in combination with data validation rules to enforce specific formatting or requirements.
Let’s assume you have a column of product codes, and you want to ensure that they are all alphanumeric and exactly 6 characters long.
| A | |
|---|---|
| 1 | Product Code |
| 2 | ABC123 |
| 3 | 12345 |
| 4 | XYZ789 |
| 5 | AB12!@ |
To set up data validation, select the range of cells in column A (excluding the header), go to the “Data” tab, and click on “Data Validation.” In the Data Validation settings, choose “Custom” as the validation criteria and enter the following formula:
=AND(EXACT(A2, UPPER(A2)), ISNUMBER(VALUE(A2)), LEN(A2) = 6) This formula ensures that the value in each cell is uppercase, consists of alphanumeric characters only, and has a length of 6 characters.
7️⃣ Example 7: Case-Sensitive Lookup
In some scenarios, you may need to perform a case-sensitive lookup to retrieve specific information. The EXACT function can be used to achieve this.
Assume you have a dataset with customer names in column A and their corresponding phone numbers in column B. You want to perform a case-sensitive lookup to retrieve the phone number of a specific customer.
| A | B | |
|---|---|---|
| 1 | Customer Name | Phone Number |
| 2 | John | 555-1234 |
| 3 | jane | 555-5678 |
| 4 | Mary | 555-9012 |
| 5 | John | 555-7890 |
To perform a case-sensitive lookup, enter the customer name you want to search for in cell D2. In cell E2, enter the following formula and drag it down to apply it to the remaining cells:
=IF(EXACT(D2, A2), B2, "") The formula compares the customer name in cell D2 with the name in cell A2. If they match exactly, it returns the corresponding phone number in cell B2; otherwise, it returns an empty string. The result will be displayed in column E.
These examples demonstrate various business scenarios where the EXACT function can be used to validate input, cleanse data, enforce data validation rules, and perform case-sensitive lookups.
✨ Part 3: Tips and Tricks
- To handle case-insensitive comparisons, you can use the LOWER or UPPER function in combination with the EXACT function.
- The EXACT function can be combined with other functions like IF, COUNTIF, and VLOOKUP to perform complex logical operations and data validation checks.
- Be cautious while comparing numeric values as text strings, as leading zeros or formatting differences can result in mismatched comparisons.