Array Formula Issue

Question

So I have ArrayFormula checking Column C Price to see what range it falls in. Then it assigns the number based on that range it puts it in Column M. It works perfectly as a formula but when I make it an array it just post all Zeros. So not seeing any in the range even though they all are in one range or another. Any help is a appreciated.

=ARRAYFORMULA(IF(ROW(C1:C)=1,"Price Range ID", IF(ISBLANK(C1:C),"",IF(AND(C2:C>=1, C2:C<=150000), 1,IF(AND(C2:C>=150001, C2:C<=250000), 2, IF(AND(C2:C>=250001, C2:C<=350000),3, IF(AND(C2:C>=350001, C2:C<=450000),4, IF(AND(C2:C>=450001, C2:C<=550000),5, IF(AND(C2:C>=550001, C2:C<=650000),6, IF(AND(C2:C>=650001, C2:C<=750000),7, IF(AND(C2:C>=750001, C2:C<=900000),8, IF(AND(C2:C>=900001, C2:C<=1200000),9,0))))))))))))

Answer 1

and() is an aggregating function and will not work the way you seem to expect in an arrayformula() wrapper. You can use Boolean arithmetic and replace and() with the * operator, but I do not think it would actually be necessary here. Try this:

=arrayformula( 
  ifs( 
    row(C1:C) = 1, "Price Range ID", 
    isblank(C1:C), iferror(1/0), 
    C1:C < 1, 0, 
    C1:C <= 150000, 1, 
    C1:C <= 250000, 2, 
    C1:C <= 350000, 3, 
    C1:C <= 450000, 4, 
    C1:C <= 550000, 5, 
    C1:C <= 650000, 6, 
    C1:C <= 750000, 7, 
    C1:C <= 900000, 8, 
    C1:C <= 1200000, 9, 
    true, "that's one big number" 
  ) 
)