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.
Bookmarks