How to / Lookups

How to compare two columns in Excel

To see which values of one list are also in the other, use =IF(COUNTIF($B$2:$B$5, A2)>0, "In both", "Only in A") and fill it down.

Is this name in the other list?

ABCD
1List AList B
2AnaAnaIn both
3BenDev
4Chloeben
5DevEve

In D2: =IF(COUNTIF($B$2:$B$5, A2)>0, "In both", "Only in A") → In both

Ana is in List B, although not on the same row in every case: Dev is in both lists too, on different rows.

How it works

  1. COUNTIF counts how many times the name in column A appears anywhere in List B.
  2. A count above 0 means it is there. The order of the two lists does not matter.
  3. The $ signs keep List B in place while the formula is filled down.

Variations

  1. A name that is missing from the other list

    ABCD
    1List AList B
    2AnaAna
    3BenDev
    4ChloebenOnly in A
    5DevEve

    In D4: =IF(COUNTIF($B$2:$B$5, A4)>0, "In both", "Only in A") → Only in A

    The same formula on row 4: Chloe is not in List B.

  2. Are the two cells on the same row equal?

    ABCD
    1List AList B
    2AnaAnaTRUE
    3BenDev
    4Chloeben
    5DevEve

    In D2: =A2=B2 → TRUE

    The simplest comparison: row by row. Upper and lower case are treated as the same.

  3. A comparison that tells capitals apart

    ABCD
    1List AList B
    2AnaAnaFALSE
    3BenDev
    4Chloeben
    5DevEve

    In D2: =EXACT(A3, B4) → FALSE

    A3 holds "Ben" and B4 holds "ben". With = they would count as equal.

  4. List everything that is missing from List B

    ABCD
    1List AList B
    2AnaAnaChloe
    3BenDev
    4Chloeben
    5DevEve

    In D2: =FILTER(A2:A5, COUNTIF(B2:B5, A2:A5)=0) → Chloe

    Excel 2021 and Microsoft 365.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related