Calculate Full price Based on Location and 2 Dates

[dluhut]

Excel Experts,

I'm facing a challenge that I'm asking for help!

The challenge is that I need to get the full price based on LOCATION, INSTALL & DISCONNECT DATE and getting the correct Price based on the EFFECTIVE DATE.

I have attached a sample so that you guys could know what I'm trying to accomplish.

Also, to summarize, below is what the logic would be.

When a user keyed in a date, i.e Sept 15, 2012 for example,
(Point 1 & 2 is PROBLEM 1)
1. If Install Date is a string ('Prior to 20xx') OR <= Desired Date (i.e Sept 15, 2012) AND
2. Disconnect Date is 'Blank' OR > Desired Date (i.e. Sept 15, 2012) THEN
3. Go to 'Price Table' Sheet and check IF location exists (i.e. 'Inventory' sheet shows as Mexico to America, but 'Price Table' sheet shows as America to Mexico. This is ok)
4. (PROBLEM 2) If Location Exist, get the price where 'Price Table' Month (i.e. Jul 2012) <= Desired Date (Sept 15, 2012) <= 'Price Table' Month (i.e Jan 2013)