Doing a Lookup between Dates & Returning a Value if before a certain date

[Sundance_Kid]

Hey,

I am trying to add an if part to a sum & vlookup formula, to lookup up & return a different column depending on whether the date is before a certain date.

Currently my formula is =SUM(O1*VLOOKUP(B1,Sheet1!$D$3:$F$8,3,0)) which is basically multiply the number of days in cell O1 x the relevant amount returned from the cells D3:F8 column 3 on a separate sheet based on cell B1.
Now what I am trying to do, is change the formula to include a date.

So it will be the same formula above but now saying if the date in column M1 is before the 1st February please look up cells D3:G8 & look up column 3 (F) but if the date is after the 1st Dec please look up column 4 (G).

Can anyone advise on the best way to include that please?

Thanks

 

[jasonb75]

Assuming Feb 2021,

=SUM(O1*VLOOKUP(B1,Sheet1!$D$3:$F$8,IF($M$1<DATE(2021,2,1),3,4),0))

I'm not sure if there is a typo or missing info in your post, but I've assumed that it should be before or after 1st Feb, not before 1st Feb or after 1st Dec (with nothing to say what happens between).

 

[Sundance_Kid]

Assuming Feb 2021,

=SUM(O1*VLOOKUP(B1,Sheet1!$D$3:$F$8,IF($M$1<DATE(2021,2,1),3,4),0))

I'm not sure if there is a typo or missing info in your post, but I've assumed that it should be before or after 1st Feb, not before 1st Feb or after 1st Dec (with nothing to say what happens between).

Hi, Yes you are correct it was a typo & it should have been the 1st Feb 2020 in all instances & not Dec.

Just on the <Date(2021,2,1) part, the date I have in the file is in the format DD/MM/YYYY - so for example 25/01/2020 being the 25th January 2020. How should the formula look in terms of <Date(2021,2,1) when entered?

Thanks for your help

 

[jasonb75]

How should the formula look in terms of <Date(2021,2,1) when entered?

Exactly the same, as long as the dates are valid then they are comparable regardless of format.

Invalid format dates rarely work with any formula.

 

[Sundance_Kid]

Exactly the same, as long as the dates are valid then they are comparable regardless of format.

Invalid format dates rarely work with any formula.

Hey, this is worked. Many thanks for your help.