How to Compare Two Columns In Google Sheets (4 Methods)

how to Compare two columns in Google Sheets 12

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.

See also  How to Use Transpose Function In Google Sheets (Easy Guide)

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.

See also  How to Add Text and Text Box 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

Step 1

In the third column of your data write the =qual operator

how to Compare two columns in Google Sheets 1

Step 2

Pass the cell address of column 1

how to Compare two columns in Google Sheets 2

Step 3

Equal sign after the first cell, then pass the cell address of column 2

how to Compare two columns in Google Sheets 3

Step 4

Hit Enter. Now you will get the TRUE or FALSE for same or not same respectively.

how to Compare two columns in Google Sheets 4

Step 5

You can drag this formula for the entire column

how to Compare two columns in Google Sheets 5

This is how easily you can compare two columns using a simple =equal operator or comparison operator.

See also  How to Add/Create Drop Down List in Google Sheets

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.

Step 1

In the third column of your data write the IF statement

how to Compare two columns in Google Sheets 6

Step 2

Define the condition, here our condition is to check A2=B2

how to Compare two columns in Google Sheets 7

Step 3

Add a comma, then In double quotes write anything you want to print when the condition is true

how to Compare two columns in Google Sheets 8

Step 4

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.

how to Compare two columns in Google Sheets 9

=IF(A2=B2,”Same”,”Different”)

Step 5

Hit enter, and you can see how our custom text has replaced the TRUE or FALSE.

how to Compare two columns in Google Sheets 10

Step 6

Drag for the entire column

how to Compare two columns in Google Sheets 11

how to Compare two columns in Google Sheets 12

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.

See also  How to Make a Gantt Chart in Google Sheets (Easy Guide)

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

Step 1

Have some sample data to follow the example

how to Compare two columns in Google Sheets 13

Step 2

in the third column’s first empty cell, write down the IF statement and pass the cell address

how to Compare two columns in Google Sheets 14

Step 3

Pass the condition as I specified above A2<B2 or A2>B2

how to Compare two columns in Google Sheets 15

Step 4

Write custom texts for true and for false in separate double quotes

how to Compare two columns in Google Sheets 16

The formula will look like

=IF(A2<B2,”Yes”,”No”)

Step 5

Hit Enter, and you’re done. You can drag down the formula for the entire column.

how to Compare two columns in Google Sheets 17

how to Compare two columns in Google Sheets 18

See also  How to Make a Bell Curve in Google Sheets [Easy Guide]

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.

Step 1

Have some sample data (Numeric or text)

how to Compare two columns in Google Sheets 19

Step 2

Write the =MATCH function in an adjacent empty cell

how to Compare two columns in Google Sheets 20

Step 3

Pass the cell address you want to search in the other column.

how to Compare two columns in Google Sheets 21

Step 4

Pass the range in which you want to search for the cell address’ value

how to Compare two columns in Google Sheets 22

Step 5

Pass 0 for exact matches and 1 for partial matches, I would recommend 0 for exact matches.

how to Compare two columns in Google Sheets 23

how to Compare two columns in Google Sheets 31

Step 6

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 24

See also  How to Count Unique Values in Google Sheets (4 Methods)

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.

Step 1

Write the VLOOKUP function

how to Compare two columns in Google Sheets 25

Step 2

Pass the cell address you want to search

how to Compare two columns in Google Sheets 26

Step 3

Pass the range (in $ Notation) in which you want to search your key

how to Compare two columns in Google Sheets 27

Step 4

Pass the index as 1

how to Compare two columns in Google Sheets 28

Step 5

Pass the false for getting only exact matches, similarly true for partial matches

how to Compare two columns in Google Sheets 29

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.

how to Compare two columns in Google Sheets 30

This is how we can compare two columns using the VLOOKUP function in google sheets.

See also  How to Use VLOOKUP in Google Sheets (User Guide)

Tips

  • 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

See also  How to Make a Pie Chart in Google Sheets

FAQ

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.

See also  How to Sort by Number in Google Sheets (A-Z or Z-A)

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general