IF function 64 level of nesting question.

[VinhPham]

Hi,
Part of my daily job is sorting and identifying profitable keywords based on the search queries from Google Adwords. Now, this process is both tedious and taxing so I came up with a formula that can quickly pick up some of the important terms inside of a query. The formula works like a charm but I couldn't go pass 64 levels. My question is, is there a better way to accomplish what I'm trying to do or how to go pass this limit? Thank you so much for your help.

This is how it looks like in excel:

This is the formula:
=IF(ISNUMBER(SEARCH("top",A16)),"Tops",IF(ISNUMBER(SEARCH("blou",A16)),"Blouses",IF(ISNUMBER(SEARCH("polo",A16)),"Polos",IF(ISNUMBER(SEARCH("sweate",A16)),"Sweaters",IF(ISNUMBER(SEARCH("turtlen",A16)),"Turtlenecks",IF(ISNUMBER(SEARCH("tank",A16)),"T Shirts & Tank Tops",IF(ISNUMBER(SEARCH("t shir",A16)),"T Shirts & Tank Tops",IF(ISNUMBER(SEARCH("tshi",A16)),"T Shirts & Tank Tops",IF(ISNUMBER(SEARCH("tee",A16)),"T Shirts & Tank Tops",IF(ISNUMBER(SEARCH("tunic",A16)),"Tunic",IF(ISNUMBER(SEARCH("cardi",A16)),"Cardigans",IF(ISNUMBER(SEARCH("hoodi",A16)),"Hoodies & Sweat Shirts",IF(ISNUMBER(SEARCH("sweat sh",A16)),"Hoodies & Sweat Shirts",IF(ISNUMBER(SEARCH("fleec",A16)),"Fleece",IF(ISNUMBER(SEARCH("botto",A16)),"Bottoms",IF(ISNUMBER(SEARCH("jean",A16)),"Jeans",IF(ISNUMBER(SEARCH("denim",A16)),"Jeans",IF(ISNUMBER(SEARCH("pant",A16)),"Pants",IF(ISNUMBER(SEARCH("long pant",A16)),"Pants",IF(ISNUMBER(SEARCH("khaki",A16)),"Bottoms",IF(ISNUMBER(SEARCH("short",A16)),"Shorts",IF(ISNUMBER(SEARCH("skirt",A16)),"Skirts",IF(ISNUMBER(SEARCH("skor",A16)),"Skorts",IF(ISNUMBER(SEARCH("legging",A16)),"Leggings & Tights",IF(ISNUMBER(SEARCH("tight",A16)),"Leggings & Tights",IF(ISNUMBER(SEARCH("sweatp",A16)),"Sweatpants",IF(ISNUMBER(SEARCH("dress",A16)),"Dresses",IF(ISNUMBER(SEARCH("maxi",A16)),"Maxi Dressses",IF(ISNUMBER(SEARCH("sundr",A16)),"Sun Dresses",IF(ISNUMBER(SEARCH("sun",A16)),"Sun Dresses",IF(ISNUMBER(SEARCH("maxidr",A16)),"Maxi Dressses",IF(ISNUMBER(SEARCH("rompe",A16)),"Rompers",IF(ISNUMBER(SEARCH("romp",A16)),"Rompers",IF(ISNUMBER(SEARCH("accesso",A16)),"Accessories",IF(ISNUMBER(SEARCH("glove",A16)),"Accessories",IF(ISNUMBER(SEARCH("hat",A16)),"Accessories",IF(ISNUMBER(SEARCH("mitten",A16)),"Accessories",IF(ISNUMBER(SEARCH("scarv",A16)),"Accessories",IF(ISNUMBER(SEARCH("bags",A16)),"Accessories",IF(ISNUMBER(SEARCH("handbag",A16)),"Accessories",IF(ISNUMBER(SEARCH("outerw",A16)),"Outerwear",IF(ISNUMBER(SEARCH("outer w",A16)),"Outerwear",IF(ISNUMBER(SEARCH("jacke",A16)),"Jackets",IF(ISNUMBER(SEARCH("coat",A16)),"Coats",IF(ISNUMBER(SEARCH("clth",A16)),"Clothes",IF(ISNUMBER(SEARCH("cloth",A16)),"Clothes",IF(ISNUMBER(SEARCH("blaz",A16)),"Blazers",IF(ISNUMBER(SEARCH("vest",A16)),"Vests",IF(ISNUMBER(SEARCH("unif",A16)),"Uniform",IF(ISNUMBER(SEARCH("sleep",A16)),"Sleepwear",IF(ISNUMBER(SEARCH("pj",A16)),"Sleepwear",IF(ISNUMBER(SEARCH("pajama",A16)),"Sleepwear",IF(ISNUMBER(SEARCH("active",A16)),"Activewear","Unassigned")))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[PCL]

See how to split the formula into several columns (and hide the columns) or see the use of names

 

[MrZNF]

I don't think there is a way to get around the limit.

You could try to filter out all the separate words in the "keyword" column and then count how often words appear everywhere. Whichever appears more = more profitable if I understand correctly.

 

[VinhPham]

Hi MrZNF, thank you for your help, I tried it your way but it took way more time that the original method.

 

[VinhPham]

See how to split the formula into several columns (and hide the columns) or see the use of names

Would you be elaborate? Thank you

 

[West Man]

Would a VBA custom function be acceptable? Also your formula does not have the ability to have more than one match to cell A16. Is it possible to have more than 1 match? If so, how would you like it to be handled?

 

[adambelnap]

What is a multiple if statement if not a 2-column vlookup chart? I'd recommend creating your list of IFs and Thens in two columns. then the nested if is just a vlookup. It matches the if an returns the formula text. Combine this with an indirect formula and not only is it easy to update and add words (you never have to mod the formula again just your if then list) you are limited only by the rows in excel.

 

[Norie]

Why not set up a table/list with the keywords in one column and the corresponding terms you want to return in an adjacent column?

Then you should be able to use lookup/match formulas to do what you want.

 

[MrZNF]

I'm curious how you tried this and why it took way more time.

You need to somehow filter out all words for the keyword column, after which you will have several columns (depending on the amount of words in one search) with words.

Column A: Keyword
Column B: =LEFT($A1,FIND(" ",$A1,1)-1)
Column C: =MID($A1,FIND(" ",$A1,LEN(B1))+1,FIND(" ",$A1,LEN(B1)+2)-FIND(" ",$A1,LEN(B1))-1)
Column D: You'll need to expand the formula, or maybe it can be done smarter, but I think this should allow you to separate all the data into pieces you can use to create for example a pivot table.

If you're saying that Excel can't handle the size (I'm not familiar with data sets this large), then I'd follow PCL's advice. Essentially create one IF formula per column, to get the results per IF formula. Then combine that into something you can use.

 

[VinhPham]

Would a VBA custom function be acceptable? Also your formula does not have the ability to have more than one match to cell A16. Is it possible to have more than 1 match? If so, how would you like it to be handled?

VBA is acceptable, and you are right, another limitation with this formula is that it only picks whatever term that come first. If there is a VBA that could help solving both problems, I would love to know more. Thank you.