If I've understood...
G24:
=LOOKUP(2,1/(($A$2:$A$19<=$D24)*($B$2:$B$19>=$D24)*($C$2:$C$19<=$E24)*($D$2:$D$19>=$E24)*($E$2:$E$19<=$F24)*($F$2:$F$19>=$F24)),$G$2:$G$19)
copy down as required
You could use Sumproduct also but this would be prone to error should you have 1+ instances where all criteria are met (ie the Prices would be summed)... the LOOKUP approach in such instances would return the last price.
Where no matches are found as is the case with row 28 you will get an #N/A error... to handle this and to avoid double evaluating what is a slow formula with an ISERROR/ISNA test I would myself opt for:
G24:
=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,LOOKUP(2,1/(($A$2:$A$19<=$D24)*($B$2:$B$19>=$D24)*($C$2:$C$19<=$E24)*($D$2:$D$19>=$E24)*($E$2:$E$19<=$F24)*($F$2:$F$19>=$F24)),$G$2:$G$19)))
This approach will return 0 where no Price is found.
Bookmarks