+ Reply to Thread
Results 1 to 6 of 6

Searching a Range for a Specific Text and put a different text in a different cell

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    Places
    MS-Off Ver
    Excel 2010
    Posts
    2

    Searching a Range for a Specific Text and put a different text in a different cell

    Not sure this is where this question goes but who knows. What I am attempting to do it search a range of cells for a specific word or words that is found in a different cell. Then take information that is in relation to the first cell and put it in the same relation to the second cell. Just read the example.

    In the Example provided what I want, if possible, to have it search the range of First Names in Sheet two for those matching a name in the range from Sheet one, then place the corresponding last name, from sheet one, with the first name on sheet two.

    Don't know how complicated that would be, or not at all, but I have been trying to do it for ages and can't figure it out.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Searching a Range for a Specific Text and put a different text in a different cell

    Maybe:

    =VLOOKUP(C4,Sheet1!$C$2:$E$21,3,FALSE)

  3. #3
    Registered User
    Join Date
    11-13-2013
    Location
    Places
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Searching a Range for a Specific Text and put a different text in a different cell

    Thank you. I thought it might be easier than I was trying to make it

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Searching a Range for a Specific Text and put a different text in a different cell

    In principle, as John H. Davis has said:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This version uses whole columns in the lookup which needs less maintenance but could be slower.

    For performance and ease of maintenance, it would be better to define a dynamic named range:
    Say, NameTable:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then your formula could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    However, VLOOKUP will always return the first matching entry so it doesn't cope well with duplicates.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Searching a Range for a Specific Text and put a different text in a different cell

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Searching a Range for a Specific Text and put a different text in a different cell

    Thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Searching cells within a range that contain specific text
    By JohnG73 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-20-2016, 05:52 AM
  2. Searching if a cell contains a specific piece of text in an IF/OR formula
    By jshaw82 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-23-2013, 02:52 AM
  3. Replies: 2
    Last Post: 08-05-2013, 07:32 PM
  4. Replies: 19
    Last Post: 04-10-2013, 08:30 AM
  5. Searching for specific text within a cell
    By plannerg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2006, 05:38 PM

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