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

#### Sundance_Kid

##### New Member
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).

Thanks

### 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
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).

#### Sundance_Kid

##### New Member
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?

#### jasonb75

##### Well-known Member
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
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.

Replies
5
Views
83
Replies
4
Views
113
Replies
19
Views
187
Replies
0
Views
205
Replies
14
Views
205

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.

### Which adblocker are you using?

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

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