Recently, one of my students raise the query “How to compare two columns in excel?” There are many ways to perform compare two columns in excel including Vlookup function in excel, Conditional formatting, If Statement, and other simple methods. Depending on your requirements, you need to use the appropriate method. To solve your problem, we have listed all the methods to compare two columns in excel here.
Table of Contents
This is quite an important topic as it is searched by many users. In our previous post, you might have seen how to find duplicates in excel and how to remove them. Now to compare two columns for match, you can use various different processes that are listed below. Take a look at these processes and start using it.
Here we are using simple method ‘=’ that compare cells in the same row.
There are many ways to excel to compare values in two columns to find matched value or different values. Below we will be using If statement to compare two columns in excel for matches and differences.
Here we will tell you how to use If the formula in excel to compare two columns.
If you want to perform both matches and differences together then write this formula.
=If(A2=B2,”Match”,”No Match”)
To compare two columns in excel and find matches using Vlookup function in excel. Here you can check the steps that help you perform the task.
For performing a comparison, we use three functions.
Vlookup: To do research ISNA: To perform the test If: To customize the result
Check the steps of Vlookup formula to compare two columns.
=Vlookup(A2,$B$2:$B$8,1,0)
=ISNA(Vlookup(A2,$B$2:$B$8,1,0)
=If(ISNA(Vlookup(A2,$B$2:$B$8,1,0)),”Missing”,””)
This is how to use vlookup to compare two columns.
To find duplicate values by comparing two columns, you need to use conditional formatting excel formula on the columns. We have discussed other ways also like Excel If statement formula and Vlookup function above but here we are using conditional formatting.
Here you can check how to compare two columns in excel to find matches between two fields. Follow the process and take out the matched value.
This way you can find out the matches by excel compare values in two columns using conditional formatting process.
To find the different values in two columns, we use conditional formatting and using this formatting we highlight all the differing values.
By following the above method, you can compare two columns in excel to find differences using conditional formatting.
Hence these are the four different ways to compare values and to find the matched and different values. Read all the ways carefully and choose the best way to find the matched and different values using these above formulas. Hope that above formulas to compare two columns with each other were useful.
Q1. How to compare multiple columns in Excel in the same row for matches? Count the total duplicates also.
Ans. We have given the procedure to compare two columns in excel for the same row above. But if you want to compare multiple columns in excel for the same row then see the example
=IF(AND(A2=B2, A2=C2),”Full Match”, “”)
Here we have compared data of column A, column B, and column C. After this, I have applied the above formula in column D and get the result.
Now to count the duplicates, you need to use the Countif function.
=If(CountIf($A2:$E2, $A2)=5, “Full Match”, “”)
Q2. Which operator do you use for matches and differences?
Ans. For matches: =
For differences: <>
Q3. How to compare two different tables and pull matching data?
Ans. For this, you can use Vlookup function or Index & Match formula. To understand this thing in a better way we will take an example. Here we will take two tables and now want to do pull matching data. In the first table, you have a dataset and in the second table, take the list of fruits and then use pull matching data in another column. For pull matching, use the formula
=Index($B$2:$B$6,Match($D2,$A$2:$A$6,0))
Q4. How to remove duplicates in excel?
Ans. To remove duplicate data you need to first find the duplicate values. To find the duplicate, you can use various methods like conditional formatting, Vlookup, If Statement, and many more. To get more details to know the process to remove duplicate values Click Here.
Q5. How to find missing data by comparing two columns in excel?
To understand its concept well, we will take an example here. In this data set, we will put values in column 1 and column 2.
=IsError(VLookup(A2,$B$2:$B$10,1,0))
This formula works where the Vlookup function will check whether the fruit listed in column A is also available in column B or not. IsError will return either True or False.
Q6. I have a database of 100 rows that looks like
A1 Seema
A2 Shanti
A3 Suhasini
A4 Shalini
A5 Shweta
And in another table, you have 50 rows and the data looks like
B1 Shalini
B2 Samir
Now I want to highlight cells available in column A where the data is similar as in column B. Only the matched data gets highlighted and leaving other cells white. I have already tried conditional formatting and it’s not working. Is there any other formula apart from conditional formatting to highlight the matched data?
Ans. There are various options to compare two columns in excel. Here we are going to use Vlookup formula to find whether the value appears in the list or not. For column A, use the formula of Vlookup function:
=Not(ISNA(Vlookup(A1,$B:$B,1,False)))
Drag the corner to other cells. In this formula, the Vlookup will look for the value in Cell A1 comparing it with Column B. The Vlookup function in this formula will return ISNA if the match is not found.
Q7. How to compare two columns that give the result as TRUE when all first columns’ integer values are not less than the second column’s integer values. To solve this problem, I do not require conditional formatting, Vlookup, If Statement, and any other formulas. I need the formula to solve this problem.
Ans. You can use the array formula for solving this problem. The syntax is {=AND(H6:H12>I6:I12)}. This will give you “True” as a result whenever the value of Column H is greater than the value in column I else “False” will be the result.
In this tutorial, we have covered all the Excel functions and formulas. Here we have listed questions that our excel users asked. If you have any other questions then you can mention below in the comment box. We will soon get back with the answer as we receive your question.
NGINX Server Nginx, a popular open-source web server, excels at handling high traffic websites efficiently.… Read More
In the realm of web hosting, choosing the right web server is paramount. It acts… Read More
Are indispensable for ensuring smooth, precise linear motion in many industrial applications. Whether in robotics,… Read More
Cyber attacks are becoming more frequent, complex, and damaging. They can disrupt critical operations and… Read More
With the rise of new threats and the increasing complexity of IT environments, organizations need… Read More
1. Introduction In software design, managing complex systems can be challenging. The Facade Design Pattern… Read More