Array Formula Question.

[Evagrius Ponticus]

Hi,

This formula below of course works ( I think Junmo1 wrote it ). What I don't understand is why doesn't the lookup value clumn (D1:d13) have to be an array - this would make sense to me since the lookup value is an entire column and not a single value? In a formula such as this, how does one know which arguement to make the aray? Thanks for any insight.

=MATCH(D1:D13,{"Cancelled","Withdrawn","Rejected"},0)

 

[Richard Schollar]

Only if that formula appears within something like SUMPRODUCT does it not have to be array entered - otherwise, if not array-entered then it will only lookup the value in D1 against the match list.

 

[Evagrius Ponticus]

well, here is the entire formula - I was taking it apart to understand each concept piece by piece.

=SUMPRODUCT((B1:B13="Orange")*(NOT(ISNUMBER(MATCH(D1:D13,{"Cancelled","Withdrawn","Rejected"},0))))*(C1:C13={"Blue","Green"})*A1:A13)