Compare Two Lists In Google Sheets

What Is Compare Two Lists In Google Sheets?

The Compare Two Lists in Google Sheets feature helps users to compare two sets of data from a single or multiple datasets to identify similar or different data.

The Google Sheets Compare Two Lists finds it usewhen working with large datasets, as it saves time and reduces errors, that are likely to occur when comparing lists manually.

For example, the dataset given below consists of two lists with some random values. Let us compare them using the basic comparison method using the equal sign “=”.

Select cell C2, enter the formula =A2=B2, press “Enter” and drag the formula from cell C2 to C3 using the fill handle, to get the following results.

The output indicates that the first set of values in the lists in row 2 are the same or equal, hence TRUE, and the second set of compared values are not the same, hence FALSE.

How To Compare Two Lists In Google Sheets?

We can Compare Two Lists In Google Sheets using various methods, namely,

1. Using conditional formatting.
2. Using MATCH in INDEX.
3. Using ISERROR with VLOOKUP.

Method #1 – Using conditional formatting –

 Step 1: Choose one set of data to be compared à select the “Format” tab – click the “Conditional formatting” option, as shown below.

The “Conditional format rules” pane appears on the right side, as shown above. Here, click the “Add another rule” option.

Step 2: We see the “Single Color” and the “Color scale” tabs. Here, click the “Single color” tab, 

• First, select the required cell or cell range in the “Apply to range” field that must be formatted.
• Next, select the “Custom formula is” option from the “Format cells if…” drop-down and enter the formula accordingly in the “Value or formula” field, then select the required highlight color from the “Formatting style”, as shown below.
• Finally, click the “Done” option.

The cells, either unique or different, are highlighted in the dataset according to the formula applied.

Method #2 – Using MATCH in INDEX –

Step 1: Select and empty cell and type =INDEX(reference, where reference argument value will be column or cell range of first list to be compared.

Step 2: Continue the formula and insert the MATCH function as =INDEX(reference,MATCH( then,insert the argument values. Here, range will be the other list to be compared to, close the brackets and press “Enter”.

Method #3 – Using ISERROR with VLOOKUP –

Step 1: Select and empty cell and type =ISERROR(

Step 2: Continue the formula and insert the VLOOKUP function as =ISERROR(VLOOKUP( then, insert the argument values, close the brackets and press “Enter”.

Examples

Let us consider some Compare Two Lists In Google Sheets examples for the above-mentioned methods.

Example #1 – Find matches using MATCH in INDEX

The dataset below consists of items bought for a living room and their respective costs. Some items are must be bought for the master bedroom. Therefore, we will find matches using MATCH in INDEX and retrieve the cost for the items using the first list.

The steps to find matches using MATCH in INDEX are,

Step 1: Select cell E2 and enter the formula =Index($A$2:$B$7,MATCH(D2,$A$2:$A$7,0),2), as shown below.

[Note: As we insert the formula and press “Enter” the selected cell range will automatically become absolute.]

Step 2: Next, press “Enter” and drag the formula from cell E2 to E7 using the fill handle, to get the following results.

In the output retrieved, we see the formula compares items amount from the first dataset with the second dataset items and for every match, it retrieves the amount and returns the “#NA” error for no matches.

Example #2 – Find differenes using conditional formatting

Consider the dataset of cosmetic brands that are famous worldwide and in India as two lists to be compared and find differences using conditional formatting.

The steps to find differences using conditional formatting are,

Step 1: Choose first list, i.e., cell range A2:A10 – select the “Format” tab – click the “Conditional formatting” option, as shown below. Then, the “Conditional format rules” pane appears on the right side, as shown below. Here, click the “Add another rule” option.

Step 2: Click the “Single color” tab and proceed as follows:

• First, check if the cell range is selected, if not, select the required cell or cell range in the “Apply to range” field that must be formatted.
• Next, select the “Custom formula is” option from the “Format cells if…” drop-down and enter the formula =COUNTIF(C:C,A2)=0 in the “Value or formula” field, then select the required highlight color, here Magenta, from the “Formatting style”, as shown below.
• Finally, click the “Done” option.

We get the output shown below where the unique or the differences in the lists are highlighted.

Example #3 – Find Missing Values Using ISERROR with VLOOKUP

We know that the INDEX+MATCH function combination is an alternative for the VLOOKUP function. Therefore, we will consider the Example #1 dataset and retrieve the compared and matched data using the VLOOKUP and find Missing Values Using ISERROR.

The steps to Find Missing Values Using ISERROR with VLOOKUP are,

Step 1: Select cell E2 and enter the formula =VLOOKUP(D2,A2:B7;2,0), as shown below.

Step 2: Press “Enter” and drag the formula from cell E2 to E7 using the fill handle, to get the following results.

Step 3: Now, select cell F2 and enter the formula =ISERROR(E2), as shown below.

Step 4: Finally, press “Enter” and drag the formula from cell F2 to F7 using the fill handle, to get the following results.

[Note: We can also enter the formula as =ISERROR(VLOOKUP(D2,A2:B7;2,0)) in the result cells. We will get the same output as displayed in column F.]

Example #4 – Find duplicates using conditional formatting

Let us consider the Example #2 dataset of cosmetic brands, again, as two lists to be compared and this time find duplicates using conditional formatting.

The steps to find duplicates using conditional formatting are,

Step 1: Choose first list, i.e., cell range A2:A10 – select the “Format” tab – click the “Conditional formatting” option, as shown below. Then, the “Conditional format rules” pane appears on the right side, as shown below. Here, click the “Add another rule” option.

Step 2: Click the “Single color” tab and proceed as follows:

• First, check if the cell range is selected, if not, select the required cell or cell range in the “Apply to range” field that must be formatted.
• Next, select the “Custom formula is” option from the “Format cells if…” drop-down and enter the formula =COUNTIF(C:C,A2)>0 in the “Value or formula” field, then select the required highlight color, here cyan, from the “Formatting style”, as shown below.
• Finally, click the “Done” option.

We get the output shown below where the matches found or the duplicates are highlighted.

Important Things To Note

1. Formulas, such as COUNTIF or VLOOKUP, allow for more customization and potential for automation of the comparison process and are used to Compare Two Lists in Google Sheets.
2. In conditional formatting method, selecting the correct cell range is important. Or else incorrect cells will get highlighted cells.
3. If it is a smaller dataset, the manually comparison and the basic equal to method might be useful. However, if the data is not organized it will pose a problem.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand the Compare Two Lists in Google Sheets feature and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Compare Two Lists In Google Sheets. We learn how to Compare Two Lists In Google Sheets its methods, examples, work template. You can learn more from the following articles.