How to Compare Two Columns in Excel (In-Depth Guide)

Excel has many functions for manipulating and analysing data, and the function to compare two columns is a useful feature for many use cases. When comparing columns, you can identify the main similarities and differences in a data set, highlight unique data or find specific comparative information quicker. Developing your knowledge of Excel can help you grow your professional knowledge to help you be more productive. In this article, we discuss the process of comparing two columns in Excel in various ways.

How to compare two columns in Excel using the IF function

Using the IF function is a simple method of learning how to compare two columns in Excel. The basic structure of this method is using the "=" operator, but with the IF function. This way, you're able to choose the value that gets outputted when you get a match or difference. The IF formula that returns "Match" when data values are the same and "Mismatch" when they're different is "=IF(A2=B2,"Match","Mismatch")". Here's how to use the IF function in Excel:

1. Create a third column where you want to input your end values. If your information is in columns "A" and "B", you can put the formulas in column "C".

2. If you have headers in the first row and your lists start from the second row, type the formula "=IF(A2=B2,"Match","Mismatch")" into cell "C2".

3. If the value in cell "A2" is the same as in cell "B2", the formula outputs "Match" in cell "C3".

4. Once you have the formula completed in cell "C3", you can drag the formula into the other columns by selecting cell "C3" and then clicking the bottom right of the cell and dragging down the column.

What is the VLOOLUKUP function in Excel?

The IF function is efficient for simple and more beginner uses, but the VLOOKUP algorithm works better in more practical scenarios. Excel spreadsheet may not always have two side-by-side data lists for you to compare, which is where the IF algorithm is more practical. When you have more scattered data, the VLOOKUP algorithm can be more practical to use. The syntax of the VLOOKUP formula is "=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])". The "lookup_value", is what you're looking for or trying to compare. This can be a cell reference or you can type a value.

The "table_array" and "column_index_num" is how you tell Excel where to look. If you choose columns "A" to "D" as your "table_array", entering "2" for the "column_index_num" tells Excel to search column "B". For "[range_lookup]", you can enter "TRUE" or "1" if you want an approximate match or "FALSE" or "0" if you want an exact match. Excel chooses "TRUE" by default if you don't enter anything.

Examples of using VLOOKUP to compare two columns in Excel

Here's how you can use the VLOOKUP function in different ways:

Find matches and differences

Here's an example of using the VLOOKUP function to find matches:

If your goal is to find data in "Column B" that's also in "Column A", you can enter "=VLOOKUP(B2,$A$2:$A$6,1,0)" into cell "C2" and drag the formula down to cell "C6". It's important to add the "$" signs into the "table_array" to ensure it doesn't change as you copy the formula into other cells. If there are no matches, the formula outputs "#N/A" and if there's a match, it outputs the value you're looking up.

Output specific values

If you want to output specific values instead of the reference cell or "#N/A", you can combine the VLOOKUP formula with the IF and ISERROR formulas. Here's an example of all the functions being used together:

If you have a list of people who are available to attend an event and a second list of people you want to invite, you can use the VLOOKUP formula to identify who among your intended invitees are available. The IF function lets you choose what values a formula outputs based on whether it matches a specific value. The ISERROR function tells the IF function to output "Not available" if the VLOOKUP formula outputs an error and "Available" if it doesn't output an error. You can enter "=IF(ISERROR(VLOOKUP(B2,$A$2:$A$7,1,0)),"Not available","Available") into cell "C3" and drag down to cell "C5".

Extracting data

Apart from finding matches or differences in two data sets, you can also extract data from them to sort information out in a more organised manner. You can save a lot of time by implementing variations of the VLOOKUP function that can reduce time wasted on tedious tasks. Here's an example of how to use the VLOOKUP function to extract data:

You may have a long list of students' test scores, but you only want to see the results of specific students. You can enter "=VLOOKUP(D2,$A$2:$B$7,2,0)" into cell "E3" and drag downwards. Whenever you type a name into any cell in column "D", it either outputs the student's test scores if available or returns "#N/A" if your data set doesn't have their test score.

Related: Excel Skills: Definition and Examples

Other ways to highlight and compare information in columns

The IF function and VLOOKUP formula are two common ways to compare, highlight or filter data from two columns. There are also a few other ways you can sort through information on your Excel spreadsheets depending on your proficiency with Excel. Here are some alternate ways of comparing data:

Highlighting equivalent data

If you're comparing two columns of simple data and want an easy and convenient way to highlight any equivalent or duplicate values without using a third column, you can use the Excel's conditional formatting feature. Highlight the two columns you want to compare by dragging over all the values with your cursor, go to the "Home" tab in Excel and click on the "Conditional Formatting" menu. In this menu you can click on "Highlight Cell Rules" then "Duplicate Values". You can then customise how you want to format the result and how you want Excel to highlight the duplicate values.

Related: 16 Must-Have Data Analyst Skills (Plus Job Responsibilities)

Highlighting unique data

Highlighting unique data is the opposite of comparing equivalent data, but the steps to achieve the result are similar. In the "Conditional Formatting" menu, when prompted to format cells, select "Unique" instead of "Duplicate" and then choose which colour and highlight you wish to display the values in. This method is suitable for smaller columns but comes with limitations when trying to apply it to larger data sets.

Related: Computer Skills: Definitions and Examples

Tips to improve organisation in Excel

When learning how to compare two columns in Excel, keeping your spreadsheet organised can help you analyse your data more efficiently: Here are some tips to help with organising your spreadsheets:

Colour coding

Colours can help you easily identify specific values without needing to read all the cells. Selecting generic colours such as red and green to highlight matches or differences in data also helps give more context to data. This can help a reader unfamiliar with your spreadsheet to more easily understand its purpose.

Contrast

There are a few ways to create contrast in Excel documents that can help improve the readability of a spreadsheet. You can create contrast through text by bolding or italicising content or by adding borders between different data sets or values. Adding these formatting differences can help you identify more relevant content faster.

Related: How to Split Cells in Excel Using Delimiter and Fixed Width

Spacing

Keeping all your information in a condensed area can make it difficult to read, as there's too much information. Creating spacing in your document can help readers better distinguish different sections of your spreadsheet. You can create space by leaving blank columns and rows or resizing columns and rows with data to ensure there's noticeable space between the values and the borders of the cells.

Please note that none of the companies, institutions or organisations mentioned in this article are affiliated with Indeed.