Find as per weight

[turkanet]

Hi,
Attached is the file that i am searching my costs in green on sheet OFT, depending on the list of values on sheet PRE. I used SUMPRODUCT before i had to add weight criteria in yellow. but when i tried to add index formula in sumproduct, i had wrong result. I kindly ask your help. Thank you in advance for this formula.

[JohnTopley]

Try

Helper in column F of "PRE"

=C2&D2&E2

in "OFT"

in O2

=VLOOKUP($U2,OFFSET(PRE!$G$1,MATCH($D2&$N2&$E2,PRE!$F$2:$F$15,0),0,COUNTIF(PRE!$F$2:$F$15,$D2&$N2&$E2),7),MATCH(LEFT($F2,1),PRE!$K$1:$M$1,0)+4,1)

Sample in P2 of attached

[turkanet]

That is perfect, really thank you so much.

[chief_abound]

O2 --> =max(index((pre!$c$2:$c$15=d2)*(pre!$d$2:$d$15=n2)*(pre!$e$2:$e$15=e2)*(pre!$g$2:$g$15<=u2)*(left(f2,1)=pre!$k$1:$m$1)*(pre!$k$2:$m$15),))

* If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.