Nov 29 2022 12:29 AM
Hello
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
thanks
Nov 29 2022 01:50 AM
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.
Dec 05 2022 08:02 AM
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
Dec 05 2022 10:51 AM
Take a good look at your formula. It is not the same as the formula that I posted.