Multiple lookup based on date range pricing

Hi,

I want to find out price of particular material appicable as on its shipping date. I am having price list of the material prices which are range bound. Please help me to get the price as on shipping date based on attached sheet.

Thanks in Advance.

Your dates in Price Data are formatted MM/DD/YYYY

Your dates in Data are formatted DD/MM/YYYY

First: Make the Data sheet same as the Price Data sheet


You'll need helper columns on each sheet
on BOTH sheets E2 =A2&C2, and copy down

on Date D2 =INDEX('Price Data'!$A:$D,MATCH($E:$E,'Price Data'!$E:$E,1),4)
and copy down.

Assuming Price Data is sorted by Part number and End Date, this will match the Price Data End Date that is closest to without being later than the Data Shipping Date