CODEWITHSUNDEEP
excelif-statement
Dalilah Perez
Dalilah Perez

Reputation: 61

IF function with 3 conditions

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.

Upvotes: 5

Views: 277634

Answers (4)

zx8754
zx8754

Reputation: 56249

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}))

enter image description here

Upvotes: 4

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96781

You can simplify the 5 through 21 part:

=IF(E9>21,"Text1",IF(E9>4,"Text2","Text3"))

Upvotes: 0

Tommy Thai
Tommy Thai

Reputation: 101

=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.

Upvotes: 1

lurker
lurker

Reputation: 58324

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"))

Upvotes: 8

Related Questions