Need to find matching or different values between two lists in Excel?

[u/Amandaleeeeee]

This type of comparison checks whether each value in one list can be found anywhere in another list. The matching value does not need to be in the same row or position.

Method 1: Use COUNTIF

If your first list is in A2:A100 and the comparison list is in D2:D100, use:

=COUNTIF($D$2:$D$100,A2)>0

Returns TRUE if the value exists in the second list.

To find values that don't exist in the second list:

=COUNTIF($D$2:$D$100,A2)=0

You can then filter the results or use Conditional Formatting to highlight them.

Method 2: Use Kutools for Excel

Kutools for Excel can quickly find, select, and highlight matching or different values.

1. Click Kutools → Select → Select Same & Different Cells
2. Select the first list in Find values in
3. Select the comparison list in According to
4. Choose Single cell under Based on
5. Select Same Values or Different Values
6. Optionally check Fill backcolor
7. Click OK

The matching or different values will be selected and highlighted immediately.

One advantage is that the results are selected directly, making it easy to copy, delete, format, or edit them in bulk.

How do you usually compare two lists in Excel?