IF function with 3 conditions

Question

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 gt 21  Text 1  IF E9 lt 21 E9 gt 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

User · Accepted Answer

You can do it this way    IF E9 gt 21  Text 1  IF AND E9 gt  5 E9 lt  21   Test 2   Text 3      Note I assume you meant  gt   and  lt   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 gt 21  Text 1  IF E9 lt 5  Text 3   Text 2

User · Answer

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

User · Answer

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

User · Answer

You can simplify the 5 through 21 part    IF E9 gt 21  Text1  IF E9 gt 4  Text2   Text3

[excel] IF function with 3 conditions

Examples related to excel

Examples related to if-statement