Return Value in Cell from Table

In the B14 cell, I need to have excel return the value in the D column that associates with Lot in the A column within the WT1 & Wt2 Range. This would be relative to Lot entered into the B12 cell and the within the ranges in the B2:C7 array.

For example, with the values entered in on the attached spreadsheet, B12 & B13, $1.05 would be returned to B14.

Thanks ahead of time for your help. Happy New Years!

Originally Posted by mike7952

One way

=SUMPRODUCT((Table10[Lot]=B12)*(Table10[Wt 1 Range]<=B13)*(Table10[Wt 2 Range]>=B13)*Table10[CoG])

Thanks Mike. This is a work in progress. I'll have additional questions pertaining to what i'm ultimately trying to accomplish.