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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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! :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top