Lexicographic Index Numbers / Combination Sequence Numbers (CSN's) for a Lotto.

[PAB]

Hi everyone,

Since there are several threads on this board showing an interest in Lexicographic Index Numbers, better known as Combination Sequence Numbers (CSN's), I thought I would put together something that would calculate the vast majority of C(n, k) Lexicographic Index Numbers, where n is the total numbers drawn from, and k is the total numbers drawn.
I have created SIX Excel formulas to do this for 2, 3, 4, 5, 6 & 7 total numbers drawn (k numbers) in such a way that the formulas do NOT need to be amended or adjusted for the individual Lotto. Depending on which Lotto you play you just use the relevant formula below and copy it down as far as it is needed.

Setup the criteria for the k and n numbers.

We will use cell K2 for the total numbers drawn (k).
We will use cell K3 for the total numbers drawn from (n).

Setup the draws and formulas.

We will use cells B7:H7 and continuing down for the ACTUAL numbers drawn for each draw, so if you have 4 numbers drawn in your Lotto then cells B7:E7 and continuing down will only have numbers in them. If you have 6 numbers drawn in your Lotto then cells B7:G7 and continuing down will only have numbers in them etc.

In cell K7, COPY & PASTE the formula below that applies to your particular Lotto and copy it down as far as it is needed.

TWO NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-C7>0,COMBIN(K$3-C7,K$2-1),0)

THREE NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-D7>0,COMBIN(K$3-D7,K$2-2),0)

FOUR NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-E7>0,COMBIN(K$3-E7,K$2-3),0)

FIVE NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-F7>0,COMBIN(K$3-F7,K$2-4),0)

SIX NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-G7>0,COMBIN(K$3-G7,K$2-5),0)

SEVEN NUMBER LOTTO:

=COMBIN(K$3,K$2)-IF(K$3-(K$2-1)-B7>0,COMBIN(K$3-B7,K$2-0),0)-IF(K$3-(K$2-2)-C7>0,COMBIN(K$3-C7,K$2-1),0)-IF(K$3-(K$2-3)-D7>0,COMBIN(K$3-D7,K$2-2),0)-IF(K$3-(K$2-4)-E7>0,COMBIN(K$3-E7,K$2-3),0)-IF(K$3-(K$2-5)-F7>0,COMBIN(K$3-F7,K$2-4),0)-IF(K$3-(K$2-6)-G7>0,COMBIN(K$3-G7,K$2-5),0)-IF(K$3-H7>0,COMBIN(K$3-H7,K$2-6),0)

So just to summarise:

In cells B7:H7 and continuing down are the ACTUAL lotto numbers drawn.
In cell K2 is the total numbers drawn for your particular Lotto (k).
In cell K3 is the total numbers drawn from in your particular Lotto (n).
In cell K7 is the formula pertaining to your particular Lotto. Copy this formula down as far as it is needed.

You can format the cells with the Lexicographic Index Numbers to account for the thousands separator to make it easier to read.

That’s it!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB,

Nice work, congratulation.

I have a question, would be able to create the formula ( Not a macro ) to do the other way around, meaning if I put the Lexi number in cell G1, can you have the combination in cell A1,B1,C1,D1,E1 for example the game 5/39 ?

Lexi : 575757 would give combination : 35 36 37 38 39.

Thank you.
Serge.

 

[PAB]

Hi Serge,

I have a question, would be able to create the formula ( Not a macro ) to do the other way around, meaning if I put the Lexi number in cell G1, can you have the combination in cell A1,B1,C1,D1,E1 for example the game 5/39 ?

To my knowledge you would NOT be able to do this using an Excel formula.
In order to achieve this you would need a Worksheet Function using VBA which would go in the This Worksheet Module and then you could use an Excel Array formula in the Spreadsheet to obtain the data.
I hope this helps.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[Frank]

