You can use the following basic formula to compare two lists in Excel using the VLOOKUP function:
=ISNA(VLOOKUP(A2,$C$2:$C$9,1,False))
Using the Conditional Formatting tool in Excel, we can use this formula to highlight every value in column A that does not belong to a range in column C.
The following example shows how to use this formula in practice.
Example: Compare Two Lists Using VLOOKUP
Suppose we have the following two datasets in Excel:
Suppose we’d like to identify the teams in Dataset 1 that are not in Dataset 2.
To do so, we can highlight every value in column A and then click the Conditional Formatting button on the Home tab along the top ribbon.
We can then click New Rule…
In the new window that appears, select the option that says Use a formula to determine which cells to format then type in the following formula:
=ISNA(VLOOKUP(A2,$C$2:$C$9,1,False))
Then click the Format button and choose a color to fill in values:
Once you click OK, every value in column A that does not appear in column C will be highlighted:
We can manually verify that a few of the values are highlighted correctly:
- Hawks appear in both Dataset 1 and Dataset 2, so it is not highlighted.
- Mavericks appear in both Dataset 1 and Dataset 2, so it is not highlighted.
- Lakers appear in Dataset 1 but not Dataset 2, so it is highlighted.
And so on.
Note that we chose to highlight values that did not belong to both datasets, but we could also apply a different styling such as bolded text, increased font size, a border around cells, etc.
Additional Resources
How to Compare Two Excel Sheets for Differences
How to Calculate the Difference Between Two Dates in Excel
How would you have this do the same to a list that has repeating values?
Great question! When comparing two lists in Excel using `VLOOKUP`, repeating values can introduce complications — for example, `VLOOKUP` will always return the **first** match it finds, so if you have duplicates, it won’t match all instances.
Let me break this down with a practical solution for **handling repeating values**:
—
### **Scenario**:
– **List A**: contains values (possibly with duplicates)
– **List B**: another list you want to compare against List A (also possibly with duplicates)
You want to **find which items in List A exist in List B**, accounting for duplicates properly.
—
### ✅ Option 1: Use **Helper Columns** with COUNTIFS (Best for repeated values)
#### Example:
Assume:
– List A is in `A2:A10`
– List B is in `B2:B10`
Step-by-step:
1. In column C (next to List A), write this formula:
“`excel
=IF(COUNTIF(B$2:B$10, A2) >= COUNTIF(A$2:A2, A2), “Match”, “No Match”)
“`
2. **What it does**:
– `COUNTIF(B$2:B$10, A2)`: counts how many times the value in A2 appears in List B
– `COUNTIF(A$2:A2, A2)`: counts how many times the value in A2 has occurred **so far** in List A
– If the value exists **at least that many times** in List B, it’s a “Match”
– This way, duplicates are tracked accurately
—
### ✅ Option 2: Use Power Query (for advanced users)
You can use Power Query to load both lists and perform an **inner join**, which will return only the matching rows (including duplicates properly). This is ideal if you’re doing this comparison repeatedly.
—
### ❌ Why `VLOOKUP` alone doesn’t work well with duplicates:
`=IF(ISNA(VLOOKUP(A2, B:B, 1, FALSE)), “No Match”, “Match”)`
– Works fine for **existence check**
– **Fails** if you want to compare quantities or handle repeated values **accurately**
—