How can I create an Excel formula that will only return a value if it is an exact match?

By /

To create an Excel formula that will only return a value if it is an exact match, you can use the IF and EXACT functions together. The IF function will check if the value matches the criteria, and if it does, it will return the value. The EXACT function will compare the value to the criteria and return TRUE if they are an exact match, and FALSE if they are not. By combining these two functions, you can ensure that only exact matches will be returned by the formula.

Excel Formula: If Exact Match Then

You can use the following syntax in Excel to create an IF statement that checks if the text in two cells exactly match:

=IF(EXACT(A2, B2), "Yes", "No")

This particular formula checks if the text in cells A2 and B2 are an exact match and returns either “Yes” or “No” as a result.

Note: Feel free to replace “Yes” and “No” with whatever other values you’d like to return.

The following example shows how to use this syntax in practice.

Example: Use IF Statement to Check for Exact Match in Excel

Suppose we have the following list of student names from two different classes in Excel:

Suppose we would like to check if the names in corresponding cells in each column are an exact match.

We can type the following formula into cell C2 to do so:

=IF(EXACT(A2, B2), "Yes", "No")

The following screenshot shows how to use this formula in practice:

Excel if exact match

Column C returns either “Yes” or “No” to indicate if the text in corresponding cells in columns A and B are exact matches.

Note that in order for the EXACT function to return TRUE, the text must have the same characters in the same positions and have the same case.

Also note that you could instead return a cell value instead of “Yes” or “No” in the IF statement.

For example, you could use the following formula to return the name of the student in column A if the name exactly matches the corresponding name in column B or a blank otherwise:

=IF(EXACT(A2, B2), A2, "")

The following screenshot shows how to use this formula in practice:

The IF statement now returns the name from column A if it exactly matches the name in column B or a blank cell otherwise.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel Formula: If Exact Match Then
  2. Excel Formula: If Match then Return Yes or No
  3. Can I write a Google Sheets formula to return “Yes” or “No” if there is a match?
  4. How can I use INDEX MATCH to return the maximum value in Excel?
  5. Does the Google Sheets formula return Yes or No if there is a match?
  6. How Can INDEX MATCH Be Used to Return the Maximum Value in Excel?
  7. Excel Formula: If Blank Then Return Value of Another Cell
  8. Excel Formula: If Between Two Numbers then Return Value
  9. How can I create an Excel formula that returns the value of another cell if a certain cell is blank?
  10. How to Match Two Columns and Return a Third in Excel