+ Reply to Thread
Results 1 to 10 of 10

Searching if any word within a cell within the master list from a table array of values

  1. 09-13-2013, 02:12 PM #1
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Searching if any word within a cell within the master list from a table array of values

    I have a master list which I would like to search against using a list of search queries, i.e. does the master list contain any of the values in my table array.

    If "Coke AG" is in the master list, I would like "Coke", in the table array to trigger the search results.

    More data:

    Master list Results Table Array
    Coachingleaders Confirmed Coach
    Coaches Confirmed Coke
    Tablet N/A Nike
    Coke AG Confirmed
    Nike New York Confirmed

    Do you know of any function that would solve for this? I'm thinking some "IF" functionality contained within a vlookup?

    Thank you for any help.

    Alex
    Reply With QuoteRegister To Reply
  2. 09-13-2013, 02:16 PM #2
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Searching if any word within a cell within the master list from a table array of value

    Not sure why the format on the data looks so funky. There are supposed to be three columns showing the desired results there
    Last edited by arosier; 09-13-2013 at 02:18 PM. Reason: bad format
    Reply With QuoteRegister To Reply
  3. 09-13-2013, 03:52 PM #3
    lazybone
    lazybone is offline
    Registered User
    Join Date
    05-15-2013
    Location
    Munich, Germany
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Searching if any word within a cell within the master list from a table array of value

    upload a excel file would help
    Reply With QuoteRegister To Reply
  4. 09-13-2013, 06:22 PM #4
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Searching if any word within a cell within the master list from a table array of value

    searchcontainstemplate.xlsx Here is a template of what I am trying to accomplish.
    Reply With QuoteRegister To Reply
  5. 09-13-2013, 06:48 PM #5
    lazyme
    lazyme is offline
    Forum Contributor
    Join Date
    08-30-2007
    Location
    Vancouver, B.C.
    Posts
    149

    Re: Searching if any word within a cell within the master list from a table array of value

    How many search terms in the Lookup Table are we looking at? If it is only a few then it still can be accomplish using a formula. Otherwise, it is much easier with VBA.
    Reply With QuoteRegister To Reply
  6. 09-13-2013, 07:06 PM #6
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Searching if any word within a cell within the master list from a table array of value

    There are thousands in the list. Do you happen to know the vba code?
    Reply With QuoteRegister To Reply
  7. 09-13-2013, 08:38 PM #7
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Searching if any word within a cell within the master list from a table array of value

    Try this in C2, then copy down.

    =IF(COUNT(LOOKUP(40000,SEARCH(E$2:E$7,A2))),A2,"")
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  8. 09-13-2013, 09:42 PM #8
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Searching if any word within a cell within the master list from a table array of value

    That is exactly what I am looking for! Thank you so much for your help. If you wouldn't mind, what is the meaning behind the "40000" in the formula? Is it just the code for look within?

    Thank you again, I have been trying to solve this all week. This is a huge relief!
    Reply With QuoteRegister To Reply
  9. 09-13-2013, 10:06 PM #9
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Searching if any word within a cell within the master list from a table array of value

    Total number of characters that a cell can contain is 32,767 characters so, SEARCH will look for E$2:E$7 words in A2 & give its position where all the words 'CONTAINS' in A2, so this will be <=32767. If any of the word couldn't found in E$2:E$7 will give an error. So if we use 40000 LOOKUP always look for <=lookup_value, which is 40000.

    Hope this helps you.
    Reply With QuoteRegister To Reply
  10. 09-13-2013, 10:16 PM #10
    arosier
    arosier is offline
    Registered User
    Join Date
    09-13-2013
    Location
    Ma
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Searching if any word within a cell within the master list from a table array of value

    Makes sense. Thanks again!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to input list of values in one cell to generate table of calculated values.
    By pmhowe in forum Access Tables & Databases
    Replies: 2
    Last Post: 12-14-2012, 01:17 PM
  2. Copy/Paste Cells from old list to new master list by matching values
    By rfsmith in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-19-2012, 05:32 PM
  3. Get 3 cell values from all the excel files located in folder & make table in master file
    By ronin21 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-18-2012, 08:50 AM
  4. Macro - Create smaller word tables from master excel table
    By VBA_n00b in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2011, 10:12 PM
  5. Build List using Array of Unique Cell Values
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-05-2011, 06:59 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1