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.

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)

5. 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:

1. Wrap the formula with IFNA to replace #N/A with an empty string:

IFNA(XMATCH(A2, $B$2:$B))

2. 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")))))

Conclusion

Using the XMATCH function in conditional formatting is a simple and efficient way to highlight matches or differences between two lists in Google Sheets. Whether the lists are in the same sheet or across different sheets, these formulas provide a flexible approach for quick comparison without helper columns.

For more advanced techniques and similar use cases, explore the complete guide here: The Ultimate Guide to Conditional Formatting in Google Sheets

Resources

• Highlight Matching Data Across Two Sheets in Google Sheets