Pull rates from another sheet in work book

[ITSDISP]

I am sure my title is not correct on what I need to do.

OK, I have a spreadsheet (work book) that has 2 sheets in it. Points has city and miles.

Points Miles MEMPHIS TN 13 GERMANTOWN TN 18 BARTLETT TN 22 CORDOVA TN 24 COLLIERVILLE TN 27 MILLINGTON TN 29 EADS TN 32 BARRETVILLE TN 34 BOLTON TN 37 MUNFORD TN 37 DRUMMONDS TN 39 GALLAWAY TN 39 OAKLAND TN 39 BRIGHTON TN 42 MOSCOW TN 42 MASON TN 49 COVINGTON TN 52 LA GRANGE TN 52 GRAND JUNCTION TN 59 HENNING TN 59 ROSSVILLE TN 61 RIPLEY TN 66 BROWNSVILLE TN 68 STANTON TN 74 TIPTON TN 75 FORT PILLOW TN 76 BOLIVAR TN 77 HALLS TN 78 BELLS TN 79 MIDDLETON TN 79 WYNNBURG TN 80 SOMERVILLE TN 83 SAULSBURY TN 84 HORNSBY TN 86 MAURY CITY TN 86 <tbody> </tbody>

<tbody>
</tbody>

Rates has
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {color:black; font-size:10.0pt; font-weight:700; font-family:Arial, sans-serif; mso-font-charset:0; border:.5pt solid #4472C4 ;}.xl66 {color:black; font-size:11.0pt; border:.5pt solid #4472C4 ;}.xl67 {color:black; font-size:11.0pt; mso-number-format:"\0022$\0022\#\,\#\#0_\)\;\[Red\]\\\(\0022$\0022\#\,\#\#0\\\)"; border:.5pt solid #4472C4 ;}.xl68 {font-size:11.0pt; border:.5pt solid #4472C4 ; background:#D9E1F2; mso-pattern:black none;}.xl69 {font-size:11.0pt; mso-number-format:"\0022$\0022\#\,\#\#0_\)\;\[Red\]\\\(\0022$\0022\#\,\#\#0\\\)"; border:.5pt solid #4472C4 ; background:#D9E1F2; mso-pattern:black none;}--></style>

Miles	Memphis, TN
0-15	$200
16-20	$210
21-25	$222
26-30	$233
31-35	$243
36-40	$255
41-45	$266
46-50	$277
51-55	$288
56-60	$299
61-65	$310
66-70	$321
71-75	$332
76-80	$344
81-85	$355
86-90	$366
91-95	$378
96-100	$389
101-105	$401
106-110	$412
111-115	$423
116-120	$435
121-125	$447
126-130	$457
131-135	$469
136-140	$481
141-145	$492
146-150	$503
151-155	$515
156-160	$526
161-165	$538
166-170	$549
171-175	$560
176-180	$572

<tbody>
</tbody>

I need a formula that will go to the rate sheet and pull the rate over to the points sheet. Attached is a link to the spreadsheet on drop box. It should open. My 1st problem is the rates are grouped together. Any help would greatly be appreciated. I am doing this by hand now using a filter and I have thousands of these points.



https://www.dropbox.com/s/zfdejeoey36oyiz/HELP%20TEST.xlsx?dl=0

 

[jtakw]

Hi,

If I understand correctly, does this do what you need?

Book1
	A	B	C
2	MEMPHIS TN	13	200
3	GERMANTOWN TN	18	210
4	BARTLETT TN	22	222
5	CORDOVA TN	24	222
6	COLLIERVILLE TN	27	233
7	MILLINGTON TN	29	233
8	EADS TN	32	243
9	BARRETVILLE TN	34	243
10	BOLTON TN	37	255
11	MUNFORD TN	37	255
12	DRUMMONDS TN	39	255
13	GALLAWAY TN	39	255
14	OAKLAND TN	39	255
15	BRIGHTON TN	42	266
16	MOSCOW TN	42	266
17	MASON TN	49	277
18	COVINGTON TN	52	288
19	LA GRANGE TN	52	288
20	GRAND JUNCTION TN	59	299
21	HENNING TN	59	299
22	ROSSVILLE TN	61	310
23	RIPLEY TN	66	321
24	BROWNSVILLE TN	68	321
25	STANTON TN	74	332
26	TIPTON TN	75	332
27	FORT PILLOW TN	76	344
28	BOLIVAR TN	77	344
29	HALLS TN	78	344
30	BELLS TN	79	344
31	MIDDLETON TN	79	344
32	WYNNBURG TN	80	344
33	SOMERVILLE TN	83	355
34	SAULSBURY TN	84	355
35	HORNSBY TN	86	366
36	MAURY CITY TN	86	366
37	GADSDEN TN	87	366
38	DYERSBURG TN	88	366
POINTS

Cell Formulas
Range	Formula
C2	=LOOKUP(B2,LEFT(RATES!A$3:A$80,FIND("-",RATES!A$3:A$80)-1)+0,RATES!B$3:B$80)

Formula copied down.