Google-sheets – How to correct this formula with If function

formulasgoogle sheets

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

Best Answer

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,""))
Related Topic