Matching location info by fid# to campaign name

[rhartman]

Need a formula or macro to match up the fid# in Column B in Sheet1 (and its corresponding location data in the row) to the number within the Campaign name in column A in sheet2. Numbers that need to match are highlighted in red. Below data is only a small sample of the full list.


SHEET 1:

[TABLE="width: 1935"]
<tbody>[TR]
[TD]fid[/TD]
[TD]location_name[/TD]
[TD]address_1[/TD]
[TD]address_2[/TD]
[TD]suite[/TD]
[TD]city[/TD]
[TD]region[/TD]
[TD]post_code[/TD]
[TD]country[/TD]
[TD]post_code_plus[/TD]
[TD]local_phone[/TD]
[TD]url[/TD]
[/TR]
[TR]
[TD="align: right"]101 [/TD]
[TD]Arcadia[/TD]
[TD]815 West Naomi Avenue[/TD]
[TD]Unit #C&D[/TD]
[TD][/TD]
[TD]Arcadia[/TD]
[TD]CA[/TD]
[TD="align: right"]91007[/TD]
[TD]US[/TD]
[TD="align: right"]7560[/TD]
[TD](626) 445-9747[/TD]
[TD]http://locations.jennycraig.com/ca/arcadia/weight-loss-center-101.html[/TD]
[/TR]
[TR]
[TD="align: right"]102[/TD]
[TD]Brea[/TD]
[TD]2500 E. Imperial Highway[/TD]
[TD]#124-4[/TD]
[TD][/TD]
[TD]Brea[/TD]
[TD]CA[/TD]
[TD="align: right"]92821[/TD]
[TD]US[/TD]
[TD="align: right"]6122[/TD]
[TD](714) 671-0995[/TD]
[TD]http://locations.jennycraig.com/ca/brea/weight-loss-center-102.html[/TD]
[/TR]
[TR]
[TD="align: right"]103[/TD]
[TD]Downey[/TD]
[TD]8310 Firestone Boulevard[/TD]
[TD][/TD]
[TD][/TD]
[TD]Downey[/TD]
[TD]CA[/TD]
[TD="align: right"]90241[/TD]
[TD]US[/TD]
[TD="align: right"]3842[/TD]
[TD](562) 622-9230[/TD]
[TD]http://locations.jennycraig.com/ca/downey/weight-loss-center-103.html[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD]Glendale[/TD]
[TD]314-316 N. Glendale Ave[/TD]
[TD][/TD]
[TD][/TD]
[TD]Glendale[/TD]
[TD]CA[/TD]
[TD="align: right"]91206[/TD]
[TD]US[/TD]
[TD="align: right"]3758[/TD]
[TD](818) 240-7800[/TD]
[TD]http://locations.jennycraig.com/ca/glendale/weight-loss-center-104.html[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD]Lakewood[/TD]
[TD]4048 Hardwick Street[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lakewood[/TD]
[TD]CA[/TD]
[TD="align: right"]90712[/TD]
[TD]US[/TD]
[TD][/TD]
[TD](562) 630-6704[/TD]
[TD]http://locations.jennycraig.com/ca/lakewood/weight-loss-center-105.html[/TD]
[/TR]
[TR]
[TD="align: right"]106[/TD]
[TD]Irvine[/TD]
[TD]14415 Culver Drive[/TD]
[TD][/TD]
[TD][/TD]
[TD]Irvine[/TD]
[TD]CA[/TD]
[TD="align: right"]92604[/TD]
[TD]US[/TD]
[TD="align: right"]305[/TD]
[TD](949) 857-8171[/TD]
[TD]http://locations.jennycraig.com/ca/irvine/weight-loss-center-106.html[/TD]
[/TR]
[TR]
[TD="align: right"]110[/TD]
[TD]Studio City[/TD]
[TD]12930 Ventura Boulevard[/TD]
[TD]Ste. 226B[/TD]
[TD][/TD]
[TD]Studio City[/TD]
[TD]CA[/TD]
[TD="align: right"]91604[/TD]
[TD]US[/TD]
[TD="align: right"]2200[/TD]
[TD](818) 990-6212[/TD]
[TD]http://locations.jennycraig.com/ca/studiocity/weight-loss-center-110.html[/TD]
[/TR]
[TR]
[TD="align: right"]111[/TD]
[TD] Torrance[/TD]
[TD]21203 Hawthorne Blvd[/TD]
[TD]Unit 107-39 B[/TD]
[TD][/TD]
[TD]Torrance[/TD]
[TD]CA[/TD]
[TD="align: right"]90503[/TD]
[TD]US[/TD]
[TD="align: right"]5501[/TD]
[TD](310) 316-3047[/TD]
[TD]http://locations.jennycraig.com/ca/torrance/weight-loss-center-111.html[/TD]
[/TR]
[TR]
[TD="align: right"]114[/TD]
[TD]Northridge[/TD]
[TD]18007 Chatsworth Street[/TD]
[TD][/TD]
[TD][/TD]
[TD]Granada Hills[/TD]
[TD]CA[/TD]
[TD="align: right"]91344[/TD]
[TD]US[/TD]
[TD="align: right"]5608[/TD]
[TD](818) 832-4955[/TD]
[TD]http://locations.jennycraig.com/ca/granadahills/weight-loss-center-114.html[/TD]
[/TR]
[TR]
[TD="align: right"]115[/TD]
[TD]Huntington Beach[/TD]
[TD]7251 Warner Ave.[/TD]
[TD]Suite C[/TD]
[TD][/TD]
[TD]Huntington Beach[/TD]
[TD]CA[/TD]
[TD="align: right"]92647[/TD]
[TD]US[/TD]
[TD="align: right"]5487[/TD]
[TD](714) 841-1463[/TD]
[TD]http://locations.jennycraig.com/ca/huntingtonbeach/weight-loss-center-115.html[/TD]
[/TR]
[TR]
[TD="align: right"]116[/TD]
[TD] Culver City[/TD]
[TD]10814 Jefferson Boulevard[/TD]
[TD]Ste. F[/TD]
[TD][/TD]
[TD]Culver City[/TD]
[TD]CA[/TD]
[TD="align: right"]90230[/TD]
[TD]US[/TD]
[TD="align: right"]4988[/TD]
[TD](310) 253-5393[/TD]
[TD]http://locations.jennycraig.com/ca/culvercity/weight-loss-center-116.html[/TD]
[/TR]
[TR]
[TD="align: right"]117[/TD]
[TD] Montclair[/TD]
[TD]8801 Central Ave.[/TD]
[TD]Suite E[/TD]
[TD][/TD]
[TD]Montclair[/TD]
[TD]CA[/TD]
[TD="align: right"]91763[/TD]
[TD]US[/TD]
[TD="align: right"]1657[/TD]
[TD](909) 624-0058[/TD]
[TD]http://locations.jennycraig.com/ca/montclair/weight-loss-center-117.html[/TD]
[/TR]
[TR]
[TD="align: right"]118[/TD]
[TD]Riverside[/TD]
[TD]10560 Magnolia Avenue[/TD]
[TD]Suite F[/TD]
[TD][/TD]
[TD]Riverside[/TD]
[TD]CA[/TD]
[TD="align: right"]92505[/TD]
[TD]US[/TD]
[TD="align: right"]1889[/TD]
[TD](951) 359-2060[/TD]
[TD]http://locations.jennycraig.com/ca/riverside/weight-loss-center-118.html[/TD]
[/TR]
[TR]
[TD="align: right"]119[/TD]
[TD] Whittier[/TD]
[TD]15030 Whittier Boulevard[/TD]
[TD]Ste. 1[/TD]
[TD][/TD]
[TD]Whittier[/TD]
[TD]CA[/TD]
[TD="align: right"]90603[/TD]
[TD]US[/TD]
[TD="align: right"]2070[/TD]
[TD](562) 693-3761[/TD]
[TD]http://locations.jennycraig.com/ca/whittier/weight-loss-center-119.html[/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD]Ventura[/TD]
[TD]4255 E. Main Street[/TD]
[TD]Suite 14[/TD]
[TD][/TD]
[TD]Ventura[/TD]
[TD]CA[/TD]
[TD="align: right"]93003[/TD]
[TD]US[/TD]
[TD="align: right"]5283[/TD]
[TD](805) 644-9902[/TD]
[TD]http://locations.jennycraig.com/ca/ventura/weight-loss-center-121.html[/TD]
[/TR]
[TR]
[TD="align: right"]122[/TD]
[TD]Thousand Oaks[/TD]
[TD]728-732 N. Moorpark Road[/TD]
[TD][/TD]
[TD][/TD]
[TD]Thousand Oaks[/TD]
[TD]CA[/TD]
[TD="align: right"]91360[/TD]
[TD]US[/TD]
[TD="align: right"]3705[/TD]
[TD](805) 494-4425[/TD]
[TD]http://locations.jennycraig.com/ca/thousandoaks/weight-loss-center-122.html[/TD]
[/TR]
[TR]
[TD="align: right"]123[/TD]
[TD]West Hollywood[/TD]
[TD]100 North La Cienega Blvd.[/TD]
[TD]Suite A-105[/TD]
[TD][/TD]
[TD]Los Angeles[/TD]
[TD]CA[/TD]
[TD="align: right"]90048[/TD]
[TD]US[/TD]
[TD="align: right"]1938[/TD]
[TD](310) 358-0436[/TD]
[TD]http://locations.jennycraig.com/ca/losangeles/weight-loss-center-123.html[/TD]
[/TR]
</tbody>[/TABLE]






SHEET 2:


[TABLE="width: 1520"]
<tbody>[TR]
[TD]Campaign[/TD]
[TD]Company Name[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Postal Code[/TD]
[TD]Country[/TD]
[TD]Phone Number[/TD]
[/TR]
[TR]
[TD]Local: Springfield Centre: 2210[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Avondale Centre: 3209[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Tustin Centre: 167[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Green Tree Centre: 1253[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Countryside: 1704[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: London Centre: 1901[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Edison Centre: 2030[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Herndon Centre: 4409[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Champions Centre: 3052[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Annapolis Centre: 3303[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Staten Island Centre: 2035[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Huntington Station Centre: 2045[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: St. Catharines Centre: 1819[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Alexandria Centre: 4403[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Langley Centre: 1059[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Braintree Centre: 658[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Clackamas Centre: 454[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Hyde Park Centre: 1205[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Akers Mill Centre: 552[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Southshore Centre: 1971[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Glendale Centre: 3201[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Coral Springs Centre: 954[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: West Chester Centre: 2213[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: McLean Centre: 4411[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Fresno Centre: 3091[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Wayne Centre: 2027[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Palmdale Centre: 130[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Oviedo Centre: 1002[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Puyallup Centre: 417[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Columbia Centre: 3306[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Springfield Centre: 2025[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Westminster Centre: 3451[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Local: Manchester Centre: 663[/TD]
[TD]Jenny Craig®[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Welcome to MrExcel.

Something like this perhaps:

B2: {=MATCH(" " &A2,RIGHT(E$2:E$5,1+LEN(A2)),0)} array-entered
C2: =INDEX(E$2:F$5,B2,2)

Or in one go, C2: {=INDEX(E$2:F$5,MATCH(" " &A2,RIGHT(E$2:E$5,1+LEN(A2)),0),2)}

Excel 2010

	A	B	C	D	E	F
Table on another sheet
Somewhere 555
Palmdale: 130
Somewhere else 306
Columbia Centre 3306

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]FID[/TD]
[TD="align: right"]FindRow[/TD]
[TD="align: right"]FindValue[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]Q[/TD]
[TD="align: right"][/TD]

[TD="align: center"]P[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3306[/TD]
[TD="align: right"]4[/TD]
[TD="align: center"]S[/TD]
[TD="align: right"][/TD]

[TD="align: center"]Q[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]R[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]S[/TD]

</tbody>

Sheet1​