Need Formula or Data Table usage on Collecting Data

New Contributor



I'm working on a database to find patterns in a lottery, based on frequencies of combinations in the previous drawings.

My table has 3 columns: Ball 1, Ball 2, and Pairs. Ball 1 and Ball 2 column is numbered 1-35 with no duplicates (ex, 1 & 2, 1 & 3, 1 & 35, 2 & 3, 2 & 4, etc.). 34 & 35 is the final row.

My database has all the drawings and setup to give me the number of drawings with each pair of balls entered.
Inputs: B1 (Ball1) and C1 (Ball 2)

Output: G1 (Pairs)


G1 is a formula that counts how many drawings have the balls (inputs) in it. In the G column of the database, each drawing has a formula there that uses COUNTIF to determine how many balls in B1:F1 is in the drawing (row).


B1 = 1, C1 = 2, Then G1 = 4

This means that 4 drawings have both balls "1" and "2" in them.

If you make a change (C1 = 3), then G1 = 5; meaning 5 drawings have both balls "1" and "3".


I'm looking for a formula something like, "This cell is equal to "G1" if "B1" = X and "C1" = Y, where X & Y are the balls paired together. I can only get G1 when I manually enter B1 and C1.


With 595 rows to find the outputs of each combination, is there a formula I can use or if not, how can I use the Data Table function?

2 Replies
best response confirmed by TeeTom (New Contributor)


I have created a sample file. Is that what you meant?




A second Example with an alternative pair calculation.


Thanks for the example table, it was very similar to mines. I eventually figured it out and the solution bothers me as much as the problem.

The issue was the Data Table function was able to do what I needed to do, but only if the table was on the same sheet as the database with the drawings.

Thanks for the effort on it as well, one of us might hit a jackpot with these Excel skills.