Return YES If 2 Cells Match in Excel (10 Methods)

We can compare two cells in MS Excel in different ways. Excel offers many easy methods to compare two cells and return a certain value if the values match. In this article, we will learn 10 methods to return YES if 2 cells match.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

10 Methods to Return YES If 2 Cells Match in Excel

We will apply 10 different methods to see if 2 cells match and indicate yes in Excel. We have a dataset, which contains the name of the tennis and rugby players of the school. Some of them play both games.

1. Use Excel IF Function to Return YES If 2 Cells Match

The IF function is a logical function. It makes a comparison between the given value and the expected value and returns TRUE, FALSE, or a specified text.

We can perform this IF function in two ways.

1.1 IF Function with Matching Condition

We will check if 2 cells are the same and return Yes, otherwise it will return a blank.

Step 1:

• Go to Cell D5.
• Write the following formula on that cell.

Step 2:

• Press the Enter button and drag the Fill Handle icon.

We can see that the status is Yes when cells of both columns match. The columns in the red rectangles are not the same, so they are showing blank returns.

Read More: Compare Two Cells in Excel and Return TRUE or FALSE (5 Quick Ways)

1.2 IF Function with Odd Data

Here, we will check if both cells are different or not. If cells are different, the status will remain blank; otherwise, show Yes.

Step 1:

• Go to Cell D5 and replace the previous formula with the below one.

Step 2:

• Now, press Enter.

We used the range in the formula. So, no need to drag the formula.

2. Insert Excel EXACT Function to Match 2 Cells and Return YES

The EXACT function checks two texts and results TRUE or FALSE.

We will insert the EXACT function with the IF function to match 2 cells.

Step 1:

• Go to Cell D5.
• Copy and paste the following formula.

Step 2:

• Press Enter and pull the Fill Handle icon.

Read More: Excel Compare Two Strings for Similarity (3 Easy Ways)

3. Use AND and IF Functions to Show YES If 2 Cells Are the Same

The AND function is a logical function and checks conditions. If all conditions are fulfilled, it returns TRUE.

We will use the AND function with the IF function in this method.

Step 1:

• Copy and paste the following formula on Cell D5.

Step 2:

• Press the Enter button and pull the Fill Handle icon.

Here, match cells are showing Yes.

4. Combine COUNTIF and IF Functions to Test 2 Cells

The COUNTIF function is a statistical function that counts the number of cells based on criteria.

We will combine the COUNTIF function with the IF function to test two cells and return Yes.

Step 1:

• Move to Cell D5.
• Type the following formula.

Step 2:

• Hit the Enter button and drag the Fill Handle icon.

We are getting Yes for match cells.

5. Test 2 Cells Using Excel OR Function and Show YES

The OR function is one of the logical functions. It returns TRUE if any of the conditions are fulfilled.

We will test 2 cells using the OR function.

Step 1:

• Enter Cell D5.
• Type the formula below.

Step 2:

• Hit Enter button and pull the Fill Handle icon.

6. Combination of MATCH and ISERROR Functions to Test Two Cells and Return YES

The MATCH function looks for a given reference from a range.

The ISERROR function checks a reference if that is an error or not.

We will use the combination of the MATCH and ISERROR functions to test 2 cells.

Step 1:

• Copy and paste the following formula on Cell D5.

Step 2:

• Hit the Enter button and drag the Fill Handle icon.

7. Join IF and SUM Functions to Test 2 Cells in Excel

The SUM function adds value from a range of given values.

We will use a simple SUM function to perform this.

Step 1:

• Go to Cell D5.
• Write the following formula on that cell.

Step 2:

• Hit the Enter button and drag the Fill Handle icon.

8. Combine IF, ISERROR, and VLOOKUP Functions to Test 2 Cells and Print YES

The VLOOKUP function looks for a value from a range and gives an output.

The VLOOKUP function can check two cells and print Yes if they match.

Step 1:

• Copy and paste the following formula on Cell D5.

Step 2:

• Hit the Enter button and pull the Fill Handle icon.

We get Yes when 2 cells match.

9. Join IF and TRIM Functions to Test 2 Cells

The TRIM function removes spaces from a given text.

This TRIM function removes spaces and tests 2 cells.

Step 1:

• Enter Cell D5.
• Write the formula below on that cell.

Step 2:

• Press Enter and drag the Fill Handle icon.

10. Excel VBA to Test 2 Cells and Print Yes When They Match

We will use Excel VBA to test 2 cells and print Yes when matched.

Step 1:

• Go to the Developer tab.
• Click on the Record Macro option.
• Set a name for the Macro and click OK.

Step 2:

• Set a name for the Macro and click OK.
• Click on Macros from the Ribbon and Step Into it.

Step 3:

• Now put the following VBA code on the module.

Sub Match_2_Cells()
Dim n, m As Range
Set n = Application.InputBox(prompt:="Sample_1", Type:=8)
Set m = Application.InputBox(prompt:="Sample_2", Type:=8)
If n.Value = m.Value Then
Range("E6").Value = "Yes"
Else
Range("E6").Value = "No"
End If
End Sub

Step 4:

• Press F5 to run the code.
• A dialog box will appear. Put 1st cell reference.

Step 5:

• Press OK Again, put a cells cell reference on 2nd dialog box.

Now, look at the dataset.

As both cells match, we get Yes.

Apply Conditional Formatting to Highlight When 2 Cells Match

We have learned 10 methods of getting YES if two cells match so far. Now in this section, we will see how Conditional Formatting can detect when 2 cells match and highlight them.

Step 1:

• Go to the Home tab.
• Choose Highlight Cells Rules from the Conditional Formatting.
• Select Duplicate Values from the list.

Step 2:

• A new dialog box will appear. Choose Duplicate and click OK.

Look at the dataset. When 2 cells match, the color of the cells changes.

Read More: How to Compare Text in Excel and Highlight Differences (8 Quick Ways)

Conclusion

In this article, we showed 10 methods to explain if two cells match then print Yes in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Similar Articles

• How to Compare Two Cells and Change Color in Excel (2 Ways)
• Excel Formula to Compare Two Cells in Different Sheets (3 Examples)
• Statistical Comparison of Two Data Sets Excel
• How to Compare Addresses in Excel (11 Easy Methods)