# Opps only works with 2 + bonus and 5 + bonus

##### Active Member
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
ABCDEFGHIJK
1wednov9
2TicketsAmounts
32 + bonus5
4310
5DrawDateonetwothreefourfivesixbonus474.7
6Wed Nov 30, 2005518222829321752397.1
75 + bonus255354.9
8616840173
9
10DrawDateonetwothreefourfivesixNumbers DrawnWinnings
11Wed Nov 30, 2005518222829150
12Wed Nov 30, 2005172918325285 + bonus255354.9
13Wed Nov 30, 200532292241284940
14Wed Nov 30, 2005853227284430
15Wed Nov 30, 20051718224312 + bonus5
Sheet1

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Drew

##### Board Regular

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

##### Active Member
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

##### MrExcel MVP
Drew said:
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.

Replies
1
Views
204
Replies
3
Views
219
Replies
10
Views
210
Replies
0
Views
154
Replies
10
Views
146

1,196,008
Messages
6,012,836
Members
441,733
Latest member
MartijnB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back