Nested If(ISNUMBER(SEARCH... Question

[ireland13752]

Good day everyone and Happy Friday! I am trying to build a rather large complex nested IF statement. I have 49 different options that I need to reveiw for in column collection (or single cell) These are listed in the cell as shown below. And since they are in a single cell combined as they are, a VLookup wont work. Also since there will not always be the items I am looking for listed and some I do not care about at all it compounds the delimia. I can get the IF to work, at a smaller scale... I think I may have run into to too many arguments. Any suggestions? I have listed my formula as it is build below.
68.49 Total abd hyst NEC/NOS
65.61 Oth remove ovaries/tubes
47.19 Other incid appendectomy
45.62 Part sm bowel resect NEC
45.91 Sm-to-sm bowel anastom
54.59 Oth periton adhesiolysis
45.75 Opn lft hemicolectmy NEC
46.10 Colostomy NOS
=IF(ISNUMBER(SEARCH("*17.39*",U4)),"17.39",IF(ISNUMBER(SEARCH("*17.31*",U4)),"17.31",IF(ISNUMBER(SEARCH("*17.32*",U4)),"17.32",),IF(ISNUMBER(SEARCH("*17.33*",U4)),"17.33",IF(ISNUMBER(SEARCH("*17.35*",U4)),"17.35",IF(ISNUMBER(SEARCH("*17.34*",U4)),"17.34",IF(ISNUMBER(SEARCH("*17.36*",U4)),"17.36",IF(ISNUMBER(SEARCH("*45.03*",U4)),"45.03",IF(ISNUMBER(SEARCH("*45.26*",U4)),"45.26",IF(ISNUMBER(SEARCH("*45.41*",U4)),"45.41",IF(ISNUMBER(SEARCH("*45.49*",U4)),"45.49",IF(ISNUMBER(SEARCH("*45.52*",U4)),"45.52",IF(ISNUMBER(SEARCH("*45.71*",U4)),"45.71",IF(ISNUMBER(SEARCH("*45.76*",U4)),"45.76",IF(ISNUMBER(SEARCH("*45.79*",U4)),"45.79",IF(ISNUMBER(SEARCH("*45.81*",U4)),"45.81",IF(ISNUMBER(SEARCH("*45.82*",U4)),"45.82",IF(ISNUMBER(SEARCH("*45.83*",U4)),"45.83",IF(ISNUMBER(SEARCH("*45.92*",U4)),"45.92",IF(ISNUMBER(SEARCH("*45.93*",U4)),"45.93",IF(ISNUMBER(SEARCH("*45.94*",U4)),"45.94",IF(ISNUMBER(SEARCH("*45.95*",U4)),"45.95",IF(ISNUMBER(SEARCH("*46.03*",U4)),"46.03",IF(ISNUMBER(SEARCH("*46.04*",U4)),"46.04",IF(ISNUMBER(SEARCH("*46.10*",U4)),"46.10",IF(ISNUMBER(SEARCH("*46.11*",U4)),"46.11",IF(ISNUMBER(SEARCH("*46.13*",U4)),"46.13",IF(ISNUMBER(SEARCH("*46.14*",U4)),"46.14",IF(ISNUMBER(SEARCH("*46.43*",U4)),"46.43",IF(ISNUMBER(SEARCH("*46.52*",U4)),"46.52",IF(ISNUMBER(SEARCH("*46.75*",U4)),"46.75",IF(ISNUMBER(SEARCH("*46.76*",U4)),"46.76",IF(ISNUMBER(SEARCH("*46.94*",U4)),,"46.94",IF(ISNUMBER(SEARCH("*44140*",U4)),"44140",IF(ISNUMBER(SEARCH("*44141*",U4)),"44141",IF(ISNUMBER(SEARCH("*44143*",U4)),"44143",IF(ISNUMBER(SEARCH("*44144*",U4)),"44144",IF(ISNUMBER(SEARCH("*44145*",U4)),"44145",IF(ISNUMBER(SEARCH("*44146*",U4)),"44146",IF(ISNUMBER(SEARCH("*44147*",U4)),"44147",IF(ISNUMBER(SEARCH("*44150*",U4)),,"44150",IF(ISNUMBER(SEARCH("*44151*",U4)),"44151",IF(ISNUMBER(SEARCH("*44160*",U4)),"44160",IF(ISNUMBER(SEARCH("*44204*",U4)),"44204",IF(ISNUMBER(SEARCH("*44205*",U4)),"44205",IF(ISNUMBER(SEARCH("*44206*",U4)),"44206",IF(ISNUMBER(SEARCH("*44207*",U4)),"44207",IF(ISNUMBER(SEARCH("*44208*",U4)),"44208",IF(ISNUMBER(SEARCH("*44210*",U4)),"44210","")))))))))))))))))))))))))))))))))))))))))))))))))

 

[Haseeb A]

Hello,

Enter all possible SEARCH values in a range ie, A2:A50, then try;

=LOOKUP(100^100,SEARCH(A2:A50,U4),A2:A50)

 

[bobhc]

Good day ireland13752

You can have many nested if's, Excel 2003 and prior = maximum of 7 nested IF statements Excel 2007onwards = maximum of 64 nested IF statements.............but it is a bad idea to use or try to use so many.
I think you should look at Haseeb's solution, you will not have the trouble you get into trying to do all the nested if's

 

[ireland13752]

You my Friend are an absolute genius!!!!! Thank you so much! I am quite grateful for the help! Have a great weekend all!

 

[bobhc]

Good day ireland13752

If you use iferror you can show when the number you input is not in the index.

=IFERROR(LOOKUP(100^100,SEARCH(A2:A50,U4),A2:A50),"Required Index not Found")

If you get more than four Required Index not Found in one hour go and have some coffee