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!

 

[repairman615]

Hello,

At a glance I beleive the CHOOSE(AT2,,,,,,) would help here.

Try also replacing all semi colons with comma

Jeff

 

[repairman615]

I see that:

ROW(A4:G18) will always return 4 in this formula

Even ROWS(A4:G18) would constantly be 15. Maybe you know this.

Also close up the AI 18 from

(AT2=6;AI 18:AM24=AU2;

to:

(AT2=6;AI18:AM24=AU2;


Here is an example of CHOOSE()

=CHOOSE(AT2,"Here for AT2=1","Here for AT2=2","Here for AT2=3","Here for AT2=4","Here for AT2=5","Here for AT2=6","Here for AT2=7","Here for AT2=8","Here for AT2=9","Here for AT2=10","Here for AT2=11","Here for AT2=12")

Replace the "Here for..." with you formula needs

 

[Ingolf]

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?

 

[CharlesChuckieCharles]

IF(AT2=6;AI 18:AM24=AU2

is that a real space in AI18 or just a typo

 

[Ingolf]

No is not real

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))))))))))))

 

[CharlesChuckieCharles]

Should you be using ; in IF statements not ,

 

[CharlesChuckieCharles]

IF(AT2=1,I18:M24=AU2.....

Are you believing that you can update I18:M24 to the value of AU2 in an IF statement???

Because a formula can't update another cell

 

[Ingolf]

IF(AT2=1,I18:M24=AU2.....

Are you believing that you can update I18:M24 to the value of AU2 in an IF statement???

Because a formula can't update another cell

I don't follow you ???

If AT2=1 to 10 return corect answer but if AT2=10 or 11 return #ref

 

[CharlesChuckieCharles]

what does I18:M24=AU2 Mean?

IF( Logical statement, True value , false value)

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)