Creating a Matrix with Multiple Columns of Data

Hi All,

I'm trying to create a matrix that references multiple tables, see example below. I have a list of values in column 1 that are randomly created and then have another column 2 that has another list of values that are random but are as a result of an action. It is a sporting analysis results and is reviewing data that follows multiple shots. (Golf!!)

Example:
2nd shot is from 155m resulting in an outcome of the ball finishing 4m from hole. This is then randomly generated for 18 holes and has varying 2nd shot distances and different finishing positions. In order to collate the results I am putting the data within brackets, i.e. 130m to 140m, all shots from within this range are added together, same with the next shot, 1m to 3m and so on.

So the data would look something like this,

Col 1 Col2
155 5
132 8
175 3
146 10
95 4

I want to be able to look up the Col1, find a number relevent to the matrix below, then look up Col2 and the result is then placed in the relevent column in the matrix.

I've tried VLookup and a few others but am really lost as to the best way of referencing the data.

I'm thinking the matrix would look like this, pardom the formatting...


< 1 1 - 3 3 - 5 5 - 8 8 - 12 + 12
< 60
60 - 70
70 - 80
80 - 90
90 - 100
100 - 110
110 - 120
120 - 130
130 - 140
140 - 150
150 - 160
160 - 170
170 - 180
180 - 200
+ 200


Cheers

Mark

Mark

What value is being placed in the intersection of the 2 variables given in columns 1 and 2?

How about you complete the matrix in an example file for the test data.

rylo

Book1.xlsx

Attachment 173030

Rylo,

see attached file. I hope you understand my request.

thanks for your help.

Hi Mike,

thank you for your help.

one minor typo on one of the rows but easily fixed.

thanks again.

Mark.