+ Reply to Thread
Results 1 to 4 of 4

Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

  1. #1
    Registered User
    Join Date
    09-29-2015
    Location
    Detroit, Michigan
    MS-Off Ver
    Windows 8
    Posts
    1

    Lightbulb Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

    SO I think I'm going somewhere in the right direction, but I've hit a dead-end.

    In column A, each cell has a search phrase that would be put into Google (ex: A1="Purchase Electric Cars"). In column B, there will be the corresponding category of what kind of search the phrase in any given cell for Column A should be titled. These categories are based on what kind of words show up within the phrases that are in Column B. One of the category titles is "Transactional". A transactional search will be any search phrase that has the words "Buy, purchase, sale, location, dealership". There are four categories including Transactional and each has a list of words that would make a search phrase fall within that category.

    What I need is a formula that will check a cell in Column A to see if it contains any keywords that make it a transactional search, any keywords that make it an informational search, an investigation search or, if it contains none of the words from these lists, it will be a navigational search. This is the formula I have created. I do not know if it is a semantic problem, or if the formula is simply messed up.

    =IF(ISNUMBER(SEARCH({"features","benefits","pros","cons","info","information","specs","specifications","details"},A2)),"Informational",(IF(ISNUMBER(SEARCH({"reviews","pricing","price","model","roadster","versus","vs","deals","pre-owned","preowned","used","best","black","red","blue","silver","white","green","yellow","orange"},A2))),"Commercial Investigation",IF(ISNUMBER(SEARCH({"buy","purchase","sale","location","dealership”},A2)),"Transactional","Navigational))

    The first list, "features...details" would make cell B return "Informational" if it finds any of the words in that list within the phrase that is in cell A. If it does not contain anything from that list, it needs to check if it contains keywords from the Commercial Investigation list, and so on. If it does not contain keywords from any of these lists, it will by default be Navigational.

    *I've attached the excel file. Column C is actually where I am doing the formula, not B.
    Attached Files Attached Files
    Last edited by mmkessler; 09-29-2015 at 04:06 AM.

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

    Hi, welcome to the forum.

    The brackets in your formula were a bit confused and I think there was something going on with the quotation marks in your last array as I had to re-type it before it worked for me. Here's the amended formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and here's the file: MKT 363 TeslaMotors KeywordStrategy _ for mmkessler.xls
    Edit: Just drag the formula down to the end of your data (or Ctrl-double-click on the bottom-right of C2 to automatically fill the range down) - I couldn't do this for you as the file size was then too big to upload.
    Last edited by Aardigspook; 09-29-2015 at 05:41 AM. Reason: Add comment about filling formula down.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

    How do you modify this formula? Every time I try to change the search words, Navigational gets returned for every keyword.

  4. #4
    Forum Contributor
    Join Date
    06-16-2008
    Posts
    115

    Re: Having trouble with =IF(ISNUMBER(SEARCH... formula to find keywords from a cell.

    For example, if I wanted to change the Informational search words in the attached workbook.

    Work search words: features, benefits, pros and cons, pros, cons, info, information, specs, specifications, details

    Search words I'd like to use: "features", "how to", "ways to", "what is", "how", "info", "information", "details", "accessories", "where", "tips", "what", "do", "features", "how", "ways"

    One final question on this....Instead of writing every word in the formula, could I just use a cell reference? I could put all of my words in a cell, so I could change them as needed.

+ 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. [SOLVED] My =IF(ISNUMBER(SEARCH Formula is Not Working
    By thanhie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-03-2022, 12:30 AM
  2. [SOLVED] Modifying =IF(OR(ISNUMBER(SEARCH Formula To Only Look For Variable Within Part of Cell
    By SeanMichael in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-23-2014, 04:03 PM
  3. Need help with isnumber(search) Formula
    By lpretor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2013, 08:14 PM
  4. [SOLVED] Search for keywords and copy rows containing keywords to new sheet
    By lenorsk in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-15-2013, 06:54 AM
  5. [SOLVED] Trouble using ISNUMBER and SEARCH within a range
    By ktbb0312 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-23-2013, 05:56 PM
  6. Replies: 1
    Last Post: 08-12-2012, 10:12 PM
  7. using IF(ISNUMBER(SEARCH formula
    By dkmanley in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-19-2011, 01:43 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