1

This question already has an answer here:

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.

marked as duplicate by Rubén, serenesat, Vidar S. Ramdal, jonsca Oct 6 '16 at 3:08

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

I suggest you simplify your formula using vlookup.

Step1

Here's an example:

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.