# How to correct this formula with If function?

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.

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

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:

• Using `IF` is an overkill. Commented Apr 5, 2021 at 19:29
• Great Commentators. Thanks Commented Apr 6, 2021 at 7:48
• Switch is really great function. Thanks Commented Apr 6, 2021 at 18:29