0

I have a data filled from Column C9 to column I39 AND I Auto-Add text in Column J using this formula: =ArrayFormula(IF(I9:I39="Excellent","Excellent",IF(I9:I39="Good","Good",IF(I9:I39="Bad","Bad",""))))

I want to add also If(AND(C9:C39<>"",I9:I39=""),"Not Filled","") But I couldn't add it the the previous line.

https://docs.google.com/spreadsheets/d/1wRckJYig3zh2hon2xgCFpz_VeWHu8ky_uwVm4dhmH1A/edit?usp=sharing

1

If I understood well, you want to use an Arrayformula while using the AND( , ) function. It won't work, you should use this instead:

=ARRAYFORMULA(If((C9:C39<>"")*(I9:I39=""),"Not Filled",""))

Note:

Within ARRAYFORMULA:

  • to use AND, we use *
  • To use OR, we use +

Your first formula could also be simplified as follows:

=ARRAYFORMULA(IF(LEN(I9:I),I9:I,""))
1

You can use the following

=IFERROR(SWITCH(I9:I39,"Bad","Bad","","Not Filled","Excellent","Excellent"))

You can also take it a step further

=ARRAYFORMULA(IF(C9:C="",,IFERROR(SWITCH(I9:I,"Bad","Bad","","Not Filled","Excellent","Excellent"))))

Functions used:

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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