Highlight Matches or Differences in Two Lists in Google Sheets

Published on

The easiest way to compare two lists and highlight matches or differences is by using the XMATCH function in Google Sheets.

To highlight matches, you can use the following formula:

=XMATCH(lookup_value, lookup_array)

To highlight mismatches, use the following formula:

=AND(lookup_value<>"", NOT(IFNA(XMATCH(lookup_value, lookup_array))))

Where:

  • lookup_value is the relative reference to the starting cell of the first list you want to compare.
  • lookup_array is the absolute range reference of the array you want to compare to.

Examples

Highlight Matches in Two Lists in Google Sheets

Let’s assume you want to compare the lists in A2:A and B2:B and highlight the matches in A2:A.

An example demonstrating how to highlight matches between two lists in Google Sheets using conditional formatting

Follow these steps:

  1. Select the range A2:A (the range you want to highlight).
  2. Click on Format > Conditional Formatting.
  3. Under Format cells if, select Custom formula is.
  4. In the formula field, enter:
=XMATCH(A2, $B$2:$B)
  1. Choose a formatting style and click Done.

This formula compares each value in A2:A with the values in B2:B and highlights the matching values.

This method is useful when, for example, you have two groups of students and want to identify those in the first group accidentally included in the second group.

The XMATCH function checks each value in list 1 (column A) against list 2 (column B) and returns a number (the relative position in list 2) if a match is found. If no match is found, it returns an #N/A error. This results in highlighted cells wherever the formula returns a number.

Highlight Differences in Two Lists in Google Sheets

Highlighting differences is straightforward. You just need to highlight cells where the formula returns an #N/A error. To do this, follow these steps:

An example showing how to highlight differences between two lists in Google Sheets using conditional formatting
  1. Wrap the formula with IFNA to replace #N/A with an empty string:
IFNA(XMATCH(A2, $B$2:$B))
  1. Wrap it with NOT to return TRUE for empty cells and FALSE for matches:
NOT(IFNA(XMATCH(A2, $B$2:$B)))

This formula will highlight all differences in list 1. However, it may also highlight empty cells, which you likely don’t want. To exclude empty cells, use AND as follows:

=AND(A2<>"", NOT(IFNA(XMATCH(A2, $B$2:$B))))

You can use this formula to highlight mismatched values in A2:A.

The process for applying this rule is the same as for highlighting matches. You just need to replace the formula in the Conditional Formatting panel with the one for highlighting differences.

What to Do When the Lists are in Two Different Sheets within a Workbook?

When highlighting matches or differences with the above formulas in one sheet, you won’t encounter any issues. However, when list 1 is in one sheet and list 2 is in another sheet, you need to adjust the formula.

In conditional formatting, when referring to a different sheet, you must use the INDIRECT function.

For example, assume list 1 is in Sheet1!A2:A and list 2 is in Sheet2!B2:B. The formulas (highlight rules) will be:

Highlight Matches:

=XMATCH(A2, INDIRECT("Sheet2!B2:B"))

Highlight Differences:

=AND(A2<>"", NOT(IFNA(XMATCH(A2, INDIRECT("Sheet2!B2:B")))))

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

6 COMMENTS

  1. Hi, Prashanth,

    Thank you for making life easy.

    Can you tell me the syntax for adding three or more sheets to-

    =ArrayFormula(countif(indirect("Sheet2!$B$2:$B"),$A2:$A))>0

  2. So let’s say I have multiple tabs of information and I want to do a match that searches multiple tabs. i.e.= If I type “XX” into either Tab 1, Tab 2, or Tab 3 it will search Tab 4, Tab 5, and Tab 6 for a match. If it finds a match, it will highlight the match in Tab 4, Tab 5, or Tab 6.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.