Lookup? find name in cell

[Neil]

Hi!
Sorry about this cross post but it appears as though there are more people
on this group than at worksheetfunctions.

I need to lookup a name in a range and return the value of the cell to the
left of the found name.
I can do it if there is only one name in the cell but not if there is two or
more names in the cell seperated by hypens.
The workbook with the zones and names is also used by other workbooks and is
needed in that format.
for example.

Column A Column B
Zones Names
C1A Rob Logie
C1B Peter Fonti - Fred Thorp - Noel Bell
C1C Ernie Bondoc - Doug Ford
C1D Ernir Grosas - Noel Smith - Tony Smith

The name I am looking for is in one workbook and the list of names and zones
is in another.
I need to find say Fred Thorp and return C1B.
Can this be done and if so can it be done with a closed workbook.


Neil Eves

 

[Guest]

lookup will only work if all your criteria is in ascending order in both columns: ie 1 thru 10 or A to Z.
If all 3 names occur in one cell than Excel takes the first letter of that cell as its ascending criteria.

 

[Aladin Akyurek]

=INDEX('C:\NG_Files\[ClosedBookNeil
NG.xls]Sheet1'!$A$2:$A$5,MATCH("*"&A1&"*",'C:\NG_Files\[ClosedBookNeil
NG.xls]Sheet1'!$B$2:$B$5,0))