and and or formula on excel

[suiz919]

Hi-Sorry in advance for such a long drawn out formula. I'm trying to create a formula which takes the weighted average of the costproceeds and ytw array when the asset class is either a "foreign government agency" or a "sovereign" bond and certain other parameters that i have here below in the formula but excludes portfolios like "ah pp", "ch select pp" and so forth. I think the formula below works well, but gives me an error when i add the "isnumber(match()" formula. Thank you in advance!

=SUMPRODUCT(--(PORTNAME<>"ah- pp")*(PORTNAME<>"ch select pp")*(PORTNAME<>"lex- pp")*(PORTNAME<>"nu- pp"),--(TRANTYPE=$B$1),--(REGION=$C$1),--(ISNUMBER(MATCH(PORTNAME={"foreign government agency";"sovereign"},0))),COSTPROCEEDS,YTW)/SUMPRODUCT(--(PORTNAME<>"ah- pp")*(PORTNAME<>"ch select pp")*(PORTNAME<>"lex- pp")*(PORTNAME<>"nu- pp"),--(TRANTYPE=$B$1),--(ISNUMBER(MATCH(PORTNAME={"foreign government";"sovereign"},0))),--(REGION=$C$1),COSTPROCEEDS)

 

[Andrew Poulsom]

Your MATCH isn't properly constructed. The syntax is:

=MATCH(lookup_value,lookup_array,match_type)

 

[suiz919]

actually, i caught my own mistake. the change should be made to

--(ISNUMBER(MATCH(ACLASS4,{"foreign government agency";"sovereign"},0)))

Thanks anyway for all your help guys! You guys rock!