Excel help

[Bobble]

I need some conditional formatting help. Say A1 through A30 are a list of words and B1 through B30 are another list of words (one word to a cell). If any of the words in A1:A30 are the same as any of the words in B1:B30, I want to format cell $C$1. If it helps, I can make it so that A1:A100 is all the words I'll ever use.

I was thinking some application of SUMPRODUCT but I can't get it. :banghead: Who wants to whip out their big excel smarts and wave it around the room?

[MIJB#19]

I think you need column C to count the occurances of the B's in the A column and then you can format $D$1 with a sum(C:C)<1 formula. I really think there's no way around making a formula for each of the B1:B30 cells to figure out whether there are occurances of A-words.

[Suburban Rhythm]

Hidden columns associated to column A

COUNTIF(A:A,B1)

You'll either get 1's (a match) or 0's (no match). And format based on the 1s.

[Bobble]

Thanks, Guys. You were all over it. What ended up working was putting this conditional formatting formula in C1:

=MAX(COUNTIF(A1:A30,B1:B30))

It seems to work like a column of Suburban Rhythm's COUNTIF functions but all in one formula and then just take the max (0 if no matches, 1 if any match).