Nested IF alternative in my situation?

[Cferron]

Ok I have 26 or so nested IF that I want to test in a formula. I know the limit is 7 and often Vlookup is often suggested. However I'm not sure Vlookup is helpful in my situation. I explain.

The context.
I have a report with different data that need to be changed depending of a dropdown menu selection. There is about 26 different option in the menu.

The form itself

Depending of the dropdown selection the report shows the appropriate column from this tab with this information (the complete table is not shown) but the ANALYSE (T.M.) is the option in the menu:

The formula I was hoping to be able to paste was the following (SI stands for IF)

=SI($I$1=1;Donnees!B1;SI($I$1=2;Donnees!C1;SI($I$1=3;Donnees!D1;SI($I$1=4;Donnees!E1;SI($I$1=5;Donnees!F1;SI($I$1=7;Donnees!g1;SI($I$1=8;Donnees!h1;SI($I$1=9;Donnees!I1;SI($I$1=10;Donnees!J1;SI($I$1=11;Donnees!k1;SI($I$1=12;Donnees!l1;SI($I$1=13;Donnees!m1;SI($I$1=14;Donnees!n1;SI($I$1=15;Donnees!o1;SI($I$1=16;Donnees!p1;SI($I$1=17;Donnees!Q1;SI($I$1=18;Donnees!R1;SI($I$1=19;Donnees!S1;SI($I$1=20;Donnees!T1;SI($I$1=21;Donnees!U1;SI($I$1=22;Donnees!W1;SI($I$1=23;Donnees!X1;SI($I$1=24;Donnees!Y1;SI($I$1=25;Donnees!Z1;SI($I$1=26;Donnees!AA1;SI($I$1=27;Donnees!AB1;"Tonnage  non défini")))))))))))))))))))))))))))

The 7 limit is clearly past, I have try to define name and separate the IF by group of 7 as I read somewhere. This is not really clean and hard to modify also. Any alternative?

Thanks for your suggestion.

Regards,

Cferron

 

[MrKowz]

Welcome to the forums!

I'm assuming you are under the French system locale, try:

=SI(ET($I$1>0,$I$1<28);INDEX(Donnees!1:1,$I$1+1);"Tonnage non défini")

 

[ukmikeb]

Hi

Shouldn't that be -

=SI(ET($I$1>0,$I$1<28);INDEX(Donnees!1:1,,$I$1+1);"Tonnage non défini")

to refer to successive columns (B1, C1...)

hth

 

[MrKowz]

Hi

Shouldn't that be -

=SI(ET($I$1>0,$I$1<28);INDEX(Donnees!1:1,,$I$1+1);"Tonnage non défini")

to refer to successive columns (B1, C1...)

hth

Yes, thanks for catching that. My fingers must have hit the backspace when I was editing. But one other fix due to system locale:

=SI(ET($I$1>0,$I$1<28);INDEX(Donnees!1:1;;$I$1+1);"Tonnage non défini")

 

[Cferron]

Thanks for that EXTREMELY fast reply.

Unfortunately that did not work

I have tried to replace the , by ; for the AND (ET) argument, but still I was getting an error. Maybe it has something to do with the INDEX function?

=SI(ET($I$1>0;$I$1<28);INDEX(Donnees!1:1,$I$1+1);"Tonnage non défini")

Claude

 

[MrKowz]

Maybe it has something to do with the INDEX function?

It does, check posts #3 and #4.

 

[Cferron]

Forget my previous message!
You guys are way too fast!

This seem to work:
=SI(ET($J$1>0;$J$1<28);INDEX(Donnees!1:1;;$J$1+1);"Tonnage non défini")

 

[MrKowz]

Forget my previous message!
You guys are way too fast!

This seem to work:
=SI(ET($J$1>0;$J$1<28);INDEX(Donnees!1:1;;$J$1+1);"Tonnage non défini")

Glad it works, thanks for the feedback!

And just to let you know, if you or a colleague ever have the need to ask a question in another language, we have an entire board dedicated to questions in a language other than English: Questions in Other Languages

Cheers!