Nested IF AND statements for number ranges [duplicate]

Question

I am working on an IF AND statement in google sheets where the return is one of five words. However, I would like the statement to run only if the value in another cell - in my case, in column J, same row - is "Yes"

I've got the basic IF AND working, but I don't know how to tell Google sheets to only perform the statement if J2, J3, J4, etc is "Yes"

I also need this statement to not return anything until there are values in the row...I think that's probably just another IF AND part to what I have and a "" at the end for the if false part - but I modified a nested IF AND I found here to get what you see below so I'm not sure what if any parenthesis would need to be added

Here's what I have:

=IF(AND(X2 > 0,X2 < 14),"Developing",IF(AND(X2 > 13,X2 < 27),"Emerging",IF(AND(X2 > 26,X2 < 40),"Expanding",IF(AND(X2 > 39,X2 < 53),"Accomplished","Transferred"))))

Here's my google sheet: HEAR Audit

I'm sure this is simple, but I'm completely new to Nesting IF AND statements, so help is appreciated.

Answer 1

I suggest you simplify your formula using vlookup.

Step1

Here's an example:

When you use if functions for a number of criteria you may always think of the possibility to do the same with the help of a vlookup formula.

To make it work, you need to prepare a help table with "Lower bounds" and "Comments" to return. There are two ways to accomplish this:

1. Prepare a separate table and use for reference. Like in the example above, the formula is =VLOOKUP(X2,A:B,2). This way is good because you can change data and formulas will recount according to new criteria.

2. The other way is to build the table inside of formula, using {}, the formula is:

   =VLOOKUP(X2,{0,"Other";1,"Developing";14,"Emerging";27,"Expanding";40,"Accomplished";53,"Transferred"},2)

Step2

Now it's time to add the other conditions. In your case the formula is:

=if(or(J2<>"yes",J2=""),"",VLOOKUP(X2,A:B,2))

Replace A:B to a range with your criteria.