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

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
Assuming Feb 2021,
Excel Formula:
=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).
 
Solution

Sundance_Kid

New Member
Joined
Sep 2, 2017
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Assuming Feb 2021,
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 2, 2017
Messages
45
Office Version
  1. 365
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,376
Messages
5,624,334
Members
416,021
Latest member
simbonile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top