The EXACT function in Excel is a text function that compares two text strings and returns TRUE if they are exactly the same, and FALSE otherwise. The comparison is case-sensitive, meaning that uppercase and lowercase letters are considered different.
The syntax of the EXACT function is:
=EXACT(text1, text2)
Where text1 and text2 are the text strings to compare. They can be entered directly, or as cell references, or as results of other formulas.
One common use case of the EXACT function is to compare two columns of data and identify any discrepancies or mismatches. For example, suppose you have a list of employee names in column A and another list of employee names in column B, and you want to check if they are the same. You can use the EXACT function in column C to compare the values in column A and column B, and return TRUE or FALSE.
The formula in cell C2 is:
=EXACT(A2, B2)
And you can copy it down to the rest of the column. The result will look like this:
| A | B | C |
|---|---|---|
| John Smith | John Smith | TRUE |
| Jane Doe | Jane Doe | TRUE |
| Bob Lee | Bob Li | FALSE |
| Alice Wong | Alice Wang | FALSE |
| Tom Jones | Tom Jones | TRUE |
As you can see, the EXACT function returns FALSE for the rows where the names are different, even by one letter.
How to Highlight the Differences Using Conditional Formatting
To make the differences more visible, you can use conditional formatting to highlight the cells that contain FALSE. To do this, follow these steps:
- Select the range of cells that contain the EXACT function results (C2:C6 in this example).
- Go to the Home tab, and click on Conditional Formatting.
- Choose New Rule from the drop-down menu.
- Select Use a formula to determine which cells to format.
- In the Format values where this formula is true box, enter the formula:
=C2=FALSE - Click on the Format button, and choose a fill color or a font color to apply to the cells that contain FALSE. Click OK.
- Click OK again to close the New Formatting Rule window.
The result will look like this:
| A | B | C |
|---|---|---|
| John Smith | John Smith | TRUE |
| Jane Doe | Jane Doe | TRUE |
| Bob Lee | Bob Li | FALSE |
| Alice Wong | Alice Wang | FALSE |
| Tom Jones | Tom Jones | TRUE |
The cells that contain FALSE are highlighted with the color you chose.
Other Approaches to Compare Two Columns in Excel
Besides the EXACT function, there are other ways to compare two columns in Excel and find the differences. Here are some of them:
- Use the IF function to return a custom message instead of TRUE or FALSE. For example, the formula
=IF(EXACT(A2, B2), "Match", "Mismatch")will return “Match” or “Mismatch” depending on the comparison result. - Use the COUNTIF function to count how many times a value in one column appears in another column. For example, the formula
=COUNTIF(B:B, A2)will return the number of times the value in A2 appears in column B. If the value appears only once, it means there is a match. If the value appears zero or more than once, it means there is a mismatch or a duplicate. - Use the MATCH function to find the position of a value in one column in another column. For example, the formula
=MATCH(A2, B:B, 0)will return the row number where the value in A2 is found in column B. If the value is not found, it will return an error. You can use the ISERROR function to check for errors and return a custom message. For example, the formula=IF(ISERROR(MATCH(A2, B:B, 0)), "Not Found", "Found")will return “Not Found” or “Found” depending on the match result.
