IF statement

[Ingolf]

Hey

I have this formula: =INDEX(A1:G24;SUMPRODUCT(--(IF(AT2=1;I18:M24=AU2;IF(AT2=2;N18:R24=AU2;IF(AT2=3;S18:W24=AU2;IF(AT2=4;X18:AC24=AU2;IF(AT2=5;AD18:AH24=AU2;IF(AT2=6;AI18:AM24=AU2;IF(AT2=7;I28:N34=AU2;IF(AT2=8;O28:S34=AU2;IF(AT2=9;T28:X34=AU2;IF(AT2=10;Y28:AC34=AU2;IF(AT2=11;AD28:AH34;IF(AT2=12;AI28:AN34)))))))))))))*ROW(A18:G24));MATCH(C15;A17:G17;0)) and I think is to much IF statement, cause any search in this area IF(AT2=11;AD28:AH34;IF(AT2=12;AI28:AN34) has result #ref.
Hoe can I rebuild this formula?
I use excel 2010
Thanks for any help!

 

[Ingolf]

This is CSE formula and is working!

=INDEX(A1:G24;SUMPRODUCT(--(IF(AT2=1;I18:M24=AU2;IF(AT2=2;N18:R24=AU2;IF(AT2=3;S18:W24=AU2;IF(AT2=4;X18:AC24=AU2;IF(AT2=5;AD18:AH24=AU2;IF(AT2=6;AI18:AM24=AU2;IF(AT2=7;I28:N34=AU2;IF(AT2=8;O28:S34=AU2;IF(AT2=9;T28:X34=AU2;IF(AT2=10;Y28:AC34=AU2;IF(AT2=11;AD28:AH34;IF (AT2=12;AI28:AN34)))))))))))))*ROW(A18:G24));MATCH(C15;A17:G17;0))

that is mean: if AT2= (let say) 3 lookup and find AU2 in area S18:W24....
if AT2= 9 lookup and find AU2 in area T28:X34....
hope you understand

 

[CharlesChuckieCharles]

is this excel? or another spreadsheet program?

i haven't seen ; used in IF statements before

so i can't help

 

[Ingolf]

Excel (2010).Thanks anyway.

 

[repairman615]

what does I18:M24=AU2 Mean?

...what is I18:M24=AU2, which looks like you're attempting to update range I18:M24 with the value from AU2 ?

I haven't seen this before and can not get the most simplified version of it to work
as i IF(A1=1,B1:B3=C2,"") =#VALUE!

did you mean IF( AND(AT2=1, I18:M24=AU2 ), true,false)

I had similar thoughts. I am believing the semicolon goes vertically. I could be wrong as I am not very good with array formulas (Control Shift Enter). I found a bit in the help (2007) in arrays and constants... bit over my head but worth a look.

I not understend CHOOSE(AT2,"Here for AT2=1","Here for AT2=2",..... my formula is
IF(AT2=1;I18:M24=AU2;IF(AT2=2;N18:R24=AU2....... how can I replace?

Using the CHOOSE() function a user may choose an outcome. Using the logical tests you have to find if cell AT2 contains a number from 1 to 12, a CHOOSE() would be better in my opinion to help streamline your formula and lose most of the IF()s.

You would need to replace each of the "Here for AT2=1" with what is needed (formula) to return in each of those. See help file.


Copy and paste the formula I provided into your spreadsheet that has a value 1 - 12 in AT2 and look at the results of the formula. Hope that clears up any confusion.


Jeff

 

[Ingolf]

I know what CHOOSE function is...but I not understand in my case how it works couse if AT2= (let say) 3 lookup and find AU2 in area S18:W24....
if AT2= 9 lookup and find AU2 in area T28:X34....
hope you understand ....
But your ideea was god for me couse I replace all that IF with :
CHOOSE(AT2;Area1;Area2;Area3;......Area12)=AU2) and now formula:
=INDEX(A1:G24;SUMPRODUCT(--(CHOOSE(AT2;Area1;Area2;Area3;.....Area12)=AU2)*ROW(A18:G24));MATCH(C15;A17:G17;0)) .... and now formula works fine.

Thank you for answer me.
PS You have to change ";" with "," couse I have diferent regional settings...<!-- / message -->

 

[repairman615]

Very glad to help!

Jeff