It can be done without using VBA but it needs a lot more than a formula. A table has first to be created for the specific lottery type which enables the Lex value to be factorised so that its components for each number can be evaluated. Then formulas can be applied to convert these factors back to numbers in the appropriate columns. Its a lot of hard work but has the advantage that the finished formulas can be copied down the sheet indefinately so that a column of numbers can be converted back to numbers.

 

[Frank]

I almost forgot, this has been discussed before here...http://www.lottoforums.com/lottery/showthread.php?t=12452. I gave a breakdown of the method and there is a link to a demo version, which may or may not work for you depending on whether you have a google docs account and whether you need to be authorised to input values. I haven't time to check it out now, I'll be away for a few days too..

 

[PAB]

Frank,

It can be done without using VBA but it needs a lot more than a formula. A table has first to be created for the specific lottery type which enables the Lex value to be factorised so that its components for each number can be evaluated. Then formulas can be applied to convert these factors back to numbers in the appropriate columns. Its a lot of hard work but has the advantage that the finished formulas can be copied down the sheet indefinately so that a column of numbers can be converted back to numbers.

Thanks for confirming the fact that this is NOT an easy request and cannot be achieved by just using a single Excel formula.

Serge,

I have put together the UDF (User Defined Function) below that will output the combination relevant to the Lexicographic Index Number that you enter in cell G1.
I don't know what version of Excel you are using or if you are familiar with using VBA or not so I will list step by step how to do this.

Instructions:

(1) Open your Excel Workbook.
(2) Make sure that you have Macro's enabled. This will be in Macro Security and Macro Settings. Make sure Enable all Macros is checked.
(3) Press Alt-F11 to OPEN the VBE editor.
(4) On the Menu bar at the top click Insert.
(5) On the drop down menu click Module.
(6) Copy and paste the UDF code below into the blank Module.
(7) Press Alt-Q to CLOSE the VBE editor.
(8) Select and highlight the cells A1,B1,C1,D1,E1 which is where you said that you wanted the combination output to.
(9) In the formula bar type in the formula:

=Lex_2_Num(G1)

Do NOT press ENTER. Instead press Ctrl-Shift-Enter. The reason for this is that the formula is an Array formula which I mentioned earlier. You have to confirm Array formulas by pressing Ctrl-Shift-Enter instead of just Enter.

UDF Code:

Function Lex_2_Num(LexVal As Long)
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim E As Long
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
If LexVal < 1 Or LexVal > 575757 Then Exit Function
For A = 1 To 35
For B = A + 1 To 36
For C = B + 1 To 37
For D = C + 1 To 38
For E = D + 1 To 39
n = n + 1
If n = LexVal Then
Lex_2_Num = Array(A, B, C, D, E)
Exit Function
End If
Next
Next
Next
Next
Next
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Function

Now when you change the Lexicographic Index Number in cell G1 it will produce the combination in cells A1,B1,C1,D1,E1 relevant to that Lexicographic Index Number.
I hope this does what you want.
Let me know how you get on.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Frank,

I just noticed that you posted a follow up to your previous post while I was compiling my post above.
I will have a look at the thread a little bit latter this evening.
Thanks in advance.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB,

Thank you for the hard work, but I already have it that's why I mention ( No macro ) in the post, but I sure that will help someone else.
I've been searching for this formula for a long time with no success so far, I used to have it but I lost it, I know it's not an easy one.

Thank you for all your time and efforts.

Regards,
Serge.

 

[PAB]

Hi Serge,

I have a question, would be able to create the formula ( Not a macro ) to do the other way around, meaning if I put the Lexi number in cell G1, can you have the combination in cell A1,B1,C1,D1,E1 for example the game 5/39 ?

Thank you for the hard work, but I already have it that's why I mention ( No macro ) in the post, but I sure that will help someone else.

I would be interested in, as I am sure others would be in seeing the VBA code that you have for converting a Lexicographic Index Number to the respective combination. If you could post your code it will be appreciated.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB,

