More than 26 nested IF statements?

[wdmiramesa]

Hello,

My name is Ming and I'm new to the forum. Thank you for having a resource such as this so I can ask questions. I've found a way to next up to 26 variables within one cell, like this...

=IF(OR((DG6=1),(DG6=2),(DG6=3),(DG6=4),(DG6=5),(DG6=6)),IF((DG6=1),BC6,IF((DG6=2),BD6,IF((DG6=3),BE6,IF((DG6=4),BF6,IF((DG6=5),BG6,BH6))))),IF(OR((DG6=7),(DG6=8),(DG6=9),(DG6=10),(DG6=11),(DG6=12)),IF((DG6=7),BI6,IF((DG6=8),BJ6,IF((DG6=9),BK6,IF((DG6=10),BL6,IF((DG6=11),BM6,BN6))))),IF(OR((DG6=13),(DG6=14),(DG6=15),(DG6=16),(DG6=17),(DG6=18)),IF((DG6=13),BO6,IF((DG6=14),BP6,IF((DG6=15),BQ6,IF((DG6=16),BR6,IF((DG6=17),BS6,BT6))))),IF(OR((DG6=19),(DG6=20),(DG6=21),(DG6=22),(DG6=23)),IF((DG6=19),BU6,IF((DG6=20),BV6,IF((DG6=21),BW6,IF((DG6=22),BX6,BY6)))),IF((DG6=24),BZ6,IF((DG6=25),CA6,IF((DG6=26),CB6,"")))))))

I wanted to find out if it's possible to go up to 52. I'd like to extract information based on which week in the year we're in (52 weeks in one year). So do you think this is possible? If not, I'll have to find another solution. Thank you for your expertise and help. Ming

[icestationzbra]

hi ming,

i am really not sure what you have going on there, however, i think the following formula might simplify your work a little bit.

assuming that you have numbers between 1 and 52 appear in cell DG6, and based on that, you need to fetch a corresponding value from the range BC6:DB6, try this:

Please Login or Register  to view this content.

if there is no value in DG6, this formula will show you #N/A. if you want to avoid displaying that error whenever DG6 is blank, you could just use the following:

Please Login or Register  to view this content.

[FDibbins]

I agree with Ice...nesting 26 if()'s is crazy...trying to nest 52 is insane...there are always better ways than using that many nested ifs. it may look impressive to those that dont know any better, but apart from anything else, try to trouble-shoot a monster like that

[wdmiramesa]

Hello Icestationzbra,

Thank you very much. I was unaware that there was an easier way. That worked great when I plugged it in.

However, I do have another problem...

My end product would be to upload it to Google spreadsheets so everyone at my work would have access to it. But unfortunately, the "lookup" function is nonexistent in Google spreadsheets. I think I ran into this problem before, which led me to my ridiculous nested IF statements. I appreciate any insight you might have to this. Thanks. Ming

[wdmiramesa]

Hi again,

Nevermind. I found that I just have to change "Lookup" to "Index" for Google spreadsheets. Thank you again. I'm still learning. Ming

[wdmiramesa]

Index didn't work. I'll direct the question to a Google forum. Thank you very much for your help in Excel. It worked great! Ming