SELECT

s.Name

,s.City

,s.State

,r1.ReferenceName1

,r1.Address1

,r1.Address2

,r2.ReferenceName2

,r2.City AS R2_City

,r2.State AS R2_State

FROM #SourceData s

LEFT JOIN #ReferenceTable1 r1 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r1.ReferenceName1,CHARINDEX(' ',r1.ReferenceName1))

LEFT JOIN #ReferenceTable2 r2 ON LEFT(s.Name,CHARINDEX(' ',s.Name)) = LEFT(r2.ReferenceName2,CHARINDEX(' ',r2.ReferenceName2))

John