Comparing a row to multiple columns

[jdanehl]

Hello,

I'm an Excel novice trying to create another lotto checker type Excel sheet. I've looked quite a bit through the forum and have not really found an answer to what I need. I need to compare a row (represents the weekly 5 winning numbers) to the players static numbers. I then want to highlight matches.
I have been trying to use conditional formatting based on the result of the COUNTIF formula comparing the winning numbers to the players numbers. I have not been successful. Any suggestions??

Regards.

[alansidman]

Welcome to the forum. Suggest you post a sample worksheet so that we can see exactly how this is arranged and provide a suitable response. Click on the Go Advanced button and follow the wizard.

[GeneralDisarray]

See attached example. You would need to adjust the formula/formatting to your own situation (upload an example if you need further help).

This is the formula used for the conditional formatting:
=(SUMPRODUCT(1*(C2=$C$4:$G$4))>0)

[jdanehl]

Thank you for your replies. I've attached the workbook to this reply. Here is what I am trying to achieve:
For example... week of 1/16/2013, winning numbers are contained in C3:G3. I want to interrogate L3:P22 and highlight any matches from C3:G3. The following week would just mean changing the range of C3:G3 to C4:G4.
Additionally I want to compare (again for the week of 1/16/2013) H3 to Q3:Q22 and highlight any matches.

[jdanehl]

Perhaps a more specific way to ask my question:
Is it possible to compare the value in each cell of a single row of cells (A2:E2) to the individual value of each cell in a range of cells (L2:P23)?

So for example, if the number 2 is present in A2:E2 I want all cells with the number 2 found within L2:P23 to highlight. On the other hand, what I do not want is to highlight duplicates just within the L2:P23 range. I hope I haven't made this more confusing.

Any direction will be immensely appreciated.

Thanks!

[Tony Valko]

Try this...

Select the *entire* range L3:P22 starting from cell L3.
Cell L3 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=MATCH(L3,OFFSET($C$3:$G$3,MATCH(100,$G$3:$G$500)-1,,1,5),0)

Click the Format button
Select the desired style(s)
OK out

Now, do the same thing for the Powerball...

Select the *entire* range Q3:Q22 starting from cell Q3.
Cell Q3 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>
Manage rules>New rule>Use a formula to determine
which cells to format

Enter this formula in the box below:

=Q3=LOOKUP(100,H:H)

Click the Format button
Select the desired style(s)
OK out

[jdanehl]

Tony,

I tried what you suggested. I've attached the workbook to this reply with the conditional format applied for the weekly winning numbers. It looks to me like what is happening is the highlighted cells are only duplicates within the range of all the past weeks winning numbers. What I am trying to achieve is comparing only a one week of winning numbers to the players numbers at a time.

Did I not implement your suggestion properly??

Thanks!!

[Tony Valko]

The conditional formatting is highlighting the numbers in L3:P22 that match the numbers from the bottom-most row in columns C:G, currently C16:G16 (and the powerball is a separate match).

Isn't that what you wanted?