Collating data from separate columns

[Milano1000]

Hello all, I am confused how to collate or tabulate the results in this data set. Any help would be appreciated. I think this can be accomplished by using a formula but every effort has failed. I have given up and am asking for help.

This is it is just a set of math mathematical formulas that represent data points for a roll playing game. There 3 columns, Range Band (column B), Damage (column C), and Penetration (column D). The columns C and D are two separate formulas drawn from mathematical formulas. What I am trying to accomplish is take the data set of B:3 to D:31 and have Excel automatically Create the table in cells F:20 to K:21. Could I get help doing this from you all?

To clarify or reiterate, Damage gets smaller with Range, as does the Penetration. I am looking for a way to tabulate the data in table B:3 to D:31. Then reproduce it in F:20 to K:21. I highlighted the applicable data in red (column B) and Orange (column C and D). For example, (in row 7) at 145m the data should be eventually read as "4 / 2". As in a reference to G:20 and G:21. At 900m (row 27) should eventually read "1 / nil" as in K:20 and K:21.

I hope what I am asking makes sense. If not please do not hesitate to ask me and I will try to reiterate what I am trying to do.

Formula results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Band	Damage	Penetration
3		0
4		0	5	2
5		5	4	2
6		10	4	2
7		145	4	2
8		150	4	2
9		155	4	2
10		160	4	2
11		165	4	2
12		170	3	2
13		175	3	2
14		225	3	2
15		250	3	2
16		275	3	2
17		300	3	3
18		325	3	3
19		350	3	3
20		375	2	3		0 - 5	5 - 165	165 - 275	275 - 350	350 - 575	575 - 950
21		400	2	3		5 / 2	4 / 2	3 / 2	3 / nil	2 / nil	1 / nil
22		525	2	4
23		550	2	4
24		575	2	4
25		600	1	5
26		625	1	5
27		900	1	12
28		925	1	13
29		950	0	15
30		975	0	16
31		1000	0	18
32
Sheet1

 

[Milano1000]

Hey thank you all! Peter_SSs I worked off your suggestion and it came out perfectly. My actual file is 11 tabs long. Mostly for organization because of the amount of data and cross referencing, so it took me these last few days to translate your solution into the actual data.

To Doss, I appreciate the help as well. It could be an easier or better solution but given that this is just a hobby and something I do in free time I didn't really want to learn VBA.

To RandomHelper, I didn't see your posting until after I was engaged with the idea from Peter. It could be that this is easier also.

Thank you again guys.

 

[Peter_SSs]

You're welcome. Glad we were able to help. Thanks for the follow-up.