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:

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

<tbody>
</tbody>

SHEET 2:

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

<tbody>
</tbody>

 

[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
1	FID	FindRow	FindValue		Table on another sheet
2	130	2	Q		Somewhere 555	P
3	3306	4	S		Palmdale: 130	Q
4					Somewhere else 306	R
5					Columbia Centre 3306	S

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​