I should note that this is based on names where there is only one first name
like Bob. If there might be names with more than one first name like Mary
Beth, then this probably won't work unless the lookup table has each first
name in a separate cell also.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try something like this.

A1 = lookup_value

A1 = Lee, Robert E

Lookup table:

..........E..........F.........G..........H
1...Smith.......Sue...................10
2...Jones.......Joe........J..........22
3...Smith.......Tom......R.........15
4...Lee.........Robert...E..........60
5...Brown....Bill.........T..........20

Array entered** : (all on one line)

=INDEX(H$1:H$5,MATCH(SUBSTITUTE(LEFT(A1,
FIND(" ",A1&" ",FIND(",",A1)+2)-1),",",""),
E$1:E$5&" "&F$1:F$5,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Tim Nealon" wrote in message
...
I have two sets of employee data. The first is a download from our
payroll
system that gives each employees department number. The second is a
download
of our health insurance billing. I need to break the health insurance
bill
out to the various departments. The only field that is the "same" in
both
sets of data is the employee name.
Unfortunatley, the health insurnace bill has the name in one field as
"last
name, firstname middle initial". The payroll data breaks last name,
first
name, and middle initial into three separate fields.
I have concatenated the payroll data into one field and then copied -
paste
values so it is no longer a formula. I then tried to use vlookup
combined
with the trim function in case there were unwanted spaces, but no luck.
All
I can get is N/A.
Naturally, to further complicate matters, is sometimes the middle initial
appears in one set of data but not another. Also, we have several
employees
with the same last names so I can't just search by last name.
Any ideas on how to lookup this data so I can get the departments from
the
payroll data over to my health insurance data?

Thanks