How to Compare Two Columns in Excel (In-Depth Guide)
By Indeed Editorial Team
Published 16 May 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
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:
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".
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".
If the value in cell "A2" is the same as in cell "B2", the formula outputs "Match" in cell "C3".
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:
Data set 1
Data set 2
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:
Want to invite
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".
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:
Candidates of interest
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.
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.
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.
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:
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.
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.
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.
Explore more articles
- A Guide to Assertiveness (Benefits, Tips and Skills)
- What Are the CMA Pros and Cons? (Plus Definition and FAQs)
- Comprehensive Guide to a Milestone vs. Task (With Tips)
- A Complete Guide to Hot Desking: With Definition and Tips
- What Is Inside Sales? (With Skills, Benefits and Tips)
- What Is a Personal Development Plan? With Template
- What Is a Functional Structure? (With Pros and Cons)
- What Are Personal References and Professional References?
- Web Server vs. Application Server: How Do They Differ?
- What Is Job Security? (Definition and How to Improve It)
- How To Prioritise Tasks in the Workplace (With Tips)
- Guide to Terminal Value: Definition, Usage and Importance