FirstName, LastName and vice versa

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bob - 27 Oct 2006 03:37 GMT

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

Reply to this Message

Biff - 27 Oct 2006 03:54 GMT

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

> In columns X and Y of Sheet X, I have a list of names and numbers:
>
[quoted text clipped - 26 lines]
> thanks,
> Bob

Reply to this Message

bob - 27 Oct 2006 15:48 GMT

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

> As long as the names are 2 words only (in both sheets):
>
[quoted text clipped - 32 lines]
> > thanks,
> > Bob

Reply to this Message

Biff - 27 Oct 2006 18:54 GMT

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

Try it out.

Biff

> The problem is that sometimes a name can have three words, such as: Van
> Horn,
[quoted text clipped - 38 lines]
>> > thanks,
>> > Bob

Reply to this Message