Here it is :

Function LexToNums(LexVal As Long)
Dim a&, b&, c&, d&, e&, v&
If LexVal < 1 Or LexVal > 575757 Then Exit Function
For a = 1 To 35
For b = a + 1 To 36
For c = b + 1 To 37
For d = c + 1 To 38
For e = d + 1 To 39
v = v + 1
If v = LexVal Then
LexToNums = Array(a, b, c, d, e)
Exit Function
End If
Next
Next
Next
Next
Next
End Function

To me it's another language I don't understand, and that's why I'm still looking for the formula.

Regards,
Serge.

 

[PAB]

Hi Serge,

Thanks for posting the Function.

To me it's another language I don't understand, and that's why I'm still looking for the formula.

Do you happen to know who wrote that Function out of interest?

I use a Lex Function which is called from a Sub that converts the Combination to a Lexicographic Index Number.

Here is the Function that is called:

Function Lex() As Long
With Application.WorksheetFunction
Lex = .Combin(nMaxF, 6) - _
IIf(nMaxF - 5 - A > 0, .Combin(nMaxF - A, 6), 0) - _
IIf(nMaxF - 4 - B > 0, .Combin(nMaxF - B, 5), 0) - _
IIf(nMaxF - 3 - C > 0, .Combin(nMaxF - C, 4), 0) - _
IIf(nMaxF - 2 - D > 0, .Combin(nMaxF - D, 3), 0) - _
IIf(nMaxF - 1 - E > 0, .Combin(nMaxF - E, 2), 0) - _
IIf(nMaxF - F > 0, .Combin(nMaxF - F, 1), 0)
End With
End Function

I use a Sub to covert a Lexicographic Index Number to a Combination.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi Serge,

Don't worry, I found out who it was that wrote the Function.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB

I'm still looking for that formula, if one day you find it please post it on this tread.
Thank you very much.

Regards,
Serge.

 

[PAB]

Hi Serge,

After reading Frank's post in another thread and thinking about the logic behind this I think I will be able to build a formula from scratch to calculate the combination from a Lexicographic Index Number. I will try and build the formula over this weekend, time permitting of course.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Thanks, I really appreciate it.

Regards,
Serge.

 

[PAB]

Hi Serge,

This is as you requested if the Lexicographic Index Number is in cell G1.

Setup Instructions for C(n,k) and the Lookup Table.

(01) In cell I1 enter k.
(02) In cell J1 enter n.
(03) In cell K1 enter C(n,k).
(04) In cell I2 enter 5.
(05) In cell J2 enter 39.
(06) In cell K2 enter the formula:

=COMBIN($J$2,$I2)

(07) In cells R1:R39 enter the numbers 1:39.
(08) In cell M1 enter the formula:

=IF(R1<I$2,"",COMBIN($R1,$I$2))

(09) Copy this formula down to cell M39.
(10) In cell N1 enter the formula:

=IF(R1<I$2,"",COMBIN($R1,$I$2-1))

(11) Copy this formula down to cell N39.
(12) In cell O1 enter the formula:

=IF(R1<I$2,"",COMBIN($R1,$I$2-2))

(13) Copy this formula down to cell O39.
(14) In cell P1 enter the formula:

=IF(R1<I$2,"",COMBIN($R1,$I$2-3))

(15) Copy this formula down to cell P39.
(16) In cell Q1 enter the formula:

=IF(R1<I$2,"",COMBIN($R1,$I$2-4))

(17) Copy this formula down to cell Q39.

Setup Instructions for the Lexicographic Index Number to Combination Formulas.

(18) In cell A1 enter the formula:

=IF($G$1<1,"",$J$2-VLOOKUP($K$2-$G$1,$M$1:$R$39,6))

(19) In cell B1 enter the formula:

=IF($G$1<1,"",$J$2-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1),$N$1:$R$39,5))

