I want to find the Row that the Value in the Table and Column correspond To

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
So I have on the X-axis Pitching Distances.

On the Y-axis Velocities.

And in the table is the corresponding reaction times.

If I have the reaction time and the distance, how do I create a formula to find the row. (I know how to use Index Match to find the intersection of the Pitching Distances and the Velocities, but not how to find the Row based off the value and Distance).

Reaction TimeDistance
404142434445464748495051525354555657585960
Recorded Velocity600.450.470.480.490.500.510.520.530.550.560.570.580.590.600.610.630.640.650.660.670.68
610.450.460.470.480.490.500.510.530.540.550.560.570.580.590.600.610.630.640.650.660.67
620.440.450.460.470.480.490.510.520.530.540.550.560.570.580.590.600.620.630.640.650.66
630.430.440.450.470.480.490.500.510.520.530.540.550.560.570.580.600.610.620.630.640.65
640.430.440.450.460.470.480.490.500.510.520.530.540.550.560.580.590.600.610.620.630.64
650.420.430.440.450.460.470.480.490.500.510.520.530.550.560.570.580.590.600.610.620.63
660.410.420.430.440.450.460.480.490.500.510.520.530.540.550.560.570.580.590.600.610.62
670.410.420.430.440.450.460.470.480.490.500.510.520.530.540.550.560.570.580.590.600.61
680.400.410.420.430.440.450.460.470.480.490.500.510.520.530.540.550.560.570.580.590.60
690.400.410.420.420.430.440.450.460.470.480.490.500.510.520.530.540.550.560.570.580.59
700.390.400.410.420.430.440.450.460.470.480.490.500.510.520.530.540.550.560.560.570.58
710.380.390.400.410.420.430.440.450.460.470.480.490.500.510.520.530.540.550.560.570.58
720.380.390.400.410.420.430.440.450.450.460.470.480.490.500.510.520.530.540.550.560.57
730.370.380.390.400.410.420.430.440.450.460.470.480.490.500.500.510.520.530.540.550.56
740.370.380.390.400.410.410.420.430.440.450.460.470.480.490.500.510.520.530.530.540.55
750.360.370.380.390.400.410.420.430.440.450.450.460.470.480.490.500.510.520.530.540.55
760.360.370.380.390.390.400.410.420.430.440.450.460.470.480.480.490.500.510.520.530.54
770.350.360.370.380.390.400.410.420.430.430.440.450.460.470.480.490.500.500.510.520.53
780.350.360.370.380.380.390.400.410.420.430.440.450.450.460.470.480.490.500.510.520.52
790.350.350.360.370.380.390.400.410.410.420.430.440.450.460.470.470.480.490.500.510.52
800.340.350.360.370.380.380.390.400.410.420.430.430.440.450.460.470.480.490.490.500.51
810.340.350.350.360.370.380.390.400.400.410.420.430.440.450.450.460.470.480.490.500.51
820.330.340.350.360.370.370.380.390.400.410.420.420.430.440.450.460.470.470.480.490.50
830.330.340.350.350.360.370.380.390.390.400.410.420.430.440.440.450.460.470.480.480.49
840.320.330.340.350.360.370.370.380.390.400.410.410.420.430.440.450.450.460.470.480.49
850.320.330.340.340.350.360.370.380.390.390.400.410.420.430.430.440.450.460.470.470.48
860.320.330.330.340.350.360.360.370.380.390.400.400.410.420.430.440.440.450.460.470.48
870.310.320.330.340.340.350.360.370.380.380.390.400.410.420.420.430.440.450.450.460.47
880.310.320.330.330.340.350.360.360.370.380.390.400.400.410.420.430.430.440.450.460.46
890.310.310.320.330.340.340.350.360.370.380.380.390.400.410.410.420.430.440.440.450.46
900.300.310.320.330.330.340.350.360.360.370.380.390.390.400.410.420.420.430.440.450.45
910.300.310.310.320.330.340.340.350.360.370.370.380.390.400.400.410.420.430.430.440.45
920.300.300.310.320.330.330.340.350.360.360.370.380.390.390.400.410.420.420.430.440.44
930.290.300.310.320.320.330.340.340.350.360.370.370.380.390.400.400.410.420.430.430.44
940.290.300.300.310.320.330.330.340.350.360.360.370.380.380.390.400.410.410.420.430.44
950.290.290.300.310.320.320.330.340.340.350.360.370.370.380.390.390.400.410.420.420.43
960.280.290.300.310.310.320.330.330.340.350.360.360.370.380.380.390.400.400.410.420.43
970.280.290.300.300.310.320.320.330.340.340.350.360.370.370.380.390.390.400.410.410.42
980.280.290.290.300.310.310.320.330.330.340.350.350.360.370.380.380.390.400.400.410.42
990.280.280.290.300.300.310.320.320.330.340.340.350.360.370.370.380.390.390.400.410.41
1000.270.280.290.290.300.310.310.320.330.330.340.350.350.360.370.380.380.390.400.400.41

