Index with Multiple criteria and Partially defined date ranges.

Hello,
Please help!

I am having an issue with a criteria check for Index

I need to pull a cost based off a start date, The only end date is the date a new cost is added subsequently, else the cost is current to date.

So "Product A" starting Jan 1st, Feb 1st and Mar 1st all have a different costs on the Master Price List
However during certain dates, there is additional discounts added to those costs. End dates can be variable on the discounts but never over lap a new master list cost date.

If both start dates match exactly I can get it to work, So if the Master List Price and the Discount price both start Jan 01 I can match them..
If the Master Price starts Jan 01 and the discount starts at a later date like Jan 16, I cannot get it to match to the Jan 01 cost unless I manually input that cost.

I work with 3000+ discounts pulling from a master list of 10,000+ products, so to be able to match each discount period to the correct cost starting dates would be very time saving over manually entering all the current costs!

There should also be an MSRP field which changes with new costs, but I assume it will be the same formula for finding that, just a separate column in the same row.

Sample below of the setup of it with some notes in the file of what my goal is.


https://www.dropbox.com/s/4egkig925vgdf6h/Sample.xlsx

Thank You, I completely over looked using the LOOKUP function, that works perfectly.