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")))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[VinhPham]

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.

I''m just curious, can vlookup or index/match find a keyword within a query and match it with a corresponding terms? what about misspelled?

 

[West Man]

Here is a UDF (User Defined Function) that was written by C Moore and modified to match your needs.

Option Compare Text
Function ContainsWords(target As Range, wordarray As Range, resultarray As Range)
num_words = wordarray.Cells.Count
    For i = 1 To num_words


        If Not wordarray(i) = vbEmpty Then
            testword = wordarray(i)
            If InStr(target, testword) > 0 Then
            Words = Words + resultarray(i) & ", "
            End If
        End If
    Next i
    ContainsWords = Left(Words, Len(Words) - 2)
End Function

To use a UDF: In your workbook Press alt+F11 (at the same time) which will bring up the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>screen. Click on Insert and select module. Now copy the UDF provided and paste it in the rightmost pane on the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>screen. Use Alt+F11 to return to your worksheet. You use this new function like any Excel function. In the cells you wish to have the words returned type =Containswords(xx,yy,zz) where xx is the cell you are checking and yy is the range of the words to be checked against and zz is the result range. Be sure to use absolute references for these ranges, for example $a$2:$A$700, so that when copying the formula that range does not change.

 

[VinhPham]

Here is a UDF (User Defined Function) that was written by C Moore and modified to match your needs.

Option Compare Text
Function ContainsWords(target As Range, wordarray As Range, resultarray As Range)
num_words = wordarray.Cells.Count
    For i = 1 To num_words


        If Not wordarray(i) = vbEmpty Then
            testword = wordarray(i)
            If InStr(target, testword) > 0 Then
            Words = Words + resultarray(i) & ", "
            End If
        End If
    Next i
    ContainsWords = Left(Words, Len(Words) - 2)
End Function

To use a UDF: In your workbook Press alt+F11 (at the same time) which will bring up the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>screen. Click on Insert and select module. Now copy the UDF provided and paste it in the rightmost pane on the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>screen. Use Alt+F11 to return to your worksheet. You use this new function like any Excel function. In the cells you wish to have the words returned type =Containswords(xx,yy,zz) where xx is the cell you are checking and yy is the range of the words to be checked against and zz is the result range. Be sure to use absolute references for these ranges, for example $a$2:$A$700, so that when copying the formula that range does not change.

Thank you sir, you are a genius. This is awesome, I'm testing it right now and will let you know the result.

 

[VinhPham]

It works beautifully sir. Thank you so much. Would you please tell me where I can learn more about cool UDF like this?

 

[West Man]

I am a VBA beginner but have learned a lot through this site. I actually started with the Excel VBA for Dummies book. It is a good starting point.