Opps only works with 2 + bonus and 5 + bonus

[adulted]

Hi Again

I moked up some numbers and only those with 2 + bonus and 5 + bonus show the returned amounts. dosen't return amounts for 3, 4, 5, or 6.

Thankss again,
Paul

Lotto649 Super7 mod.xls
	A	B	C	D	E	F	G	H	I	J	K
1										wednov9
2										Tickets	Amounts
3										2 + bonus	5
4										3	10
5	DrawDate	one	two	three	four	five	six	bonus		4	74.7
6	Wed Nov 30, 2005	5	18	22	28	29	32	17		5	2397.1
7										5 + bonus	255354.9
8										6	16840173
9
10	DrawDate	one	two	three	four	five	six	Numbers Drawn	Winnings
11	Wed Nov 30, 2005	5	18	22	28	29	1	5	0
12	Wed Nov 30, 2005	17	29	18	32	5	28	5 + bonus	255354.9
13	Wed Nov 30, 2005	32	29	22	41	28	49	4	0
14	Wed Nov 30, 2005	8	5	32	27	28	44	3	0
15	Wed Nov 30, 2005	17	18	22	4	3	1	2 + bonus	5
Sheet1

 

[Drew]

You should stick with your original thread. I believe Fairwinds answered your question...

I did take a stab at this as well... (change ranges to suit)
=SUMPRODUCT(COUNTIF($B$4:$G$4,B9:G9))&CHOOSE(COUNTIF(B9:G9,$H$4)+1,""," + bonus")
=IF(ISNA(VLOOKUP(H9,$J$1:$K$6,2,0)),0,VLOOKUP(H9,$J$1:$K$6,2,0))

And as Fairwinds said, your lookup table in column J must be text.
You probably formatted as text but I think you need to change the data type: highlight J3:J8 then go to data>text to columns>fixed>next>next then select text>finish

 

[adulted]

Thanks

Yes, it was answered, must have been too much cafine this morning. I'll be more observant when posting/replying
Thanks to you all.
Paul

 

[fairwinds]

I did take a stab at this as well... (change ranges to suit)
=SUMPRODUCT(COUNTIF($B$4:$G$4,B9:G9))&CHOOSE(COUNTIF(B9:G9,$H$4)+1,""," + bonus")

This formula could give a return like 3 + bonus wich would make the VLOOKUP fail.

Thus my lengthier formula suggestion.