Sumproduct OR coersion not working :s

[thorpyuk]

Hi,

A part of my coersion isn't working here (my OR statement):

=INDEX(Prices!$C$3:$C$2596,MATCH(LARGE(IF(Prices!$B$3:$B$2596=$F4,IF(((Prices!$J$3:$J$2596="Criteria1")*(Prices!$U$3:$U$2596=VLOOKUP(U4,Prices!$C:$U,19,FALSE))*(OR(Prices!$V$3:$V$2596="0",Prices!$V$3:$V$2596=$D4))*(Prices!$G$3:$G$2596="Criteria2")),Prices!$Q$3:$Q$2596)),1),Prices!$Q$3:$Q$2596,0))

If anyone can see what i'm doing wrong then i would be gratefull! Thanks

 

[RoryA]

You can't use OR in an array formula like that. Try using:

((Prices!$V$3:$V$2596="0")+(Prices!$V$3:$V$2596=$D4))

instead of the OR part

 

[thorpyuk]

Thanks Rorya, that's beautifully simple :>

 

[Aladin Akyurek]

Hi,

A part of my coersion isn't working here (my OR statement):

=INDEX(Prices!$C$3:$C$2596,MATCH(LARGE(IF(Prices!$B$3:$B$2596=$F4,IF(((Prices!$J$3:$J$2596="Criteria1")*(Prices!$U$3:$U$2596=VLOOKUP(U4,Prices!$C:$U,19,FALSE))*(OR(Prices!$V$3:$V$2596="0",Prices!$V$3:$V$2596=$D4))*(Prices!$G$3:$G$2596="Criteria2")),Prices!$Q$3:$Q$2596)),1),Prices!$Q$3:$Q$2596,0))

If anyone can see what i'm doing wrong then i would be gratefull! Thanks

Possibly...

=INDEX(Prices!$C$3:$C$2596,MATCH(MAX(
    IF(Prices!$B$3:$B$2596=$F4,
    IF(Prices!$J$3:$J$2596="Criteria1",
    IF(Prices!$U$3:$U$2596=VLOOKUP(U4,Prices!$C:$U,19,0),
    IF(ISNUMBER(MATCH(Prices!$V$3:$V$2596,CHOOSE({1,2},0,$D4),0)),
    IF(Prices!$G$3:$G$2596="Criteria2",Prices!$Q$3:$Q$2596))))),
      Prices!$Q$3:$Q$2596,0))

Quite heavy a formula...