I have a sheet named "lookUp" that has 3 columns

emp No Name Mobile No.
11111 Bruce, Jack lincoln 0404 126 561

and I have been given this:-

=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)

Which takes the info from the "LookUp" sheet to another sheet and puts the
name in to a cell.

But a new "LookUp1" sheet is laid out as

emp No Surname First Name Mobile No.
11111 Bruce Jack lincoln 0404 126 561

How can I change the Vlookup to now put both the Surname PLUS a comma and
then the First name all into the 1 cell in the sheet seeing that the new
LookUp1 nows seperates the surname(bruce) from the first names ??

So want

enter 1111

get

Bruce, Jack lincoln

Mike

Hi Mike,
I think you should use the & symbol to add two bits of text. Try this:
=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)&",
"&VLOOKUP(F9,LookUp1!$A$2:$B$1504,3,FALSE)
What this will do is lookup 1111 and return Bruce from the 2nd column,
then it will add ", " and then it will add Jack Lincoln from coloumn 3,
so the result is
Bruce, Jack Lincoln.
Clive

MikeR-Oz Wrote:
I have a sheet named "lookUp" that has 3 columns

emp No Name Mobile No.
11111 Bruce, Jack lincoln 0404 126 561

and I have been given this:-

=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE)

Which takes the info from the "LookUp" sheet to another sheet and puts
the
name in to a cell.

But a new "LookUp1" sheet is laid out as

emp No Surname First Name Mobile No.
11111 Bruce Jack lincoln 0404 126
561

How can I change the Vlookup to now put both the Surname PLUS a comma
and
then the First name all into the 1 cell in the sheet seeing that the
new
LookUp1 nows seperates the surname(bruce) from the first names ??

So want

enter 1111

get

Bruce, Jack lincoln

Mike


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=525139