Help!!! Nested IF Statement limitation

[trenier]

Greetings,

I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function. Your help is greatly appreciated. See Below... The function works if i use 7 IF statements.


=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),IF(I3="Apr",IF(ISNUMBER(SEARCH("NA",Data!$E$355)),"NA",Data!$E$355))))) + IF(I3="May",IF(ISNUMBER(SEARCH("NA",Data!$F$355)),"NA",Data!$F$355),IF(I3="Jun",IF(ISNUMBER(SEARCH("NA",Data!$G$355)),"NA",Data!$G$355),IF(I3="Jul",IF(ISNUMBER(SEARCH("NA",Data!$H$355)),"NA",Data!$H$355),IF(I3="Aug",IF(ISNUMBER(SEARCH("NA",Data!$I$355)),"NA",Data!$I$355),IF(I3="Sep",IF(ISNUMBER(SEARCH("NA",Data!$J$355)),"NA",Data!$J$355)))))) + IF(I3="Oct",IF(ISNUMBER(SEARCH("NA",Data!$K$355)),"NA",Data!$K$355),IF(I3="Nov",IF(ISNUMBER(SEARCH("NA",Data!$L$355)),"NA",Data!$L$355),IF(I3="Dec",IF(ISNUMBER(SEARCH("NA",Data!$M$355)),"NA",Data!$M$355))))

Cheers,

Todd

 

[Superstar31]

Greetings,

I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function. Your help is greatly appreciated. See Below... The function works if i use 7 IF statements.




=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),IF(I3="Apr",IF(ISNUMBER(SEARCH("NA",Data!$E$355)),"NA",Data!$E$355))))) + IF(I3="May",IF(ISNUMBER(SEARCH("NA",Data!$F$355)),"NA",Data!$F$355),IF(I3="Jun",IF(ISNUMBER(SEARCH("NA",Data!$G$355)),"NA",Data!$G$355),IF(I3="Jul",IF(ISNUMBER(SEARCH("NA",Data!$H$355)),"NA",Data!$H$355),IF(I3="Aug",IF(ISNUMBER(SEARCH("NA",Data!$I$355)),"NA",Data!$I$355),IF(I3="Sep",IF(ISNUMBER(SEARCH("NA",Data!$J$355)),"NA",Data!$J$355)))))) + IF(I3="Oct",IF(ISNUMBER(SEARCH("NA",Data!$K$355)),"NA",Data!$K$355),IF(I3="Nov",IF(ISNUMBER(SEARCH("NA",Data!$L$355)),"NA",Data!$L$355),IF(I3="Dec",IF(ISNUMBER(SEARCH("NA",Data!$M$355)),"NA",Data!$M$355))))

Cheers,

Todd

Show us the data!!!

 

[DENWARD]

Suggest you try a vlookup table and a helper cell.

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

=IF(COUNT(SEARCH("NA",OFFSET(Data!$B$355,,MONTH(I3&14)-1))),"NA",OFFSET(Data!$B$355,,MONTH(I3&14)-1))

Markmzz

 

[trenier]

Thanks Denward. I am new to writing some of these Excel functions. Could you give me an example of what you mean? I appreciate your help.

Todd

 

[trenier]

Hi Mark,

I3 points to a cell that gets the current Month. Some of the fields have NA in them. I am open to any suggestions you have as I am new to writing functions in Excel.

Todd

 

[vlady]

maybe you should post a sample file
what is in Data!$B$355 text with na word

 

[vlady]

i just ask since you can do it like this if the I3 is a real date

I3 = 1/1/2014
=index(Data!$B$355:$M$355,month(I3))

i'm just confused why the search
IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355) why not just return what is in B355

 

[Aladin Akyurek]

[...]

Is it something like:

=LOOKUP(9.99999999999999E+307,
  SEARCH({"jan',"feb","mar","apr","may","jun","jul","aug",
    "sep","oct","nov","dec"},I3),
  Data!$B$355:$M$355)

what you are after?

If so, you can create a range housing the month names and naming this range as MonthList and replace the above formula with:

=LOOKUP(9.99999999999999E+307,SEARCH(MonthList,I3),Data!$B$355:$M$355)

 

[trenier]

I3 contains just the current Month such as "Jan" "Feb". I need to extract the data from the source worksheet. Jan, Feb are headings in the source worksheet. Users enter NA in the fields when they do not have a value. I3 contains the current month. I need to retrive values for a particular month that are arranged in columns for a particular business metric.

Sorry my skills in Excel in this area are not strong.


Thanks for everyones help on this!

Todd