Formula to get speed rating

[gfnut]

Hi I am new to excel and would to find a formula or function to find a speed rating. I would like to input distance + time and beaten lengths to get rating.

SpeedRatingGrid.xlsx
	A	B	C	D	E	F	G	H	I		K	L	M	N	O	P	Q	R	S	T
1	Rating	3F	4F	4.5F	5F	5.5F	6F	6.5F	7F		8F	8.3F	8.5F	9F	10F	12F		Lengths into Seconds Table
2	100	31.1	43.3	49.2	55.2	61.2	67.4	113.5	119.3		133.2	137.2	139.4	145.7	158.3	223.9		Nose	0
3	99.5					61.3		113.6			133.3	137.3	139.5	145.8	158.4	224		Head	0
4	99	31.2	43.4	49.3	55.3	61.4	67.5	113.7	119.4		133.4	137.4	139.6	145.9	158.5	224.2		Neck	0
5	98.5						67.6		119.5		133.5	137.5	139.7	146	158.6	224.3		½	0.1
6	98	31.3	43.5	49.4	55.4	61.5	67.7	113.8	119.6		133.6	137.6	139.8	146.1	158.7	224.4		¾	0.1
7	97.5				55.5	61.6		113.9	119.7		133.7	137.7	139.9	146.2	158.8	224.5		1	0.2
8	97	31.4	43.6	49.5	55.6	61.7	67.8	114	119.8		133.8	137.8	140	146.3	158.9	224.6		1 ¼	0.2
9	96.5						67.9	114.1	119.9		133.9	137.9	140.1	146.4	159	224.7		1 ½	0.3
10	96		43.7	49.6	55.7	61.8	68	114.2	120		134	138	140.2	146.5	159.1	224.8		1 ¾	0.3
11	95.5										134.1	138.1	140.3	146.6	159.2	224.9		2	0.4
12	95	31.5	43.8	49.7	55.8	61.9	68.1	114.3	120.1		134.2	138.2	140.4	146.7	159.3	225		2 ¼	0.4
13	94.5					62	68.2	114.4	120.2		134.3	138.3	140.5	146.8	159.4	225.1		2 ½	0.5
14	94	31.6	43.9	49.8	55.9	62.1	68.3	114.5	120.3		134.4	138.4	140.6	146.9	159.5	225.2		2 ¾	0.5
15	93.5				56			114.6	120.4		134.5	138.5	140.7	147	159.6	225.3		3	0.6
16	93	31.7	44	49.9	56.1	62.2	68.4	114.7	120.5		134.6	138.6	140.8	147.1	159.7	225.4		3 ¼	0.6
17	92.5						68.5		120.6		134.7	138.7	140.9	147.2	159.8	225.5		3 ½	0.7
18	92		44.1	50	56.2	62.3	68.6	114.8	120.7		134.8	138.8	141	147.3	159.9	225.6		3 ¾	0.7
19	91.5					62.4		114.9			134.9	138.9	141.1	147.4	200	225.7		4	0.8
20	91	31.8	44.2	50.1	56.3	62.5	68.7	115	120.8		135	139	141.2	147.5	200.1	225.8		4 ¼	0.8
21	90.5						68.8		120.9		135.1	139.1	141.3	147.6	200.2	225.9		4 ½	0.9
22	90	31.9	44.3	50.2	56.4	62.6	68.9	115.1	121		135.2	139.2	141.4	147.7	200.3	226		4 ¾	0.9
23	89.5				56.5	62.7		115.2	121.1		135.3	139.3	141.5	147.8	200.4	226.1		5	1
24	89	32	44.4	50.3	56.6	62.8	69	115.3	121.2		135.4	139.4	141.6	147.9	200.5	226.2		5 ½	1.1
25	88.5						69.1	115.4	121.3		135.5	139.5	141.7	148	200.6	226.3		6	1.2
26	88		44,5	50.4	56.7	62.9	69.2	115.5	121.4		135.6	139.6	141.8	148.1	200.7	226.4		6 ½	1.3
27	87.5										135.7	139.7	141.9	148.2	200.8	226.5		7	1.4
28	87	32.1	44.6	50.5	56.8	63	69.3	115.6	121.5		135.8	139.8	142	148.3	200.9	226.6		7 ½	1.5
29	86.5					63.1	69.4	115.7	121.6		135.9	140	142.1	148.4	201	226.7		8	1.6
30	86	32.2	44.7	50.6	56.9	63.2	69.5	115.8	121.7		136	140.1	142.2	148.5	201.1	226.8		8 ½	1.7
31	85.5				57			115.9	121.8		136.1	140.2	142.3	148.6	201.2	226.9		9	1.8
32	85	32.3	44.8	50.7	57.1	63.3	69.6	116	121.9		136.2	140.3	142.4	148.7	201.3	227		9 ½	1.9
33	84.5						69.7		122		136.3	140.4	142.5	148.8	201.4	227.1		10	2
34	84		44.9	50.8	57.2	63.4	69.8	116.1	122.1		136.4	140.5	142.6	148.9	201.5	227.2		11	2.2
35	83.5					63.5		116.2			136.5	140.6	142.7	149	201.6	227.3		12	2.4
36	83	32.4	45	50.9	57.3	63.6	69.9	116.3	122.2		136.6	140.7	142.8	149.1	201.7	227.4		13	2.6
37	82.5						70		122.3		136.7	140.8	142.9	149.2	201.8	227.5		14	2.8
38	82	32.5	45.1	51	57.4	63.7	70.1	116.4	122.4		136.8	140.9	143	149.3	201.9	227.6		15	3
39	81.5				57.5	63.8		116.5	122.5		136.9	141	143.1	149.4	202	227.7		16	3.2
40	81	32.6	45.2	51.1	57.6	63.9	70.2	116.6	122.6		137	141.1	143.2	149.5	202.1	227.8		17	3.4
41	80.5						70.3	116.7	122.7		137.1	141.2	143.3	149.6	202.2	227.9		18	3.6
42	80		45.3	51.2	57.7	64	70.4	116.8	122.8		137.2	141.3	143.4	149.7	202.3	228		19	3.8
43	79.5										137.3	141.4	143.5	149.8	202.4	228.1		20	4
44	79	32.7	45.4	51.3	57.8	64.1	70.5	116.9	122.9		137.4	141.5	143.6	149.9	202.5	228.2		25	5
45	78.5					64.2	70.6	117	123		137.5	141.6	143.7	150	202.6	228.3		30	6
Sheet1

 

