Best way to do a Lookup on a variable date range

Occasional Contributor



what's the best formula to lookup a price based on dispatch date, customer code and item code based on another price file sheet with a variable pricing period?


in pics below- i want to return a price in the 'masterdata' sheet column AN - by looking up from the 'pricefile sheet' - as the price file changes as is dependent on despatch date the price will depend on the customer code, dispatch day and item code in the 'masterdata' sheet 




3 Replies


In AN2:

=INDEX(Pricefile!$G$2:$G$1000, MATCH(1, (Pricefile!$A$2:$A$1000=B2)*(Pricefile!$C$2:$C$1000<=M2)*(Pricefile!$D$2:$D$1000>=M2)*(Pricefile!$F$2:$F$1000=O2&""), 0))


Adjust the ranges if Pricefile has more than 1000 rows of data.

If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

Fill down.

Hi thanks Hans, This formula didn't work for me, I must be doing something wrong in the formula above and have reattached my new sheet.


Can i just match Column O2 in 'Masterdata' to lookup from column 'F' in 'Pricefile' and return if found the price in column 'G'?


I'm just unsure if I'm wording that right above 



Take a good look at your formula. It is not the same as the formula that I posted.