Hi,
I have been coming across a problem using array formulas and I'm not sure If it's a simple fix or if it's just a limitation in Excel.
Basically, I am referring to several ranges of cells in a formula. If I refer to a range once like if(AJ2:AM2)="ALL"... the formula will work just fine. However, if I add to the formula like if(and(AJ2:AM2="ALL",AN2:AS2="ALL"... the formula will not recognize one of the ranges at all. So I am being forced to write formulas like if(and(or(AJ2="ALL",AK2="ALL",...etc which works just fine but takes a ton of time and seems like there should be an easier way.
My questions is why aren't my array formulas working if there are more than one in the same formula? I have to be doing something wrong. An example is below
{=IF(AND(OR(BC2="10-",BC2="20-",BC2="30-"),(AN2:AS2="HH"),OR(AJ2:AM2="ALL",AJ2:AM2="LHHA")),BC2&"100-"&AT2,"")}
{=IF(AND(OR(BC2="10-",BC2="20-",BC2="30-"),OR(AN2="HH",AO2="HH",AP2="HH",AQ2="HH",AR2="HH",AS2="HH"),OR(AJ2="ALL",AK2="ALL",AL2="ALL",AM2="ALL",AJ2="LHHA",AK2="LHHA",AL2="LHHA",AM2="LHHA")),BC2&"100-"&AT2,"")}
In the first formula above,the bold region represents what I'd like to refer to. It's not working so I have to write it like the bold region in the second formula.
Thank you for any help.
Bookmarks