Introduction
Using VLOOKUP, NOT & ISNA in a Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.
To highlight Sheet1 Column A cells that appear in Sheet2 Column A, select Sheet1 Column A and create a new Conditional Formatting rule using ‘Use a formula to determine which cells to format‘ and use formula:
=NOT(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)))
Sheet1
A | B | |
---|---|---|
1 | Ford | |
2 | Lotus | |
3 | BMW | |
4 | Audi | |
5 | Mini | |
6 | Ford |
Sheet2
A | B | C | D | |
---|---|---|---|---|
1 | Ford | US | Escort | Red |
2 | Lotus | UK | Elise | Silver |
3 | BMW | Germany | 330 | Blue |
4 | VW | Germany | Golf | Black |
5 | Ford | US | Mondeo | Silver |
6 | Mini | Cooper |
To apply format rule to show non-matches use
=ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))
A | B | |
---|---|---|
1 | Ford | |
2 | Lotus | |
3 | BMW | |
4 | Audi | |
5 | Mini | |
6 | Ford |
Also see
- Excel Tips…Using VLOOKUP
- Excel Tips…Using VLOOKUP to check if cell value found in second data set
- Excel Tips…Using VLOOKUP to return custom text for match/no match found
- Excel Tips…Use ‘&’ or CONCATENATE to combine cells
- Excel Tips…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found