Multi-lookup help with Ranges

[VolOdd]

Need some help, see the attached sample table

In the table above, I'm going to have two values that I need to lookup

First value is the "Average Days" (First Column)
Second value is the "Actual Number of Days" (Which will be in a range between "Days Low" and "Days High")
Between the two, it should return the discount.

So, for example
Average Days: 20
Actual Days: 10
Result: 3%

Average Days: 20
Actual Days: 17
Result: 1%

Average Days: 30
Actual Days: 4
Result: 4%

Average Days: 30
Actual Days: 26
Result: 2%

Any help would be appreciated, I'm able to do it with just one input with the LOOKUP function, but the two inputs has me stumped.

 

[VolOdd]

I've attached the sample workbook

 

[eibi]

I think this formula will do what you want:

=SUMPRODUCT((--(B5:B12=H5))*(--(C5:C12<=H6))*(--(D5:D12>=H6))*E5:E12)

 

[VolOdd]

Eibi!!!!! Thank you, life saver.

Was going down the path of IFERROR,INDEX,MATCH,INDEX ... this is much more elegant.

 

[bosco_yip]

Another option :

=SUMIFS(E:E,B:B,H5,C:C,"<="&H6,D:D,">="&H6)