Need Help on Lookup & Reference Function

[saifsawafi]

I'm building gas forecast model. I have a compressor with semi fixed capacity C . I also have two fields, A & B. A has priority over B, and B is just used to top-up. I have 4 production scenarios, depending compressor capacity C.
So in my forecast table there are 3 columns, for A, B & C. I need a logic that looks at fixed value in column W, which is C, and picks up the right Scenario to return B once you enter A. I have attached a file that would give much clear overview on whets required.

[benishiryo]

hi there. is column D really supposed to be 5.75 while W16:W24 is 5.8? if both are the same numbers, then this should work:
=SUMIFS($C$12:$O$60,$B$12:$N$60,U12,$D$12:$P$60,W12)

otherwise, provide a logic for us why should V16:V24 be picking scenario 1? is it because 5.8 is closest to 5.75?

[saifsawafi]

Hi

Indeed column D must excatly macth W16: W24. In W16:W24 I just typed the numbers manually for demo. Will try to apply your formular and see how it works, and indeed it worked. Thanks very much and well apprecoated