+ Reply to Thread
Results 1 to 3 of 3

How to search for a list of words in a cell

  1. #1
    Registered User
    Join Date
    10-15-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    How to search for a list of words in a cell

    Hi I've got close to solving this issue but cant quite get it working.
    I need to search a cell containing several words to see if contains any words from a long list I have and return a true/false response

    I found some great formulas on this site which come really close using vlook up/if/search/index/match/find combinations and I can only seem to compare one word to the cell I am searching, not the full list.

    Attached is an example of the challenge

    Cell content examples:
    300g organic minced beef
    6x lincolnshire pork sausages
    12 oranges

    Search key words list example:
    Cheese
    Pork
    Beef

    Challenge:
    I want to check each cell to see if any words in the cell match any words in the list.
    So the responses I would expect in this example would be
    300g organic minced beef - True
    6x lincolnshire pork sausages - True
    12 oranges - false


    Im getting close with this
    =IF(ISNUMBER(SEARCH($F$3:$F$5,B5,1)),(TRUE),(FALSE))
    But it doesn't recognize the range F3:F5 and is only looking one of the items in the range.

    I have about 5000 cells to check against a list of 400 items, I've got as far as I can so any help greatly appreciated.
    Thanks
    Andy

    Ingredients example.xlsx

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to search for a list of words in a cell

    Hi,

    Try this in C3 instead:

    =SUMPRODUCT(--(ISNUMBER(SEARCH($F$3:$F$5,B3))))>0

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to search for a list of words in a cell

    Try in C3 and copy down.

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

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

+ 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. Replies: 3
    Last Post: 08-14-2013, 10:38 AM
  2. [SOLVED] Search cell to see if it contains text from a list of words
    By amurray2307 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-08-2013, 05:41 AM
  3. Replies: 3
    Last Post: 11-28-2012, 04:00 AM
  4. Looking for VBA that will search for a list of words and add a logo if found
    By kmckie in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-29-2012, 06:48 AM
  5. Replies: 1
    Last Post: 04-04-2012, 07:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1