lookup needed for names in varying order

[alicatnj]

Hi - I have two worksheets with employee data in over 7,000 rows. I need to
see which names on worksheet 1 are in worksheet 2. On both worksheets the
full names are in only one column and they are in varying order in each cell.
So for example:
B1 = Last name First name Middle name
B2 = First name last name middle name

There are no commas differentiating first name from last name on either
worksheet. And they are Brazilian names so most of the people have more than
one last name or middle name, so they could go in any order and they vary in
number of names per person. I have no way to determine where they should be
separated if I wanted to input a comma myself.

I put the cells on both worksheets in the same format and trimmed both so
there are no extra spaces. However when I do a vlookup I just get NA. I know
that the majority of the names in Worksheet 1 are in Worksheet 2 so I'm
definitely not setting up the right function.

What now?? Any suggestions? Your help is much appreciated.

Thanks,
Alison

 

[ryguy7272]

With the last name listed first, try this:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))&" "&LEFT(A2,FIND(",",A2)-1)

Regards,
Ryan---

 

[Billy Liddel]

Alison

If you only need to lookup one employee at a time then use a named range, a
list box and a lookup formula.

Say the named range for the employees is call Names, on sheet1 choose View,
Toolbox, Forms.

Click on the ListBox and draw it on the sheet. Right-click on the Listbox
and choose format. Set the Reference cell some on sheet1, I just used E2 and
set the Input Range as Names.

The formula =INDEX(Names,E2,1) gives you the Name, and change the offset for
different data.

For anything more complicated, you will need a List box from the Controls
Toolbox and some macros.

Hope this helps
Peter