How to highlight a matching cell from a set of results for a lottery spreadsheet

[johno17]

Hi, I am trying to set up a lottery spreadsheet, where I enter the results and matching numbers would be highlighted
Any help would be appreciated

Thanks in advanced

 

[stanleydgromjr]

johno17,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/index.php?f=1

Instructions for using "Excel Jeanie HTML 4":
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[johno17]

Excel Workbook
	I
24	41
Sheet1

 

[mole999]

for those not in the UK, its a minimum of 7 numbers (for Lotto) range 1 to 49, and normally no duplicates

 

[johno17]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1
2			Results	7	8	9	25	41	49
3
4
5					Lottery Numbers
6
7				1	5	9	49	31	15
8				24	23	25	48	49	3
9				13	14	2	9	41	15
10				14	1	5	7	8	17
11				6	16	26	36	46	47
12				2	4	6	21	25	30
13				7	11	9	14	30	8
14				3	6	9	12	13	16
15				45	46	48	12	3	4
16				25	16	19	21	8	43
17				42	13	1	8	45	47
18				4	8	18	26	30	36
19				24	25	27	29	31	42
20				5	10	15	20	25	30
21				2	7	8	11	16	18
22				3	4	25	27	30	31
23				11	16	40	43	21	15
24				22	23	29	36	40	41
25
Sheet1

 

[gardnertoo]

To highlight, use conditional format. The condition for cell D7 is set up for Cell value, is equal to, =D$2. Copy formats over and down. You can use an array formula to count how many numbers match on each line, see below. The formula is entered without the { } curly brackets, then confirmed by hitting Control + Shift + Enter instead of just Enter.

Book1
	B	C	D	E	F	G	H	I	J
1
2		Results	7	8	9	25	41	49
3
4
5				Lottery Numbers
6		Matches
7		1	1	5	9	49	31	15
8		0	24	23	25	48	49	3
9		1	13	14	2	9	41	15
10		0	14	1	5	7	8	17
11		0	6	16	26	36	46	47
12		0	2	4	6	21	25	30
13		2	7	11	9	14	30	8
14		1	3	6	9	12	13	16
15		0	45	46	48	12	3	4
Sheet1

 

[barry houdini]

You can do that like this

Select D7:I24

Use Format > Conditional formatting and use "formula is" from the dropdown and enter this formula

=MATCH(D7,$D$2:$I$2,0)

Select required formatting > OK

 

[mole999]

one thing I didn't see from the samples is highlighting cells that are not in the same column but are equal to one of the 7 header numbers

 

[gardnertoo]

Barry's formula gives you credit for any of your numbers matching any of the results numbers, regardless of order; my formula only gives you credit if your nth number matches the nth result number. How is your Lotto set up?

 

[mole999]

values in G9, G10, H10 and I13 all need to be captured. mimimum is 2 exact matches to a full line, and pays on those computations