+ Reply to Thread
Results 1 to 4 of 4

Search for a word within a list of names and return the entire name within the cell.

  1. #1
    Registered User
    Join Date
    07-21-2014
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    12

    Search for a word within a list of names and return the entire name within the cell.

    I need to create a form where the user enters one word of a business name (ex. sears) into a cell. In another cell have a formula that will look for that specific word in a column of business names and return the entire content of the cell. These business names can contain several words (ex. Kmart owned by sears). The lookup word (sears) is not always at the end and it may not be just one word.

    So, to reiterate, when the user enteres sears in the blank cell, the cell located maybe next to it will have a formula that will look in Column C (the list of business names) and return the cell content of "kmart owned by sears".

    Thanks for you help.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Search for a word within a list of names and return the entire name within the cell.

    There's a few ways you could do this. Here are two off the top of my head:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    In this example, I was searching for A1 in H1:H6
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Search for a word within a list of names and return the entire name within the cell.

    With your list in A1:A20 and the input name in D1

    =INDEX($A$1:$A$20,MATCH("*"&D1&"*",$A$1:$A$20,0))
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-21-2014
    Location
    Montana
    MS-Off Ver
    2013
    Posts
    12

    Re: Search for a word within a list of names and return the entire name within the cell.

    Thank you both for your response! I used LOOKUP(2,1/ISNUMBER(SEARCH(A1,$H$1:$H$6)),$H$1:$H$6) and it worked perfectly.

    Thanks again!!

+ 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. Search a range Last Names for age & return the value to a List of FULL NAMES
    By LunarLights in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-16-2014, 05:02 PM
  2. Replies: 2
    Last Post: 08-05-2013, 04:45 PM
  3. [SOLVED] Search a list of names and automatically return any names not already included in table
    By bishbash89 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-21-2013, 09:06 AM
  4. Replies: 1
    Last Post: 01-25-2011, 10:50 PM
  5. how do I search a word or pdf for a list of names in a CSV file?
    By Martin Crane in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2008, 05:16 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