Another one:

=VLOOKUP(LEFT(A1,10)&"*",B$1:B$12,1,0)

Although, from the looks of your sample data you don't necessarily need to
limit the lookup_value to the first 10 characters. This will work just as
well with your sample data:

=VLOOKUP(A1&"*",B$1:B$12,1,0)


--
Biff
Microsoft Excel MVP


"Jill" wrote in message
...
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

--
Jill


"Ron Rosenfeld" wrote:

On Wed, 12 Mar 2008 16:59:00 -0700, Jill
wrote:

I would like to compare one cell (A1) to the first 10 characters in a
range
of cells (B1:B125). If there is a match, I would like it displayed in
C1.

Any help would be appreciated.

Not sure what you mean by "a match". Or what "it" is that you want
displayed.

If, by "a match", you mean to say that a match exists if the contents of
A1 are
duplicated somewhere within the first 10 characters in the range of
column B;
and if, by "it", you mean to display the contents of the cell in B that
contained a1, then something like:

=LOOKUP(2,1/SEARCH(A1,LEFT(B1:B125,10)),B1:B125)

might be what you are looking for.

If you mean something else, you'll have to be more specific.
--ron