+ Reply to Thread
Results 1 to 6 of 6

Returning text from a cell in one column dependant on the text in cells two other columns

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    Bolton
    MS-Off Ver
    Excel 2010
    Posts
    2

    Returning text from a cell in one column dependant on the text in cells two other columns

    For example..... Column A contains Last Names, Column B contains First Names and Column C contains Full Names.


    0------A-------------------B---------------------C
    1
    2
    3---Last Name----------First Name------------Full Name
    4----Smith----------------Bob--------------Mr. Bob Smith
    5-----Doe----------------John--------------Mr. John Doe
    6-----Doe----------------Jane--------------Mrs. Jane Doe


    etc.

    On the sheet, I want to be able to type in cell A1 the 1st name, and in B1 the second name, and for their full name to appear in Cell C1. I have around 100 people on my sheet so beingable to type in their name at the top and have their full name appear in C1 will be very helpful. Several Clients are married (hence the John & Jane Doe example) and I want to be able to search for just one of the couple.

    I have tried formulas such as multiple VLOOKUPs within an IF statement but I probably didnt put those in correctly.

    PS. Sorry about all the lines, didn't know how to make it look like a table.

    Can anybody help?

    Cheers in advance.
    Last edited by lucasg; 04-16-2012 at 06:05 AM.

  2. #2
    Registered User
    Join Date
    04-13-2012
    Location
    nimbahera
    MS-Off Ver
    2007
    Posts
    3

    Re: Returning text from a cell in one column dependant on the text in cells two other colu

    hi,
    I tried this long ago. in excel goto function -> text function -> concatnate
    for example =a3,b3c3

  3. #3
    Forum Contributor bonny24tycoon's Avatar
    Join Date
    04-02-2012
    Location
    Hell
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    405

    Re: Returning text from a cell in one column dependant on the text in cells two other colu

    Hi lucasg,

    Can you attach an example spreadsheet?

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Returning text from a cell in one column dependant on the text in cells two other colu

    If you put your surname in D1 and the forename in E1 then something along the lines of:

    =INDEX(C:C,MAX(INDEX(ROW(A4:A500) * (A4:A500=D1) * (B4:B500=E1),0)))

  5. #5
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: Returning text from a cell in one column dependant on the text in cells two other colu

    Hi
    You can use concatenate function:
    =CONCATENATE(B2," ",A2)
    But the problem is how to check if the perosn entered is male / female for Mr./Mrs part....
    Click *, if my suggestion helps you. Have a good day!!

  6. #6
    Registered User
    Join Date
    04-16-2012
    Location
    Bolton
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Returning text from a cell in one column dependant on the text in cells two other colu

    Thanks Andrew-R, your solution works perfectly. thanks a lot

    Cheers everyone else for trying too

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1