change price according to date

RRRau 

Hi

This is a common situation. 

You may have multiple prices based upon different criteria (Say Store Regions, Clients Categories...etc)

In your question it is a DATE Cutoff (Before or After a specific date Say 1 September 2019

I created a sample File for you with 2 Price lists and in Cell B1 you enter the CutOff date

In the setup

we'll extract the price with a VLOOKUP function. However the Table array can be Price List 1 or 2 based upon the DateSo There is an IF function that selects the Table number (1 or 2 ) based upon the cutoff Date.

This if Function, is the first argument of a CHOOSE function: in case the IF returns "1" then the CHOOSE selects the first Price List and provides it as a Table Array to the VLOOKUP function. Same concept for dates after the CutOff date.

here is also a screenshot and the Function is:

=VLOOKUP($B5,CHOOSE(IF($A5<=$B$1,1,2),$G$5:$H$9,$G$14:$H$18),2,0)

I tested it and it is working fine

Of course the setup can be modified as needed with more Price Lists and more conditions

Hope that Helps

Nabil Mourad