Share via

VLOOKUP and CONCATENATE together

Anonymous
2014-06-05T16:02:33+00:00

I have a huge database of information that returns first and last name in different columns. The worksheet I am creating needs to be able to combine the first and last name once the employees ID is entered "lastname, firstname". I tried using the VLOOKUP formula but even with the () around it will not combine the first and last name to display it and I get a VALUE ERROR

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-06-06T05:57:36+00:00

    Hi,

    You can use CONCATENATE and VLOOKUP by nesting them. 

    Let's assume that the Employee ID is entered in the cell G10, the table range is $A$1 to $D$100, First names are in column B, Last Names are in column C, then you can use the following formula:

    =CONCATENATE(VLOOKUP(G10,$A$1:$D$100,2,FALSE),", ",VLOOKUP(G10,$A$1:$D$100,3,FALSE))

    We are extracting the first name and last name using two VLOOKUPs and then combining them using CONCATENATE and also giving the comma and space between first and last name.

    Hope this helps.

    50+ people found this answer helpful.
    0 comments
  2. Vijay A. Verma 104.8K Reputation points Volunteer Moderator
    2014-06-05T17:28:31+00:00

    Assuming 123 is your employee ID and appears in column A and First Name and Last Name appears in Columns B and C respectively, your formula would be

    =VLOOKUP(123,$A:$C,3,0)&", "&VLOOKUP(123,$A:$C,2,0)

    Change your employee ID and column references as per your requirement.

    20+ people found this answer helpful.
    0 comments
  3. Anonymous
    2014-06-05T16:19:01+00:00

    Could you please post a small sample of data and expected result.

    5 people found this answer helpful.
    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2014-06-06T03:28:48+00:00

    Hi,

    Share data and expected result.

    0 comments