Conditional Formatting If any cell In a column Matches any other Cell in another

0
votes

I am trying to highlight cells in a column in Excel that match a list of values in another column. Everything i have found so far like =NOT(ISERROR(MATCH(A1,$B$1:$B$1000,0))) only highlights the cell next to one that matches not the actual matching Values. Basically i am tryingto highlight every value in column B:B that matches any entry in A:A.

enter image description here

1
excel
Swap around A & B in your formula and see what happens. – Enigmativity
Excellent and so simple, thank you so much – GenericEric
Perhaps you could accept my answer? :-) – Enigmativity

1 Answers

0
votes

You just about had it right.

Try this instead:

=NOT(ISERROR(MATCH(B1,$A$1:$A$5,0)))

Or in R1C1 mode (which, IMHO, is much easier to work in as the formula doesn't change regardless which cell in column 2 you're on):

=NOT(ISERROR(MATCH(RC,R1C1:R5C1,0)))