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.
[TABLE="width: 271"]
<tbody>[TR]
[TD][TABLE="width: 271"]
<tbody>[TR]
[TD]Points[/TD]
[TD]Miles[/TD]
[/TR]
[TR]
[TD]MEMPHIS TN[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]GERMANTOWN TN[/TD]
[TD="align: right"]18[/TD]
[/TR]
[TR]
[TD]BARTLETT TN[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]CORDOVA TN[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]COLLIERVILLE TN[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD]MILLINGTON TN[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]EADS TN[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD]BARRETVILLE TN[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD]BOLTON TN[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]MUNFORD TN[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]DRUMMONDS TN[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]GALLAWAY TN[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]OAKLAND TN[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD]BRIGHTON TN[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]MOSCOW TN[/TD]
[TD="align: right"]42[/TD]
[/TR]
[TR]
[TD]MASON TN[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]COVINGTON TN[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]LA GRANGE TN[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD]GRAND JUNCTION TN[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]HENNING TN[/TD]
[TD="align: right"]59[/TD]
[/TR]
[TR]
[TD]ROSSVILLE TN[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD]RIPLEY TN[/TD]
[TD="align: right"]66[/TD]
[/TR]
[TR]
[TD]BROWNSVILLE TN[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD]STANTON TN[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]TIPTON TN[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]FORT PILLOW TN[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]BOLIVAR TN[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]HALLS TN[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]BELLS TN[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]MIDDLETON TN[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]WYNNBURG TN[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]SOMERVILLE TN[/TD]
[TD="align: right"]83[/TD]
[/TR]
[TR]
[TD]SAULSBURY TN[/TD]
[TD="align: right"]84[/TD]
[/TR]
[TR]
[TD]HORNSBY TN[/TD]
[TD="align: right"]86[/TD]
[/TR]
[TR]
[TD]MAURY CITY TN[/TD]
[TD="align: right"]86[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


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>[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Miles[/TD]
[TD="class: xl65, width: 87"]Memphis, TN[/TD]
[/TR]
[TR]
[TD="class: xl68"]0-15[/TD]
[TD="class: xl69, align: right"]$200[/TD]
[/TR]
[TR]
[TD="class: xl66"]16-20[/TD]
[TD="class: xl67, align: right"]$210[/TD]
[/TR]
[TR]
[TD="class: xl68"]21-25[/TD]
[TD="class: xl69, align: right"]$222[/TD]
[/TR]
[TR]
[TD="class: xl66"]26-30[/TD]
[TD="class: xl67, align: right"]$233[/TD]
[/TR]
[TR]
[TD="class: xl68"]31-35[/TD]
[TD="class: xl69, align: right"]$243[/TD]
[/TR]
[TR]
[TD="class: xl66"]36-40[/TD]
[TD="class: xl67, align: right"]$255[/TD]
[/TR]
[TR]
[TD="class: xl68"]41-45[/TD]
[TD="class: xl69, align: right"]$266[/TD]
[/TR]
[TR]
[TD="class: xl66"]46-50[/TD]
[TD="class: xl67, align: right"]$277[/TD]
[/TR]
[TR]
[TD="class: xl68"]51-55[/TD]
[TD="class: xl69, align: right"]$288[/TD]
[/TR]
[TR]
[TD="class: xl66"]56-60[/TD]
[TD="class: xl67, align: right"]$299[/TD]
[/TR]
[TR]
[TD="class: xl68"]61-65[/TD]
[TD="class: xl69, align: right"]$310[/TD]
[/TR]
[TR]
[TD="class: xl66"]66-70[/TD]
[TD="class: xl67, align: right"]$321[/TD]
[/TR]
[TR]
[TD="class: xl68"]71-75[/TD]
[TD="class: xl69, align: right"]$332[/TD]
[/TR]
[TR]
[TD="class: xl66"]76-80[/TD]
[TD="class: xl67, align: right"]$344[/TD]
[/TR]
[TR]
[TD="class: xl68"]81-85[/TD]
[TD="class: xl69, align: right"]$355[/TD]
[/TR]
[TR]
[TD="class: xl66"]86-90[/TD]
[TD="class: xl67, align: right"]$366[/TD]
[/TR]
[TR]
[TD="class: xl68"]91-95[/TD]
[TD="class: xl69, align: right"]$378[/TD]
[/TR]
[TR]
[TD="class: xl66"]96-100[/TD]
[TD="class: xl67, align: right"]$389[/TD]
[/TR]
[TR]
[TD="class: xl68"]101-105[/TD]
[TD="class: xl69, align: right"]$401[/TD]
[/TR]
[TR]
[TD="class: xl66"]106-110[/TD]
[TD="class: xl67, align: right"]$412[/TD]
[/TR]
[TR]
[TD="class: xl68"]111-115[/TD]
[TD="class: xl69, align: right"]$423[/TD]
[/TR]
[TR]
[TD="class: xl66"]116-120[/TD]
[TD="class: xl67, align: right"]$435[/TD]
[/TR]
[TR]
[TD="class: xl68"]121-125[/TD]
[TD="class: xl69, align: right"]$447[/TD]
[/TR]
[TR]
[TD="class: xl66"]126-130[/TD]
[TD="class: xl67, align: right"]$457[/TD]
[/TR]
[TR]
[TD="class: xl68"]131-135[/TD]
[TD="class: xl69, align: right"]$469[/TD]
[/TR]
[TR]
[TD="class: xl66"]136-140[/TD]
[TD="class: xl67, align: right"]$481[/TD]
[/TR]
[TR]
[TD="class: xl68"]141-145[/TD]
[TD="class: xl69, align: right"]$492[/TD]
[/TR]
[TR]
[TD="class: xl66"]146-150[/TD]
[TD="class: xl67, align: right"]$503[/TD]
[/TR]
[TR]
[TD="class: xl68"]151-155[/TD]
[TD="class: xl69, align: right"]$515[/TD]
[/TR]
[TR]
[TD="class: xl66"]156-160[/TD]
[TD="class: xl67, align: right"]$526[/TD]
[/TR]
[TR]
[TD="class: xl68"]161-165[/TD]
[TD="class: xl69, align: right"]$538[/TD]
[/TR]
[TR]
[TD="class: xl66"]166-170[/TD]
[TD="class: xl67, align: right"]$549[/TD]
[/TR]
[TR]
[TD="class: xl68"]171-175[/TD]
[TD="class: xl69, align: right"]$560[/TD]
[/TR]
[TR]
[TD="class: xl66"]176-180[/TD]
[TD="class: xl67, align: right"]$572[/TD]
[/TR]
</tbody>[/TABLE]

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.