Forum Discussion

cmckernan93's avatar
cmckernan93
Copper Contributor
Nov 29, 2022

Best way to do a Lookup on a variable date range

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 

 

admin
excel
Formulas and Functions
training
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 29, 2022

    cmckernan93 

    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.

    • cmckernan93's avatar
      cmckernan93
      Copper Contributor
      Dec 05, 2022

      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 

       

Resources