Vlookup a text string

[Tim Nealon]

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

 

[www.exciter.gr: Custom Excel Functions!]

What if you combined Last+First names in a new column on both sheets
and then had a VLOOKUP search using these new columns?

http://www.exciter.gr
Custom Excel Applications and Functions!

 

[Tim Nealon]

The problem is that last name, first name and middle initial (if present) are
all in one field on the health insurance data sheet. I am unsure of how to
break out the last name and first name into individual fields. That's why I
went the route of concatenating (is that a word?) them on the payroll data.
I like your idea. Would you have any hints on how to go about breaking the
one field into two?
Thanks,
Tim

 

[crferguson]

Perhaps you need to trim the names on both sources, not just the
Payroll data? Kind of hard to pinpoint the problem without actually
looking at the two sheets. Also, while troubleshooting this, I
suggest trying to get the VLookUp to work on just one pair of values
that you know should work and go from there.

Cory

 

[www.exciter.gr: Custom Excel Functions!]

You could try with text-to-columns tool using space as the separator.
Your data may need a little correcting after this, ie in some cases
there will be 2 columns (no middle name) and in others 3 (with middle
name). It depends on how many rows you have in total.

http://www.exciter.gr
Custom Excel Applications and Functions!

 

[T. Valko]

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)

 

[T. Valko]

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.