[awoohaw]

Are Distance and Time the row and column headers?

 

[gfnut]

I think what you are asking is yes

 

[gfnut]

The A colum is the answer that I am trying to get to

 

[awoohaw]

Try This. I named the table and columns.
=INDEX(SpeedRatingTable[Rating],XMATCH(V3,
INDEX(SpeedRatingTable,SEQUENCE(ROWS(SpeedRatingTable),1,1,1),
XMATCH(V2,SpeedRatingTable[#Headers])),-1))

 

[awoohaw]

Here is it is from xl2bb, The inputs are in the two cells above:

=INDEX($A$1:$A$45,XMATCH(V3,
INDEX($A$1:$P45,SEQUENCE(ROWS($A1:$A45),1,1,1),
XMATCH(V2,$A1:$P1)),-1))

Mr Excel Questions.xlsx
	U	V
1
2	Distance	3F
3	Time	31.49999
4
5		97
Sheet10

Cell Formulas
Range	Formula
V5	V5	=INDEX($A$1:$A$45,XMATCH(V3, INDEX($A$1:$P45,SEQUENCE(ROWS($A1:$A45),1,1,1), XMATCH(V2,$A1:$P1)),-1))

Named Ranges
Name	Refers To	Cells
Distance	=Sheet10!$B$1:$P$1	V5
Rating	=Sheet10!$A$1:$A$45	V5

Cells with Data Validation
Cell	Allow	Criteria
V2	List	=Distance

 

[awoohaw]

shoot you don't have xmatch.. i need to build it for 2013.. sorry

Does Excel 2013 have spilled arrays? And, this is going to be a little complicated.

 

[gfnut]

Sorry, I do have the older verison. I'm not sure about spilled arrays. Would there be away to add the beaten lengths from column R and S into the final time before getting the rating. Thank you for all the help.

 

[awoohaw]

This should work!

=INDEX($A$1:$A45,MATCH(V3,INDEX($A$1:$P$45,1,MATCH(V2,$A$1:$P$1,0)):
INDEX($A$1:$P45,ROWS($A$1:$A$45),MATCH(V2,$A$1:$P$1,0)),1))

Mr Excel Questions.xlsx
	U	V
1
2	Distance	3F
3	Time	31.49
4	Rating:	97
Sheet10

Cell Formulas
Range	Formula
V4	V4	=INDEX($A$1:$A45,MATCH(V3,INDEX($A$1:$P$45,1,MATCH(V2,$A$1:$P$1,0)): INDEX($A$1:$P45,ROWS($A$1:$A$45),MATCH(V2,$A$1:$P$1,0)),1))

Named Ranges
Name	Refers To	Cells
Distance	=Sheet10!$B$1:$P$1	V4
Rating	=Sheet10!$A$1:$A$45	V4

Cells with Data Validation
Cell	Allow	Criteria
V2:W2	List	=Distance

 

[awoohaw]

I enjoyed that exercise. I got to use the INDEX function for something that it is seldom is used for:
Building a range reference by putting ":" between to INDEX(Match()) functions. Its is a really cool aspect of the Index function that I have seldom used.

Now that I said that, I hope it works for you!