<colgroup><col><col><col span="21"></colgroup><tbody>
</tbody>

Thank you for whoever can help.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
The basic formula would be:
Code:
=MATCH(0.45,OFFSET(C5:C44,0,MATCH(48,$D$4:$S$4,0)),-1)
That finds the row for the shortest reaction time.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
This formula is essentially the same as SpillerBD's, but I added the index to get the velocity from the row.

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWX
1Reaction TimeRecorded Velocity
2Distance -->404142434445464748495051525354555657585960
3600.450.470.480.490.50.510.520.530.550.560.570.580.590.60.610.630.640.650.660.670.68
4610.450.460.470.480.490.50.510.530.540.550.560.570.580.590.60.610.630.640.650.660.67
5Reaction Time0.45620.440.450.460.470.480.490.510.520.530.540.550.560.570.580.590.60.620.630.640.650.66
6Distance46630.430.440.450.470.480.490.50.510.520.530.540.550.560.570.580.60.610.620.630.640.65
7Velocity69640.430.440.450.460.470.480.490.50.510.520.530.540.550.560.580.590.60.610.620.630.64
8650.420.430.440.450.460.470.480.490.50.510.520.530.550.560.570.580.590.60.610.620.63
9660.410.420.430.440.450.460.480.490.50.510.520.530.540.550.560.570.580.590.60.610.62
10670.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.590.60.61
11680.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.590.6
12690.40.410.420.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.59
13700.390.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.560.570.58
14710.380.390.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.58
15720.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.530.540.550.560.57
16730.370.380.390.40.410.420.430.440.450.460.470.480.490.50.50.510.520.530.540.550.56
17740.370.380.390.40.410.410.420.430.440.450.460.470.480.490.50.510.520.530.530.540.55
18750.360.370.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.530.540.55
19760.360.370.380.390.390.40.410.420.430.440.450.460.470.480.480.490.50.510.520.530.54
20770.350.360.370.380.390.40.410.420.430.430.440.450.460.470.480.490.50.50.510.520.53
21780.350.360.370.380.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.52
22790.350.350.360.370.380.390.40.410.410.420.430.440.450.460.470.470.480.490.50.510.52
23800.340.350.360.370.380.380.390.40.410.420.430.430.440.450.460.470.480.490.490.50.51
24810.340.350.350.360.370.380.390.40.40.410.420.430.440.450.450.460.470.480.490.50.51
25820.330.340.350.360.370.370.380.390.40.410.420.420.430.440.450.460.470.470.480.490.5
26830.330.340.350.350.360.370.380.390.390.40.410.420.430.440.440.450.460.470.480.480.49
27840.320.330.340.350.360.370.370.380.390.40.410.410.420.430.440.450.450.460.470.480.49
28850.320.330.340.340.350.360.370.380.390.390.40.410.420.430.430.440.450.460.470.470.48
29860.320.330.330.340.350.360.360.370.380.390.40.40.410.420.430.440.440.450.460.470.48
30870.310.320.330.340.340.350.360.370.380.380.390.40.410.420.420.430.440.450.450.460.47
31880.310.320.330.330.340.350.360.360.370.380.390.40.40.410.420.430.430.440.450.460.46
32890.310.310.320.330.340.340.350.360.370.380.380.390.40.410.410.420.430.440.440.450.46
33900.30.310.320.330.330.340.350.360.360.370.380.390.390.40.410.420.420.430.440.450.45
34910.30.310.310.320.330.340.340.350.360.370.370.380.390.40.40.410.420.430.430.440.45
35920.30.30.310.320.330.330.340.350.360.360.370.380.390.390.40.410.420.420.430.440.44
36930.290.30.310.320.320.330.340.340.350.360.370.370.380.390.40.40.410.420.430.430.44
37940.290.30.30.310.320.330.330.340.350.360.360.370.380.380.390.40.410.410.420.430.44
38950.290.290.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.420.420.43
39960.280.290.30.310.310.320.330.330.340.350.360.360.370.380.380.390.40.40.410.420.43
40970.280.290.30.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.410.42
41980.280.290.290.30.310.310.320.330.330.340.350.350.360.370.380.380.390.40.40.410.42
42990.280.280.290.30.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.41
431000.270.280.290.290.30.310.310.320.330.330.340.350.350.360.370.380.380.390.40.40.41

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
B7=INDEX($A$1:$X$43,MATCH(B5,OFFSET($A$1:$A$43,0,MATCH(B6,$A$2:$X$2,0)-1),0),3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



Note that if there are multiple matches, it will take the first match.
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136

ADVERTISEMENT

This formula is essentially the same as SpillerBD's, but I added the index to get the velocity from the row.

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWX
1Reaction TimeRecorded Velocity
2Distance -->404142434445464748495051525354555657585960
3600.450.470.480.490.50.510.520.530.550.560.570.580.590.60.610.630.640.650.660.670.68
4610.450.460.470.480.490.50.510.530.540.550.560.570.580.590.60.610.630.640.650.660.67
5Reaction Time0.45620.440.450.460.470.480.490.510.520.530.540.550.560.570.580.590.60.620.630.640.650.66
6Distance46630.430.440.450.470.480.490.50.510.520.530.540.550.560.570.580.60.610.620.630.640.65
7Velocity69640.430.440.450.460.470.480.490.50.510.520.530.540.550.560.580.590.60.610.620.630.64
8650.420.430.440.450.460.470.480.490.50.510.520.530.550.560.570.580.590.60.610.620.63
9660.410.420.430.440.450.460.480.490.50.510.520.530.540.550.560.570.580.590.60.610.62
10670.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.590.60.61
11680.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.590.6
12690.40.410.420.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.580.59
13700.390.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.560.570.58
14710.380.390.40.410.420.430.440.450.460.470.480.490.50.510.520.530.540.550.560.570.58
15720.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.530.540.550.560.57
16730.370.380.390.40.410.420.430.440.450.460.470.480.490.50.50.510.520.530.540.550.56
17740.370.380.390.40.410.410.420.430.440.450.460.470.480.490.50.510.520.530.530.540.55
18750.360.370.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.530.540.55
19760.360.370.380.390.390.40.410.420.430.440.450.460.470.480.480.490.50.510.520.530.54
20770.350.360.370.380.390.40.410.420.430.430.440.450.460.470.480.490.50.50.510.520.53
21780.350.360.370.380.380.390.40.410.420.430.440.450.450.460.470.480.490.50.510.520.52
22790.350.350.360.370.380.390.40.410.410.420.430.440.450.460.470.470.480.490.50.510.52
23800.340.350.360.370.380.380.390.40.410.420.430.430.440.450.460.470.480.490.490.50.51
24810.340.350.350.360.370.380.390.40.40.410.420.430.440.450.450.460.470.480.490.50.51
25820.330.340.350.360.370.370.380.390.40.410.420.420.430.440.450.460.470.470.480.490.5
26830.330.340.350.350.360.370.380.390.390.40.410.420.430.440.440.450.460.470.480.480.49
27840.320.330.340.350.360.370.370.380.390.40.410.410.420.430.440.450.450.460.470.480.49
28850.320.330.340.340.350.360.370.380.390.390.40.410.420.430.430.440.450.460.470.470.48
29860.320.330.330.340.350.360.360.370.380.390.40.40.410.420.430.440.440.450.460.470.48
30870.310.320.330.340.340.350.360.370.380.380.390.40.410.420.420.430.440.450.450.460.47
31880.310.320.330.330.340.350.360.360.370.380.390.40.40.410.420.430.430.440.450.460.46
32890.310.310.320.330.340.340.350.360.370.380.380.390.40.410.410.420.430.440.440.450.46
33900.30.310.320.330.330.340.350.360.360.370.380.390.390.40.410.420.420.430.440.450.45
34910.30.310.310.320.330.340.340.350.360.370.370.380.390.40.40.410.420.430.430.440.45
35920.30.30.310.320.330.330.340.350.360.360.370.380.390.390.40.410.420.420.430.440.44
36930.290.30.310.320.320.330.340.340.350.360.370.370.380.390.40.40.410.420.430.430.44
37940.290.30.30.310.320.330.330.340.350.360.360.370.380.380.390.40.410.410.420.430.44
38950.290.290.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.420.420.43
39960.280.290.30.310.310.320.330.330.340.350.360.360.370.380.380.390.40.40.410.420.43
40970.280.290.30.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.410.42
41980.280.290.290.30.310.310.320.330.330.340.350.350.360.370.380.380.390.40.40.410.42
42990.280.280.290.30.30.310.320.320.330.340.340.350.360.370.370.380.390.390.40.410.41
431000.270.280.290.290.30.310.310.320.330.330.340.350.350.360.370.380.380.390.40.40.41

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
B7=INDEX($A$1:$X$43,MATCH(B5,OFFSET($A$1:$A$43,0,MATCH(B6,$A$2:$X$2,0)-1),0),3)

<tbody>
</tbody>

<tbody>
</tbody>



Note that if there are multiple matches, it will take the first match.

So if you look at my images in the prior message, I have inserted the following formula:

=INDEX('Reaction Time Chart'!B2:W43,MATCH('Velo-Time Conversion'!B6, OFFSET('Reaction Time Chart'!B2:B43, 0, MATCH('Velo-Time Conversion'!B7,'Reaction Time Chart'!B2:W2, 0)-1),-1),3)

Please let me know if you see anything particularly wrong with it as it does not seem to be working.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
It all comes down to relative references. I based everything on A1 to make it a little easier (guess I got that wrong!), and you are basing the equation on the actual range. Here's how to adjust it using the actual table range:

=INDEX('Reaction Time Chart'!$B$2:$W$43,MATCH('Velo-Time Conversion'!B6,OFFSET('Reaction Time Chart'!$B$2:$B$43,0,MATCH('Velo-Time Conversion'!B7,'Reaction Time Chart'!$B$2:$W$2,0)-COLUMN($B$2)+1),0)-ROW($B$1)+1,1)

I believe I have my test sheet matching yours now.

Let me know if that works any better.
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136

ADVERTISEMENT

It all comes down to relative references. I based everything on A1 to make it a little easier (guess I got that wrong!), and you are basing the equation on the actual range. Here's how to adjust it using the actual table range:

=INDEX('Reaction Time Chart'!$B$2:$W$43,MATCH('Velo-Time Conversion'!B6,OFFSET('Reaction Time Chart'!$B$2:$B$43,0,MATCH('Velo-Time Conversion'!B7,'Reaction Time Chart'!$B$2:$W$2,0)-COLUMN($B$2)+1),0)-ROW($B$1)+1,1)

I believe I have my test sheet matching yours now.

Let me know if that works any better.

Please check this: https://www.dropbox.com/s/6z265avan3u8oys/Velo-Distance Converter.xlsx?dl=0

I think there might be something getting lost in my copy/paste

Thank you for all your help, I really appreciate it! :)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Aha! I didn't realize that your conversion table only shows 2 decimal places of a larger number. The formula I gave you was looking for an exact match, which it never found. There's only 1 minor change you need to make:

