Opps only works with 2 + bonus and 5 + bonus

adulted

Active Member
Joined
Jan 22, 2004
Messages
339
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top