Conditional Formatting based on a comparison to other cells W7, XL2013

[Bowlerguy300]

I started a game at work where we are playing Bingo based on the numbers that are drawn for the Powerball. (No money, just something fun to do) or so it started that way.
Each associate has a list of 11 numbers that I have been manually highlighting based on whether or not their number was drawn during the drawings held on Wednesday and Saturday nights.

I have used VLookups in the past on other spreadsheets but as far as I have found, that only seemed to work for comparing 1 cell to a column of cells, and vice versa for the HLookup.
I don't think these will work for what I want though. I am not too skilled at formulas in Excel.

I am trying to figure out a way so that when I enter the numbers on the right side (2,16,43,45,51,35), it highlights the numbers on the left if they match. Keeping in mind that the PB # is only being compared to the 1 column of numbers.

I have given a portion of the cells that are in my spreadsheet but there are over 200 associates and it takes forever to "find" and then highlight every number drawn. If there is a way to automate the highlighting of the cells based on a cell in the range of (D2:M2) equaling a cell in the range of (P2:T2), that would greatly speed up my tedious task for this now stupid game I started.

[TABLE="width: 744"]
<tbody>[TR]
[TD="colspan: 5"]BINGO NUMBERS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PB[/TD]
[TD="colspan: 2"]DRAWING DATES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]19[/TD]
[TD]25[/TD]
[TD]28[/TD]
[TD]41[/TD]
[TD]42[/TD]
[TD]46[/TD]
[TD]13[/TD]
[TD]09/03[/TD]
[TD]2[/TD]
[TD]16[/TD]
[TD]43[/TD]
[TD]45[/TD]
[TD]51[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]11[/TD]
[TD]14[/TD]
[TD]20[/TD]
[TD]23[/TD]
[TD]31[/TD]
[TD]37[/TD]
[TD]3[/TD]
[TD]09/06[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]16[/TD]
[TD]22[/TD]
[TD]31[/TD]
[TD]44[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]7[/TD]
[TD]09/10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]15[/TD]
[TD]19[/TD]
[TD]22[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]58[/TD]
[TD]3[/TD]
[TD]09/13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]7[/TD]
[TD]13[/TD]
[TD]19[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]41[/TD]
[TD]47[/TD]
[TD]53[/TD]
[TD]59[/TD]
[TD]35[/TD]
[TD]09/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]11[/TD]
[TD]16[/TD]
[TD]21[/TD]
[TD]32[/TD]
[TD]39[/TD]
[TD]44[/TD]
[TD]47[/TD]
[TD]53[/TD]
[TD]9[/TD]
[TD]09/20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]15[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]32[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]32[/TD]
[TD]09/24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]17[/TD]
[TD]21[/TD]
[TD]24[/TD]
[TD]39[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]17[/TD]
[TD]09/27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD]23[/TD]
[TD]29[/TD]
[TD]31[/TD]
[TD]42[/TD]
[TD]45[/TD]
[TD]23[/TD]
[TD]10/01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]14[/TD]
[TD]24[/TD]
[TD]28[/TD]
[TD]37[/TD]
[TD]43[/TD]
[TD]50[/TD]
[TD]52[/TD]
[TD]16[/TD]
[TD]10/04[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]22[/TD]
[TD]26[/TD]
[TD]29[/TD]
[TD]36[/TD]
[TD]44[/TD]
[TD]47[/TD]
[TD]50[/TD]
[TD]23[/TD]
[TD]10/08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD]33[/TD]
[TD]39[/TD]
[TD]49[/TD]
[TD]55[/TD]
[TD]59[/TD]
[TD]26[/TD]
[TD]10/11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your time and efforts.

 

[bbott]

Assuming that your first value is in A2 and your winning numbers are in M2:R2, try this CF in A2:

=ISNUMBER(MATCH(A2,$M$2:$R$2,0))

Copy the format to the rest of your table.

 

[Bowlerguy300]

I think I replied incorrectly as it didn't show up on the thread.
I wanted to thank you for your assistance.

I am curious as to why it fails if I change the range from
=ISNUMBER(MATCH(D2,$P$2:$T$2,0))

to

=ISNUMBER(MATCH(D2,$P$2:$T$34,0))

I found a work around by creating multiple conditional formats down from row 2 to 34. Thanks for all your help!

 

[bbott]

You're welcome.

It fails because MATCH can only work with a single column or single row range. But glad you found a way to make it work!