Find Date of Last Occurrence in a range based on a cell value

I am sorry if this is a Duplicate post, i searched and could not find anything that was what would work for me.

This is my problem. I have two sheets, one has data in a table with column headings and the other is formula or calculation sheet. On the calculation sheet I have six sets of three columns each. The first five sets are basically the same, Ball #1, Number of Times Drawn, Date Last Drew. The only column that will change the column heading is the Ball #, it will go from 1 to 2 to 3 to 4 to 5. The sixth column has the same two columns, Number of Times Drawn and Date Last Drew. The first column in the sixth column set is labeled Mega Ball. (See Pics Attached Below)

Yes I am doing a stats analyses of lottery numbers and there drawings for a project.

This is my problem, I can't get the Date Last Drew to work. I have tried the following formula:
=INDEX(Drawing_Date[Date],SUMPRODUCT(MAX(A2)*ROW(A2))-ROW(Drawing_Date[Date])+1)

The original formula was slightly different, in the MAX() function and the *ROW() function, I had "Item=A2" in the () but I received a #Name error. I figured out that Excel didn't know what "Item" was so I changed it to just the cell reference, A2. The data sheet has a table named Table1, side note Excel doesn't see Table 1 it only sees Table8 through 14(??), with the column headings Date, Ball #1, Ball #2, Ball #3, Ball #4, Ball #5, Mega Ball, and Multiplier. So when the formula is referencing the range on the data sheet I am using "Drawing_Date[Date]" to specify the range of draw dates.

When I run the formula I get a date, that seems random and is clearly wrong, and I can not figure out where it is pulling it from. I have pasted in a couple of snap shots of the two sheets for you to see. I am not even sure if I am on the right track any more. I have read so much trying to figure this out that my brain seems to be turning to mush!!

Please note that I really want a VBA solution as I am in no way shape or form good with VBA. If I can figure out the formula/function for the first one I think I can adjust it for the rest. (I hope )

Thanks for all the help!!
Mike C



Calc Sheet Snap Shot
Calc_Sheet.JPG

Data Sheet Snap Shot
Data_Sheet.jpg