Catalog

How to Compare Two Columns in Google Sheets - A Comprehensive Guide

November 28, 2023 379 views

Embark on a journey of data mastery with our comprehensive guide on comparing two columns in Google Sheets. This handbook is your compass through the intricacies of spreadsheet analysis, offering clear pathways to detect differences and enhance decision-making. Whether you're a novice or a seasoned user, this guide is tailored to empower you with invaluable skills for efficient column comparison. Uncover the secrets to precision and productivity as we unravel the art of data differentiation in Google Sheets.

Part 1: How to Compare Two Columns in Google Sheets - Using 3 Common Functions

Here is a step-by-step tutorial on how to compare two columns in Google Sheets using 3 common functions:

Function 1: COUNTIF Function

 COUNTIF formula

COUNTIF formula

The COUNTIF function is used to count the number of cells in a range that match a specific criterion. You can use this function to compare two columns by counting the number of cells in one column that match the values in the other column.

Step 1: Enter your data into two columns in your Google Sheet. For example, let's say you have a list of customers and their corresponding purchase amounts in columns A and B.

Step 2: In a new column, enter the following formula:

=COUNTIF(A:A, B2)

This formula will count the number of cells in column A that match the value in cell B2.

Step 3: Copy the formula down to the rest of the cells in the column. This will calculate the number of matches for each customer.

Function 2: VLOOKUP Function

 VLOOKUP Formula

VLOOKUP Formula

The VLOOKUP function is used to look up a value in one column and return the corresponding value from another column. You can use this function to compare two columns by finding the corresponding value in one column for each value in the other column.

Step 1: Enter your data into two columns in your Google Sheet. For example, let's say you have a list of customers and their corresponding email addresses in columns A and B.

Step 2: In a new column, enter the following formula:

=VLOOKUP(A2, B:B, 2, FALSE)

This formula will look up the value in cell A2 in column A and return the corresponding value from column B. The "FALSE" parameter indicates that you want to use an exact match.

Step 3: Copy the formula down to the rest of the cells in the column. This will find the email address for each customer.

Function 3: MATCH Function

MATCH formula

MATCH formula

The MATCH function is used to find the position of a value in a range of cells. You can use this function to compare two columns by finding the position of each value in one column in the other column.

Step 1: Enter your data into two columns in your Google Sheet. For example, let's say you have a list of products and their corresponding prices in columns A and B.

Step 2: In a new column, enter the following formula:

=MATCH(A2, B:B, 0)

This formula will find the position of the value in cell A2 in column B. The "0" parameter indicates that you want to use an exact match.

Step 3: Copy the formula down to the rest of the cells in the column. This will find the position of each product in the price list.

Examples

Here are some examples of how to use these functions to compare two columns in Google Sheets:

  • Use the COUNTIF function to count the number of customers who have made a purchase of more than $100.

  • Use the VLOOKUP function to find the email address of a customer based on their name.

  • Use the MATCH function to find the position of the product with the highest price.

Part 2: How to Compare Two Columns in Google Sheets - Other Methods

Here is a step-by-step tutorial on how to compare two columns in Google Sheets using other methods:

Method 1: Using Add-on

Google Sheets has several add-ons that can be used to compare two columns. One such add-on is Ablebits Compare Sheets. This add-on can be used to compare two columns for exact matches, duplicate values, and unique values.

Step 1: Install the Ablebits Compare Sheets add-on.

  • Go to Tools > Add-ons and click Get add-ons.

Select “Get add-ons” in Add-ons tab.

Select “Get add-ons” in Add-ons tab.

  • Search for Ablebits Compare Sheets and click Install.

Search for Ablebits Compare Sheets.

Search for Ablebits Compare Sheets.

  • Click Open.

Select Install and open extension.

Select Install and open extension.

Step 2: Open the Google Sheet that contains the two columns you want to compare.

Step 3: Click Extensions > Ablebits Compare Sheets > Compare columns.

 Select compare columns

Select compare columns

Step 4: Select the two columns you want to compare.

Step 5: Select the type of comparison you want to perform.

  • Exact matches: This will find all of the rows in which the values in the two columns are exactly the same.

  • Duplicate values: This will find all of the rows in which the values in the two columns are the same, regardless of the order of the columns.

  • Unique values: This will find all of the rows in which the values in one column are unique, meaning that they do not appear in the other column.

Compare columns on a sheet box.

