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

Below is a dataset that contains the names of the school’s tennis and rugby players. Some of them play both games.

Method 1 – Use the Excel IF Function to Return YES If 2 Cells Match

1.1 IF Function with Matching Condition

Steps:

• Go to Cell D5.
• Enter the following formula:

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

When the cells of both columns match, the status is Yes. The columns in the red rectangles are not the same, so they show blank returns.

1.2 IF Function with Odd Data

Steps:

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

• Press Enter.

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

Method 2 – Insert the Excel EXACT Function to Match 2 Cells and Return YES

Steps:

• Go to Cell D5.
• Enter the following formula:

• Press Enter and pull the Fill Handle icon.

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

Steps:

• Enter the following formula in Cell D5:

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

Method 4 – Combine the COUNTIF and IF Functions to Test 2 Cells

Steps:

• Go to Cell D5.
• Enter the following formula:

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

Method 5 – Test 2 Cells Using Excel OR Function and Show YES

Steps:

• Go to Cell D5.
• Enter the formula below:

• Press Enter button and pull the Fill Handle icon.

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

Steps:

• Enter the following formula on Cell D5:

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

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

Steps:

• Go to Cell D5.
• Enter the following formula:

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

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

Steps:

• Enter the following formula in Cell D5:

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

Method 9 – Join IF and TRIM Functions to Test 2 Cells

Steps:

• Go to Cell D5.
• Enter the following formula:

• Press Enter and drag the Fill Handle icon.

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

Steps:

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

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

• Enter the following VBA code:

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

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

• Press OK, put a cells cell reference in the 2nd dialog box.

You’ll get the below result.

Apply Conditional Formatting to Highlight When 2 Cells Match

Steps:

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

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

When 2 cells match, the color of the cells changes.

Download the Practice Workbook

<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel