Formula works until I make it an arrayformula-CodePudding

I have a formula that works when it is dragged down but it stops working as soon as it's changed to be an array formula - why could this be?

This is the formula that works when dragged down =IF(AND($W246:W= "Hired",$Y246:Y<>"Y"),"Workforce Needed","Not")

This is an array formula that suddenly doesn't work. (It no longer pulls back "Workforce Needed" when conditions change - despite me running the same test with the original formula and it working then)

=ARRAYFORMULA( IF(ISBLANK(F91:F),,IF(AND($W91:W= "Hired",$Y91:Y<>"Y"),"Workforce Needed","Not")))

Column W is a data validation drop down (text) Column Y is an array formula doing a vlookup

Any ideas much appreciated.

CodePudding user response：

The and() function is an aggregating function and will not get row-by-row results in an array formula. To make it work, use Boolean arithmetic, like this:

(W91:W = "Hired") * (Y91:Y <> "Y")

CodePudding user response：

use:

=ARRAYFORMULA(IF(ISBLANK(F91:F),,IF(($W91:W="Hired")*($Y91:Y<>"Y"),
 "Workforce Needed","Not")))