Rich (BB code):
=INDEX('Reaction Time Chart'!$B$2:$W$43,MATCH('Velo-Time Conversion'!B6,OFFSET('Reaction Time Chart'!$B$2:$B$43,0,MATCH('Velo-Time Conversion'!B7,'Reaction Time Chart'!$B$2:$W$2,0)-COLUMN($B$2)+1),-1)-ROW($B$1)+1,1)
In the original version, the red -1 was a 0. Make that change and it will work.
 

Michael515

Board Regular
Joined
Jul 10, 2014
Messages
136
Aha! I didn't realize that your conversion table only shows 2 decimal places of a larger number. The formula I gave you was looking for an exact match, which it never found. There's only 1 minor change you need to make:

Rich (BB code):
=INDEX('Reaction Time Chart'!$B$2:$W$43,MATCH('Velo-Time Conversion'!B6,OFFSET('Reaction Time Chart'!$B$2:$B$43,0,MATCH('Velo-Time Conversion'!B7,'Reaction Time Chart'!$B$2:$W$2,0)-COLUMN($B$2)+1),-1)-ROW($B$1)+1,1)
In the original version, the red -1 was a 0. Make that change and it will work.

Sorry I did not make that known. I knew I had to put in a -1 somewhere to eliminate the exact match, but I must've been putting it in the wrong place. Thank you so much, it works now! I really appreciate the continuous help :D
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,947
Members
410,713
Latest member
TaremyLunsil
Top