Excel compare two columns and highlight duplicates

Question

I have an excel file with 10,000 rows in column A some values are the same.

Example:

A1 - P7767  A2 - P3443  A3 - P7767  A4 - P8746  A5 - P9435 

etc...

I then have another column with 100 rows which have some of the values found in column A,

B1 - P7767  B2 - P8746 

etc...

I need to highlight all cells in column A where the value is found in any of the values in column B

So basically column B checks to see if it can find the same value anywhere in column A, if true highlight the cell leaving any cells white when the value is not found in column B

I hope I have explained this well, I have done some research and I believe I need to use conditional formatting to get this result but I am really stuck on the formula to use and cannot seem to find an example online (Maybe I am not searching the correct term as I'm not sure on what this is exactly called)

Answer 1

There may be a simpler option, but you can use VLOOKUP to check if a value appears in a list (and VLOOKUP is a powerful formula to get to grips with anyway).

So for A1, you can set a conditional format using the following formula:

=NOT(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))) 

Copy and Paste Special > Formats to copy that conditional format to the other cells in column A.

What the above formula is doing:

• VLOOKUP is looking up the value of Cell A1 (first parameter) against the whole of column B ($B:$B), in the first column (that's the 3rd parameter, redundant here, but typically VLOOKUP looks up a table rather than a column). The last parameter, FALSE, specifies that the match must be exact rather than just the closest match.
• VLOOKUP will return #ISNA if no match is found, so the NOT(ISNA(...)) returns true for all cells which have a match in column B.