Linking numerical value occurrence to a date

[b_rianv]

I am trying to get some info for my lottery pick 6 to find out how long it has been since a lottery number hit with in a time frame of 6 months. This number can appear in any position with in a row BUT the Row that it is in corresponds to the date of that row with in a 6 month time period in my database.
Example, Lottery database
Cells A B C D E F G
1 7/27/2014 1 2 13 11 29 36
2 7/04/2014 7 15 21 32 33 53
3 6/25/2014 1 5 12 25 36 51
4 6/15/2014 4 10 17 21 22 49
4 5/30/2014 19 22 27 36 44 53
5 5/03/2014 1 2 3 14 25 46

Example, What I am trying to do for a reference Table
Last 6 months of Activity
Cells X Y Z
Number Last Date
9 1 7/27/2014
10 2 7/27/2014
11 3 5/03/2014
12 4 6/15/2014
13 5 6/25/2014

I am using xl 2010
Here is my code that I am working with [=IFERROR(LOOKUP(2,1/(L$2:N$31=$A2),$K$2:$K$31),"") ]. This code does not refer to the above examples it is from my workbook... I keep getting "circular reference" warning OR "refers to empty cells", what am I doing wrong

I would like for this to be able to go back for the last 6 months of drawings and also would like for the range of dates to be expandable so that when I update the drawings which I do on a weekly basis that I will not have to go in and adjust the formulas to accommodate the recently added cells. Any help that any one can or is willing to provide to me on this matter will be greatly appreciated and a thousand kudos in advance. Thank You,

[p45cal]

change the way you lay out your data so it's like:

date ball
03/05/2014 1
03/05/2014 2
03/05/2014 3
03/05/2014 14
03/05/2014 25
03/05/2014 46
30/05/2014 19
30/05/2014 22
30/05/2014 27
30/05/2014 36
30/05/2014 44
30/05/2014 53
15/06/2014 4
15/06/2014 10
15/06/2014 17
15/06/2014 21
15/06/2014 22
15/06/2014 49
25/06/2014 1
25/06/2014 5
25/06/2014 12
25/06/2014 25
25/06/2014 36
25/06/2014 51
04/07/2014 7
04/07/2014 15
04/07/2014 21
04/07/2014 32
04/07/2014 33
04/07/2014 53
27/07/2014 1
27/07/2014 2
27/07/2014 13
27/07/2014 11
27/07/2014 29
27/07/2014 36
29/07/2014 3
29/07/2014 6
29/07/2014 9
29/07/2014 12
29/07/2014 15
29/07/2014 18

That's two columns, a date and one ball number per row, so each draw has 6 rows.
Give the table some headers; say Date, Number
You can add a third column, column C containing a formula in row 2:
=TODAY()-A2
and copy down. Give this column the header such as Days ago.

Then make the table an Excel table and call it Table1 (say).
The start will look like this:

date ball days ago
03/05/2014 1 70
03/05/2014 2 70
03/05/2014 3 70
03/05/2014 14 70
03/05/2014 25 70
03/05/2014 46 70
30/05/2014 19 43
30/05/2014 22 43
30/05/2014 27 43
30/05/2014 36 43

[You've got some dates in the future (that's really good by the way! - if only…) which lead to negative numbers in the 3rd column, but it doesn't matter]

Then create a pivot table and make its source data Table1. With this you'll be able to get all sorts of statistics, including the last time a given ball(s) was/were picked in the last 6 months (183 days).
By creating a table, you'l be able to add data at the bottom and the formulae will automatically generate on the new rows and the table will expand, so the pivot table will also refer automatically to the new larger table.

In the attached there are two pivot tables, the left one shows "how long it has been since a lottery number hit with in a time frame of 6 months", the right one is filtered by the ball numbers 3,6,9,12,15,18, showing that you're going to win the jackpot on July 29th as it's showing those six numbers on that date. If you use the same numbers every draw, then it also shows you got two hits on June 25th, and two hits on May 3rd.

[b_rianv]

Thank you so much! I will give this a go.