How to Compare Two Columns in Excel
If you’re working in Excel, it’s highly likely you’ll need to compare two columns of data at some point. This could be to find duplicates, highlight matching items, or identify differences between data. Here are some simple methods for comparing two columns in Excel.
Method 1: Using Conditional Formatting
Conditional Formatting is an excellent tool to use for comparing two columns in Excel. Here’s how it works:
1. Select the cells you want to compare.
2. Click the “Conditional Formatting” button in the “Home” tab.
3. Select “Highlight Cells Rules” > “Duplicate Values” from the drop-down menu.
4. Choose a formatting style to highlight matching cells.
5. Click OK and then test by entering a few matching cells.
You will now see any matching cells highlighted in your chosen format.
Method 2: Using the MATCH Function
If you want to compare cells and see their position in a column, use the MATCH function. Here’s how:
1. In a blank cell, type “= MATCH (Cell to Compare, Column to Compare to, 0).”
2. Drag the formula down to the end of the column, and the function will highlight matching cells with a number.
3. You can even add “IF” statements to display a message if the match isn’t found.
Method 3: Using the COUNTIF Function
Another way to find matching or unique cells is to use the COUNTIF function, which counts the number of cells that match specific criteria. Here’s how to use it:
1. In a blank cell, type “= COUNTIF (Range to Compare, Criteria to Search for).”
2. Drag the formula to the end of the cell, and Excel will display a count for that cell’s match.
Method 4: Using VLOOKUP
VLOOKUP helps to compare two columns of data with multiple columns. Here’s how to use it:
1. Highlight a single cell in an empty column.
2. Type “= VLOOKUP (Cell to Compare, Table Array, Column to Compare, 0)” in the cell.
3. The function will return a value if the two cells match.