(IF(AND for multiple critera

bbs

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

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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"))))

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.

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.

Replies
5
Views
209
Replies
15
Views
591
Replies
24
Views
983
Replies
6
Views
310
Replies
4
Views
224

1,203,487
Messages
6,055,714
Members
444,811
Latest member
NotJack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back