simple multiple If's not working..please help

[sunrays]

=IF(Rules!$C$19="Positive",VLOOKUP(B116,'data as reported'!$A$1:$HO$20001,MATCH($C$6&$C$5,'data as reported'!$A$1:$HO$1,FALSE),FALSE)/1000000),IF(Rules!$C$19="Negative",
-VLOOKUP(B116,'data as reported'!$A$1:$HO$20001,MATCH($C$6&$C$5,'data as reported'!$A$1:$HO$1,FALSE),FALSE)/1000000),0))

For some reason this formula isnt working. Could someone tell me why and correct the same. Thanks.

Much appreciated.

 

[mvptomlinson]

Well, you don't say how it doesn't work (error? incorrect result?), and sometimes tough to tell without seeing the document. (I also think the title, "simple multiple If's not working" is a bit of a stretch as most Excel users I know would stare at your formula with wide eyes and a tilting of the head in confusion.)

That being said, you don't have a value_if_false argument for the nested IF (for "negative") and you have an IF-ending parenthesis after the first instance of 1000000. See if this helps..

=IF(Rules!$C$19="Positive",VLOOKUP(B116,'data as reported'!$A$1:$HO$20001,MATCH($C$6&$C$5,'data as reported'!$A$1:$HO$1,FALSE),FALSE)/1000000,IF(Rules!$C$19="Negative",
-VLOOKUP(B116,'data as reported'!$A$1:$HO$20001,MATCH($C$6&$C$5,'data as reported'!$A$1:$HO$1,FALSE),FALSE)/1000000,0))