On Wed, 12 Mar 2008 18:14:00 -0700, Jill
wrote:

Thanks so much for your quick responses. I will try to clarify with actual
data ...

Say A1 contains
PROVIDENCE MEM HOSP

and B1 thru B12 contains
PASO DEL NORTE SURGERY CENTER, EL PASO, TX
PHYSICIANS HOSPITAL, EL PASO, TX
PLAINS REGIONAL MED CTR-CLOVIS, CLOVIS, NM
PRESBYTERIAN HOSPITAL, ALBUQUERQUE, NM
PROVIDENCE MEM HOSP, EL PASO, TX
R E THOMASON HOSPITAL, EL PASO, TX
ROOSEVELT GENERAL HOSPITAL, PORTALES, NM
ROSWELL REGIONAL HOSPITAL, ROSWELL, NM
SOCORRO GENERAL HOSPITAL, SOCORRO, NM
SOUTHERN NEW MEXICO SURG CTR SUITE, ALAMOGORDO, NM
SOUTHWEST ENDOSCOPY, ALBUQUERQUE, NM
ST JOSEPH HOSPITAL, ALBUQUERQUE, NM

I want C1 to return
PROVIDENCE MEM HOSP, EL PASO, TX

Then, say A2 contains
ROSWELL REGIONAL HOSP

and B1 thru B12 contains the same data as listed above

I want C2 to return
ROSWELL REGIONAL HOSPITAL, ROSWELL, NM


Perhaps:

C1:
=IF(A1="","",LOOKUP(2,1/SEARCH(A1,LEFT($B$1:$B$12,LEN(A1))),$B$1:$B$12))

and fill down as far as required.

--ron