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?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | List A | List B | ||
| 2 | Ana | Ana | In both | |
| 3 | Ben | Dev | ||
| 4 | Chloe | ben | ||
| 5 | Dev | Eve |
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
- COUNTIF counts how many times the name in column A appears anywhere in List B.
- A count above 0 means it is there. The order of the two lists does not matter.
- The $ signs keep List B in place while the formula is filled down.
Variations
A name that is missing from the other list
A B C D 1 List A List B 2 Ana Ana 3 Ben Dev 4 Chloe ben Only in A 5 Dev Eve In D4:
=IF(COUNTIF($B$2:$B$5, A4)>0, "In both", "Only in A")→ Only in AThe same formula on row 4: Chloe is not in List B.
Are the two cells on the same row equal?
A B C D 1 List A List B 2 Ana Ana TRUE 3 Ben Dev 4 Chloe ben 5 Dev Eve In D2:
=A2=B2→ TRUEThe simplest comparison: row by row. Upper and lower case are treated as the same.
A comparison that tells capitals apart
A B C D 1 List A List B 2 Ana Ana FALSE 3 Ben Dev 4 Chloe ben 5 Dev Eve In D2:
=EXACT(A3, B4)→ FALSEA3 holds "Ben" and B4 holds "ben". With = they would count as equal.
List everything that is missing from List B
A B C D 1 List A List B 2 Ana Ana Chloe 3 Ben Dev 4 Chloe ben 5 Dev Eve In D2:
=FILTER(A2:A5, COUNTIF(B2:B5, A2:A5)=0)→ ChloeExcel 2021 and Microsoft 365.
Watch out for
- Spaces at the end. "Ana" and "Ana " do not match. If values that look the same are reported as different, clean both lists with TRIM.
- Numbers and text. The number 1001 and the text "1001" do not match either. Both columns need to be the same kind.
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
- COUNTIF: Counts the cells that meet a condition.
- IF: Returns one value if a condition is TRUE and another if it is FALSE.
- EXACT: TRUE if two text values are exactly the same, including case.
- FILTER: Keeps only the rows that meet a condition. The result spills.
- MATCH: The position of a value in a row or column.