Using Vlookup In a Range

[jake424]

I am trying to use a vlookup for two cells whose data are in a range of cells. The grid is in a range for weight and miles.

	Weight	1000	1100	1200	1300	1400	1500	1600	1700
Miles		1099	1199	1299	1399	1499	1599	1699	1799
1	50	757	810	862	915	967	1019	1071	1124
51	100	786	841	896	951	1006	1061	1116	1171
151	200	834	893	952	1012	1071	1131	1190	1249
201	250	845	905	965	1025	1086	1146	1206	1267
251	300	856	918	979	1041	1102	1164	1225	1286
Weight	Miles	Output
227	1388	???????

<tbody>
</tbody>

For the example above, I have miles ranges in columns and weight ranges in rows. My output for the example should be 1025 as it falls in the 201-250 Mile and 1300-1399 Range. Is there a vlookup or a match/index that could help me find that output without looking through the grid?


Thanks,
Jake

 

[Weazel]

something like...


=INDEX(C3:J7,MATCH(A10,A3:A7),MATCH(B10,C1:J1))

	A	B	C	D	E	F	G	H	I	J
1		Weight	1000	1100	1200	1300	1400	1500	1600	1700
2	Miles		1099	1199	1299	1399	1499	1599	1699	1799
3	1	50	757	810	862	915	967	1019	1071	1124
4	51	100	786	841	896	951	1006	1061	1116	1171
5	151	200	834	893	952	1012	1071	1131	1190	1249
6	201	250	845	905	965	1025	1086	1146	1206	1267
7	251	300	856	918	979	1041	1102	1164	1225	1286
8
9	Weight	Miles	Output
10	227	1388	1025
11

<TBODY>
</TBODY>

 

[jake424]

something like...


=INDEX(C3:J7,MATCH(A10,A3:A7),MATCH(B10,C1:J1))

	A	B	C	D	E	F	G	H	I	J
1		Weight	1000	1100	1200	1300	1400	1500	1600	1700
2	Miles		1099	1199	1299	1399	1499	1599	1699	1799
3	1	50	757	810	862	915	967	1019	1071	1124
4	51	100	786	841	896	951	1006	1061	1116	1171
5	151	200	834	893	952	1012	1071	1131	1190	1249
6	201	250	845	905	965	1025	1086	1146	1206	1267
7	251	300	856	918	979	1041	1102	1164	1225	1286
8
9	Weight	Miles	Output
10	227	1388	1025
11

<tbody>
</tbody>

Hello Weazel, thanks for the response! Would this work as well if all of the information/data (A1:J7) is on Sheet1 and my weight and miles and output columns are on Sheet2? I want to hide all of the data on Sheet1 and was wondering how it would work cross sheets?


Thanks again!

Jake

 

[Weazel]

it should work ok though you would need to modify the formula a little...

=INDEX(Sheet2!C3:J7,MATCH(A10,Sheet2!A3:A7),MATCH(B10,Sheet2!C1:J1))

and make sure that your weight and miles references are correct.

 

[jake424]

it should work ok though you would need to modify the formula a little...

=INDEX(Sheet2!C3:J7,MATCH(A10,Sheet2!A3:A7),MATCH(B10,Sheet2!C1:J1))

and make sure that your weight and miles references are correct.

It worked perfectly! Thank you so much for your help, I appreciate it!