Menu
I'm looking to create a formula with 3 conditions. It is currently only working with 2 conditions. Here's what I'm looking for:
E9 has a number
If the number is 21+ then I want it to show Text 1
If the number is between 5 and 21, then I want it to show Text 2
If the number is below 5, then I want it to show Text 3
This is what I currently have:
=IF(E9>21,"Text 1",IF(E9<21,E9>5,"Text 2")
When I try and add the final condition, it gives me an error that I've entered too many arguments for this function. When the number is below 5 it shows False.
I would prefer a solution that does not use VLOOKUP.
I'm not understanding why it's saying this is not allowed, I have another IF function with 5 nested formulas that works just fine.
791
If you have to write an IF statement with 3 outcomes, then you only need to use one nested IF function. The first IF statement will handle the first outcome, while the second one will return the second and the third possible outcomes.
It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement. TIP: If you have Excel 2016, try the new IFS function instead of nesting multiple IF functions.
IF is one of the Logical functions in Microsoft Excel, and there are 3 parts (arguments) to the IF function syntax: logical_test: TEST something, such as the value in a cell. value_if_true: Specify what should happen if the test result is TRUE. value_if_false: Specify what should happen if the test result is FALSE.
The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.
You can do it this way:
=IF(E9>21,"Text 1",IF(AND(E9>=5,E9<=21),"Test 2","Text 3"))
Note I assume you meant >= and <= here since your description skipped the values 5 and 21, but you can adjust these inequalities as needed.
Or you can do it this way:
=IF(E9>21,"Text 1",IF(E9<5,"Text 3","Text 2"))
Using INDEX and MATCH for binning. Easier to maintain if we have more bins.
=INDEX({"Text 1","Text 2","Text 3"},MATCH(A2,{0,5,21,100}))
33
=if([Logical Test 1],[Action 1],if([Logical Test 2],[Action 1],if([Logical Test 3],[Action 3],[Value if all logical tests return false])))
Replace the components in the square brackets as necessary.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