Compare columns on a sheet box.

Step 6: Click Compare.

Enter Compare button.

Enter Compare button.

Step 7: The results of the comparison will be displayed in a new sheet.

Method 2: Using Equal operator

Using Equal formula

Using Equal formula

You can also use the equal operator (==) to compare two columns in Google Sheets. This method is only useful for comparing two columns for exact matches.

Step 1: Enter the following formula in a new column:

=A2=B2

This formula will compare the value in cell A2 to the value in cell B2 and return TRUE if the values are the same, and FALSE if the values are different.

Step 2: Copy the formula down to the rest of the cells in the column. This will compare the values in the two columns for each row.

Here is a brief overview of the differences between the 2 methods:

Feature

Ablebits Compare Sheets Add-on

Equal operator

Ease of use

More complex to use, requires installation of an add-on

Simpler to use, only requires knowledge of the equal operator

Functionality

Can compare two columns for exact matches, duplicate values, and unique values

Can only compare two columns for exact matches

Flexibility

Can compare two columns in different sheets

Can only compare two columns in the same sheet

Output

Displays the results of the comparison in a new sheet

Displays the results of the comparison in the same sheet

Overall, the Ablebits Compare Sheets Add-on is a more powerful and flexible tool for comparing two columns in Google Sheets. However, the equal operator is a simpler and more straightforward method for comparing two columns for exact matches.

Part 3: Best Free Alternative to Google Sheets - WPS Spreadsheet

WPS Spreadsheet is a free and powerful spreadsheet application that is compatible with Microsoft Excel, Google Sheets, LibreOffice Calc, and OpenOffice Calc. It is also compatible with common file formats such as .xls, .xlsx, .xlsm, .xlt, and .csv. You can edit Excel files on mobile devices, computers, or web pages.

WPS Spreadsheet is a great choice for users who need a powerful and versatile spreadsheet application. It is also a good choice for users who need to collaborate on spreadsheets with others.

WPS Spreadsheet

WPS Spreadsheet

Features:

  • Full compatibility with Microsoft Excel

  • Support for online documents

  • Safe and stable

  • Lightweight and easy to use

  • Rich features and functions

  • Cross-platform compatibility

How to Download WPS Office

Step 1: Go to the WPS Office website: https://www.wps.com/download/

 Insert WPS Office website.

Insert WPS Office website.

Step 2: Click the Download button.

Select Download button.

Select Download button.

Step 3: Select the appropriate version of WPS Office for your operating system.

Step 4: Click the Download button again.

Step 5: Once the download is complete, open the downloaded file and run the installation program.

Step 6: Follow the on-screen instructions to complete the installation process.

I have been using WPS Spreadsheet for several years and I am very impressed with it. It is a powerful and versatile spreadsheet application that is easy to use. I have found it to be a great alternative to Microsoft Excel.

WPS Spreadsheet is a great choice for a wide range of users, including:

  • Students

  • Businesses

  • Home users

Overall, I highly recommend WPS Spreadsheet to anyone who needs a powerful and versatile spreadsheet application.

Trustpilotstars4.8
WPS Office- Free All-in-One Office Suite
  • Use Word, Excel, and PPT for FREE, No Ads.

  • Edit PDF files with the powerful PDF toolkit.

  • Microsoft-like interface. Easy to learn. 100% Compatibility.

  • Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

5,820,008 User
avator
Algirdas Jasaitis
logo

FAQS:

  • Can I compare more than two columns simultaneously?

Google Sheets allows you to compare more than two columns simultaneously using various methods, including nested formulas, COUNTIF functions, or dedicated add-ons. Nested formulas and COUNTIF functions provide flexible comparison options, while add-ons offer user-friendly interfaces and extended functionalities. The choice of method depends on the complexity and flexibility required for the specific comparison task.

  • Can Google Sheets automatically highlight differences between columns?

Google Sheets doesn't have an automatic feature to highlight differences between columns. You can manually achieve this by using IF functions to identify differences and applying conditional formatting to highlight them

Summary

Master column comparison in Google Sheets with a guide featuring COUNTIF, VLOOKUP, and MATCH functions. Explore alternative methods like the Ablebits Compare Sheets add-on. Discover WPS Spreadsheet as a free alternative with Microsoft Excel compatibility. FAQs cover comparing multiple columns and the absence of automatic highlighting in Google Sheets.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.