Conditional Formatting Issue in Excel

[s_j_sawyer]

Hi everyone.

I have never worked in-depth with Excel and I am trying to make my life a bit simpler doing some data cleaning using the 'conditional formatting' option. My scenario is the following:

I have two columns of data, column A and column B. I would like to take each entry (a string of text) in column A and see if it exists anywhere in column B. If it does, I would like to highlight it one color, and if not I would like to highlight it another color. Actually, just highlighting one of the two cases would work.

I have figured out how to do this with one entry. I type

=OR(EXACT($A$2,$B$2:$B$35))

into the formula box under conditional formatting. This checks to see if the string in A2 exists anywhere in column B (i.e. from B2 to B35). If it does it will highlight it. However I cannot seem to generalize this to apply to every value in the column of A.

Any advice?

 

[Future Bruno]

Thank you!</code>You can use a combination of the VLOOKUP function and conditional formatting to accomplish this. The VLOOKUP function will allow you to check if the value in column A exists in column B. If it does, it will return the value in column B, otherwise it will return a value of #N/A. You can then set up two conditional formatting rules, one for when the VLOOKUP returns a value, and another for when it returns #N/A. The first rule should be set up to highlight the value in column A (or both A and B if you want) when the VLOOKUP returns a value, and the second rule should be set up to highlight the value in column A (or both A and B if you want) when the VLOOKUP returns #N/A. The formula for the first rule should be:<code>=VLOOKUP($A2,$B$2:$B$35,1,FALSE)</code>The formula for the second rule should be:<code>=ISNA(VLOOKUP($A2,$B$2:$B$35,1,FALSE))</code>