In this article, we learn how to compare two columns in google sheets.
This article is designed for beginners to learn these methods from scratch and also for the people who are already working on google sheets to go through and revise or replace their old methods and learn new and quick methods to learn how to compare two columns in google sheets.
Comparison of two columns in google sheets can be done for different purposes such as to check if both columns are equal, to check if both columns are not exactly equal. Most of the time we need to have the same columns, but we humans cannot manually identify the smaller differences or similarities. We will end up making an error that could waste our time, and our work, and can be very frustrating to repeat a long tedious work. So don’t be like that. Instead, use some methods to do this in seconds, save your time, save energy and avoid tedious work.
In this particular tutorial, we will talk about 5 methods to compare columns, and we will also learn to perform certain actions in certain scenarios.
Why do we need to Compare two columns in Google Sheets
As I discussed above in the introduction, we humans make so many errors, the computer is itself a dumb machine, but it’s our logic that allows us to make computers work accurately, if we provide accurate logic then the computer will not do any mistake. So, we will use some functions and formulas to compare two columns, that comparison can be done for checking the similarities, or differences, or we want to double-check the data, we want to compare a long data set, or for any reason. So, keeping in mind all these reasons, we should know how to compare two columns in google sheets.
How to Compare two columns in Google Sheets
To learn how to compare columns in google sheets, we have some sample data sets and some real-life examples that help you understand why we need to learn this, and also how we use them to solve our day-to-day problems. Let us move on to the methods, and let us see how each method works.
How to compare two columns in google sheets – Using Equal operator
In this section, we will learn how to compare two columns in google sheets using the equal operator, now equal operator is widely used in every programming language to check whether the two values are equal or not. This method returns only true or false checking two columns or cells to return true for same and false for not same. This is the easiest method you can use to quickly compare two columns or cells. Let’s see the below steps
In the third column of your data write the =qual operator
Pass the cell address of column 1
Equal sign after the first cell, then pass the cell address of column 2
Hit Enter. Now you will get the TRUE or FALSE for same or not same respectively.
You can drag this formula for the entire column
This is how easily you can compare two columns using a simple =equal operator or comparison operator.
How to compare two columns in google sheets – Using IF Statement
In this section, we will learn how to compare two columns in google sheets using the IF statement, now again IF statement is also a very powerful logic function used in every programming language to build if-else logic. We will check the condition on the IF statement, and perform a certain action on true or false. True or False may not be meaningful to many people, so instead of true or false, we would like to write something meaningful when the condition is true and something else when the condition is false.
In the third column of your data write the IF statement
Define the condition, here our condition is to check A2=B2
Add a comma, then In double quotes write anything you want to print when the condition is true
After a comma, inside another double quote write anything you want to print when the condition is false.
The overall formula will look like this.
Hit enter, and you can see how our custom text has replaced the TRUE or FALSE.
Drag for the entire column
This is how we can do the same as an equal operator but with an extra feature to print custom text using the IF statement.
How to compare two columns in google sheets – Numeric data comparison using IF statement
We may have numeric data in our columns, and we want to compare them using the IF statement, same logic, just a different operator. We are learning how to compare two columns in google sheets – numeric data comparison using IF statement.
I have sample data that have numeric values (prices) and we will see how can we customize the same logic for numeric values.
This time we will not use the =equal operator because we don’t want to check the equality, we want to check the less than or greater than, so we will use A2<B2, to check which price is high. Let’s understand it practically in the below step-by-step methods
Have some sample data to follow the example
in the third column’s first empty cell, write down the IF statement and pass the cell address
Pass the condition as I specified above A2<B2 or A2>B2
Write custom texts for true and for false in separate double quotes
The formula will look like
Hit Enter, and you’re done. You can drag down the formula for the entire column.
How to compare two columns in google sheets – Using Match Function
Match Function is a built-in function in google sheets, it works like a VLOOKUP function, but has some limitations. So, we will see how to compare two-column, or part of a column in another column in google sheets using the match function. Note that, the match function neither returns a Boolean value (true or false), nor the exact value, it returns the row number where your searched item is found and returns an error when your item is not found.
For this section I have a simple example, I would recommend you to follow the steps with me and you will never forget this function if you practice it.
Have some sample data (Numeric or text)
Write the =MATCH function in an adjacent empty cell
Pass the cell address you want to search in the other column.
Pass the range in which you want to search for the cell address’ value
Pass 0 for exact matches and 1 for partial matches, I would recommend 0 for exact matches.
Hit enter and you’re done: Use $ notation if you want to drag the formula for the entire column, you will be getting the row the number in which your searched item is found, otherwise, you will get an error.
How to compare two columns in google sheets – Using VLOOKUP Function
In this section, we will see how to compare two columns in google sheets using VLOOKUP Function. This method is relatively the same as the last method we saw “Match function”, but VLOOKUP is a more advanced function to do the same thing. and VLOOKUP does not return the row number, nor the Boolean volume, it returns the search key is found in both columns and returns a “not found” error when the value is not found in both of the columns. So we will use the same example, only we will change the Match function with the VLOOKUP function.
Write the VLOOKUP function
Pass the cell address you want to search
Pass the range (in $ Notation) in which you want to search your key
Pass the index as 1
Pass the false for getting only exact matches, similarly true for partial matches
The VLOOKUP function will return the same value if found in both columns and returns an error if the value is not found in both columns.
This is how we can compare two columns using the VLOOKUP function in google sheets.
- Two blank cells are true
- One blank cell and one with any data is false
- A cell that has nothing but some whitespaces is different from any other blank cell that has no whitespaces
- Remember that all the methods used for comparison are not case sensitive, Cat is equal to CAT, CAt, cAt, caT, or any combination
- Remember that the match function returns the row number
- Use $ notation in Match Function to avoid the missing cell references.
- After selecting a cell range, press F4 to convert the range into $ notation.
- Know the difference, VLOOKUP returns the value itself, and the match method returns the row number, if the statement returns true or false by default, but can be customized.
- =equal operator returns true or false and cannot be customized
What method should I use to compare two columns in google sheets?
It depends on your problem statement, the above article talks about the different problems statement in the introduction part that may help you understand your problem, you must know the problem before the solution, all the above methods taught are different, and doing different work in some way, for an easy and simple match you should use equal operator method that is extremely simple, but if your problem is complex you may use IF statement with customized text, similar goes for other methods, it depends on your if you want to get the row number as a result or want to print the actual values when found in both columns.
In this article, we learned how to compare two columns in google sheets, we talked about 4 easy methods that I described very simply with a screenshot for every small step for your ease. I have used simple real-life examples for you to make you understand the scenario and let you begin solving your problems.
I hope you find this tutorial useful. Thank you so much, see you soon. Keep learning with Office Demy.