Nested If function help

Hello,

I'm trying to come up with a formula to retrieve the discounted rate to compare it to the booked rate. Under "sheet 1" of the attached worksheet under the "discount rate" header is where I would like to place the formula. Under "sheet 2" is a list of all the discounted rates for each "code", which vary depending on the in/out dates. Also listed in this sheet are "blackout dates", whereby no discount is applied if in/out date fall within these dates. Thus, i need a formula that will retrieve the corresponding discounted rate depending on the in/out dates, whilst also validating that it does not fall within the blackout dates. If it does fall within the blackout date or if the booked "code" does not have a corresponding discount, I would like the formula to simply populate the cell with "NA".

Hopefully this make sense. Any help would be much appreciated.

Thanks!