(20) In cell C1 enter the formula:

=IF($G$1<1,"",$J$2-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1),$O$1:$R$39,4))

(21) In cell D1 enter the formula:

=IF($G$1<1,"",$J$2-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1),$O$1:$R$39,1),P$1:R$39,3))

(22) In cell E1 enter the formula:

=IF($G$1<1,"",$J$2-($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1),$O$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,$M$1:$R$39,1)-VLOOKUP($K$2-$G$1-VLOOKUP($K$2-$G$1,M$1:R$39,1),N$1:R$39,1),$O$1:$R$39,1),P$1:R$39,1)))

Notes:

If ANY of the cells in I2:K2 are empty or blank it will through out either a #N/A or #NUM! error or BOTH. I am sure that if this bothers you that you will be able to adjust the formulas accordingly.

There is a very small problem in the calculation of some of the higher Lexicographic Index Numbers which I will have a look at if I get time. I have a pretty good idea why.
Let me know how you get on!



Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB,

Congratulation.

I'm very impress by your work, you did a very good job and in a record time.

I set up the file exactly the way you explained it and everything work fine.
But like you mention it, the formula stop working when it get over 575124 then I get #N/A .

Thank you for all your hard work.

Regards,
Serge.

 

[PAB]

Hi Serge,

Thanks for your kind comments.
I started from scratch again and created NEW formulas. I have done some testing and have found that the new formulas appear to ONLY affect combinations that have the numbers 35,36,37,38,39 in them regardless of which position they fall into.
This is slightly different to my previous formulas which seem to ONLY affect combinations that have the numbers 35,36,37,38,39 in positions 1,2,3,4 and NOT in position 5.
My new formulas are more structured so I will start with trying to find a solution within them first.
I will hopefully have a solution and new formulas soon.
That will teach me to stay up to all hours.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[PAB]

Hi there,

I have done some more work on this and have come up with the following amended formulas. There does however, still appear to be a small problem.
If you are around Frank could you please cast your eyes over the formulas and see if you can see what might be causing this. I can't see the wood for the trees now, thanks.
While amending the formulas I set them up in such a way that you can copy them DOWN columns A:E if you want to test several or numerous Lexicographic Index Numbers. You CANNOT copy these formulas across the columns, they are not built in that way.

Anyway, here are JUST the amended formulas for cells A1:E1:

(18) In cell A1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1,$M$1:$R$60,6))

(19) In cell B1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),$N$1:$R$60,5))

(20) In cell C1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1),$O$1:$R$60,4))

(21) In cell D1 enter the formula:

=IF(G1=0,"",$J$2-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1),O$1:R$60,1),$P$1:$R$60,3))

(22) In cell E1 enter the formula:

=IF(G1=0,"",$J$2-($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1),O$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1)-VLOOKUP($K$2-G1-VLOOKUP($K$2-G1,M$1:R$60,1),N$1:R$60,1),O$1:R$60,1),P$1:R$60,1)))

I must admit that I rarely ever have the need to use Lookup tables, as you can probably guess, and the fact that they can become quite involved and unmanageable as you can see from above.
That said, it does keep the little grey cells ticking over.
In my personal opinion though, this is a classic example for using a custom VBA Function, it is small, accurate and simple to apply and use.

Have FUN!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.

 

[serge]

Hi PAB,

You seem to be a guy that doesn't let go until you find the solution, that show the character of a winner, I really appreciate the fact that you are persistent in helping me.

I know you still looking for the 2 last formulas to give you the right answer, but I have a question, and that's why I like formula.

If I want to change the formula to the Mega Million game ( 5/56 ) I would have to change :

1) All formulas the 39 by 56.

2) Extend M:R up to row 56.

3) change cell K2 from 575757 to 3819816.

And this should give me the combinations from the Lexi for the Mega Millions game ?

Unless there is some other change to do ?

Thank you in advance.

Regards,
Serge.