Forum Discussion

carrots33's avatar
carrots33
Copper Contributor
Dec 13, 2018

Trying to create an IF AND formula

I need to be able to have a cell return a certain number if 2 conditions are met, with a total of 4 possible returns. I've tried to nest the if formulas with the and formula, but I don't know what I'...
Formulas and Functions
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Dec 14, 2018

Hi,

 

There is a syntax error in your formula:

=IF((AND(A9=2000,A10="Age 17-49")),"11.4"),IF((AND(A9=2000,A10="Age 50-59")),"14.88"),IF((AND(A9=2000,A10="Age 60-64")),"19.08"),IF((AND(A9=2000,A10="Age 65-75")),"24.28")

 

The Nested IF formula must be as follows:

=IF(AND(A9=2000,A10="Age 17-49"),"11.4",IF(AND(A9=2000,A10="Age 50-59"),"14.88",IF(AND(A9=2000,A10="Age 60-64"),"19.08",IF(AND(A9=2000,A10="Age 65-75"),"24.28"))))

Please compare them and notice the difference.

 

When you reach the value_if_false argument, you have to start a new IF in this argument.

But you have closed each if in this argument and started another one after it.

In Nested IF formula, you have to close all IF functions at the end of the formula.

I also removed some unnecessary parentheses around AND functions.

 

Please note that if you always have the same number in cell A9 (2000), then you don't need to check this number in the formula.

If so, you can remove the AND functions and simplify the formula as follows:

=IF(A10="Age 17-49","11.4",IF(A10="Age 50-59","14.88",IF(A10="Age 60-64","19.08",IF(A10="Age 65-75","24.28"))))

 

Also, if you have Excel 2019 or Office 365, you can try the new IFS function which is the new shorthand alternative to Nested IF:

=IFS(A10="Age 17-49","11.4",A10="Age 50-59","14.88",A10="Age 60-64","19.08",A10="Age 65-75","24.28")

 

Hope that helps

Resources