FirstName, LastName and vice versa

[Guest]

In columns X and Y of Sheet X, I have a list of names and numbers:

James, Lebron 82
O'Neal, Shaquille 80
Wade, Dwyane 79
Duncan, Tim 72

In column B of Sheet B the same names are listed in a different order with
firstname first:

Shaquille O'Neal
LeBron James
Tim Duncan
Dwyane Wade

In column C of Sheet B, I want to enter a formula that references the names
in Sheet X and returns the corresponding number for each name (ex: O'Neal =
80; Wade = 79, etc.)

Can anyone help?

Thanks,
Bob


What is the formula to do that?

thanks,
Bob

 

[Biff]

As long as the names are 2 words only (in both sheets):

=VLOOKUP(MID(B2&", "&B2,FIND(" ",B2)+1,LEN(B2)+1),X!X$2:Y$5,2,0)

Biff

 

[Guest]

The problem is that sometimes a name can have three words, such as: Van Horn,
Keith. Thanks anyway.

 

[Biff]

I just tested this again and it will in fact work on 3 word names.

Try it out.

Biff