(IF(AND for multiple critera

[bbs]

I am trying to make a multiple critera statment and it keep returning a FALSE value. Basically I need to to look at two cells and say If E4=2.375 and F4=14, then multiply D4 by AB41, but if E4=2.375 and F4=12, then multiply D4 by AB40, etc etc eventually I will add in the statement if E4=2.875 and F4=14... and so on. Please help!

=IF(AND(E4=2.375,F4=14),D4*AB41,IF(AND(E4=2.375,F4=12),D4*AB40,IF(AND(E4=2.375,F4=11),D4*AB39,IF(AND(E4=2.375,F4=10),D4*AB38))))

 

[RonB1111]

It's returning FALSE because either E4 does not equal 2.375 and/or F4 is neither 14,12,11,or10. You need to specify in the formula what should be returned if your condition is false. If you want it to say NoMatch, the formula would be:

=IF(AND(E4=2.375,F4=14),D4*AB41,IF(AND(E4=2.375,F4=12),D4*AB40,IF(AND(E4=2.375,F4=11),D4*AB39,IF(AND(E4=2.375,F4=10),D4* AB38,"NoMatch"))))

 

[bbs]

It's returning FALSE because either E4 does not equal 2.375 and/or F4 is neither 14,12,11,or10. You need to specify in the formula what should be returned if your condition is false. If you want it to say NoMatch, the formula would be:

=IF(AND(E4=2.375,F4=14),D4*AB41,IF(AND(E4=2.375,F4=12),D4*AB40,IF(AND(E4=2.375,F4=11),D4*AB39,IF(AND(E4=2.375,F4=10),D4* AB38,"NoMatch"))))

And here in lies my problem, E4 does equal 2.375 and F4 equals 14. It will always be a combination of either 2.375 or 2.875 in the E column and 10 to 14 in the F column. This is why I am so stumped.

 

[RonB1111]

Are E14 and F4 "manually entered" numbers or the result of a formula? If it's a formula result, sometimes excel actually has, say, 2.3750000001 even though 2.375 is displayed (add up to 14 decimals to see). Alternatively, are any of the involved formatted as "text"? I've tried both yours and my formula and they both work properly returning a result when E4 & F4 equal the numbers in the formula, and returning either FALSE or "NoResult" if they don't.