How to Compare Two Columns in Google Sheets

How to Compare Two Columns in Google Sheets

The human eye is not very good at detecting minute differences or similarities between data objects when dealing with vast amounts of data. Fortunately, data processing tools like Google Sheets can notice details that even the most skilled human eye could overlook.

Google Sheets can automate the procedure and produce results almost immediately with the appropriate formulas. Comparing columns, identifying discrepancies, and highlighting commonalities are all tasks that may be completed quickly, accurately, and easily.

In this article, we will demonstrate numerous practical techniques to compare two columns in Google Sheets.

How to Compare Two Columns for Exact Row Matches

Finding exact row matches is the most straightforward method of comparing two columns. This only requires a comparison row by row. Simply look up which rows share the same values and which have different values in Google Sheets, then display the results in a third blank column.

Simply create a new column for the results, and it will show a TRUE if two items in the same row match; otherwise, it will show a FALSE.

For a comparison like this, the formula you would employ is:

how to compare two columns in google sheets 1

This formula determines whether the information in rows 2 of both columns matches. You will receive a TRUE result if they do. If not, the outcome is FALSE.

The steps are as follows:

  1. In the second row of column C (in cell C2), insert the formula: =A2=B2
  2. Press the enter key.
  3. Double click the fill handle to copy the same formula to the rest of the cells in column C.

The outcomes of each comparison should now be shown in column C.

how to compare two columns in google sheets 1

Compare Two Columns and Show Meaningful Text From Numerical Values

You may use an IF statement on numerical data, just like in the example above. As a logical expression, you could use whatever operator or formula you choose to display results for. You may, for instance, use the following formula:

See also  How to Remove Hidden Data in Google Sheets

how to compare two columns in google sheets 2

This formula was used in the example below to determine whether Pizza Shop A was more affordable than Pizza Shop B.

how to compare two columns in google sheets 3

Compare Two Columns and Highlight Matching Rows

You can format cells based on a condition using the fantastic technique of conditional formatting. You can use conditional formatting if you prefer to have rows with matching data highlighted rather than results displayed in a separate column.

Here’s how to use conditional formatting in Google Sheets to highlight matching rows:

  1. Click the Format menu from the menu bar.

how to compare two columns in google sheets 4

  1. Select the ‘Conditional Formatting’ option.

how to compare two columns in google sheets 5

  1. By doing so, the sidebar labeled “Conditional format rules” will open to the right of the window.

r83J8rrvuN17HWaBs512qkBs

  1. Enter the range of cells you want to compare in the input box next to “Apply to range.” For our instance, we can enter A2:B9.

how to compare two columns in google sheets 6

  1. Then click on the dropdown arrow next to “Format cells if” in the Format rules section.

Conditional format rules

  1. Select “Custom formula is” from the dropdown menu that appears.

how to compare two columns in google sheets 7

  1. A text box will be visible underneath the drop-down menu. There, enter the custom formula “=$A2=$B2”.

how to compare two columns in google sheets 8

  1. Select “Formatting style” and then select “Fill Color.”

how to compare two columns in google sheets 9

  1. Pick the color you want to use to highlight rows and cells that match.

how to compare two columns in google sheets 10

  1. Finally, to finish up Conditional formatting, click the Done button.

how to compare two columns in google sheets 11

Now, all rows and cells that match should be highlighted in the color

how to compare two columns in google sheets 12

Finding Corresponding Data by Comparing Two Columns in Google Sheets Conditional Formatting

The methods we have explored thus far essentially determined if cells in the same row were matching. What happens, though, if you want to compare two columns and determine which column A values, regardless of which row they appear in, are recurring in column B?

Here’s how to highlight duplicate data using conditional formatting:

  1. Click the Format menu from the menu bar.
  2. Select the ‘Conditional Formatting’ option.

how to compare two columns in google sheets 13

  1. By doing so, the sidebar labeled “Conditional format rules” will open to the right of the window.

how to compare two columns in google sheets 14

  1. Enter the range of cells to which you want to apply the formatting in the input box next to “Apply to range.” We just want to see the highlights of column B in our example. Thus, we enter B2:B9.
See also  How to Clean and Format Raw Data in Google Sheets

  1. Then click on the dropdown arrow next to “Format cells if” in the Format rules section.
  2. Select “Custom formula is” from the selection option that opens.
  3. You will see an input box below the dropdown list. Type your custom formula there: =COUNTIF($A$2:$A$9,B2)>0

how to compare two columns in google sheets 15

  1. Select “Formatting style” and then select “Fill Color.”
  2. Choose the color you want to use to highlight related cells or rows.
  3. Finally, let Conditional formatting finish its job by clicking the Done button.

how to compare two columns in google sheets 16

How to Compare Two Columns in Google Sheets – Using VLOOKUP Function

In this section, we’ll look at how to use the VLOOKUP function in Google Sheets to compare two columns. VLOOKUP is a sophisticated function to accomplish the same task and VLOOKUP does not return the row number or the Boolean volume; instead, it returns a “not found” error when the value of the search key cannot be found in both columns.

  1. Write the VLOOKUP function

how to compare two columns in google sheets 17

  1. Enter the search criteria for the cell number.

how to compare two columns in google sheets 18

  1. Pass the area you want to search for your key in ($ Notation).

how to compare two columns in google sheets 19

  1. Pass the index as 1

sCBeA6VhTDHA0k9N6KTBeRqp

  1. Pass the true if you want to retrieve just precise matches, and the false if you want partial matches.

how to compare two columns in google sheets 20

If the value is present in both columns, the VLOOKUP function will return the same result; otherwise, it will return an error.

how to compare two columns in google sheets 21

Comparing Columns In Google Sheets

As you can see, there are a ton of different ways to use Google Sheets. You can adapt these methods to make highly potent data comparisons depending on what you need Google Sheets to do. The applications can be as straightforward as organizing an inventory of your record collection or as complex as examining a company’s product line.

To make your spreadsheet more organized, check out FileDrop. FileDrop is a sidebar app for Google Sheets and Docs. With it you can insert any file type you need in your Google Sheets cells. Start your trial today!

🎉 Get access to FileDrop PRO for $̶1̶2̶0̶
This is default text for notification bar