Vlookup between specific dates

[Lazyh0rse]

Hi all

I need to apply price changes to diesel in a fleet based on the date that the change occurred.

So in my mind I need a formula in column C that multiplies that value in column B with the correct price value in column F based on the date.

I hope you can help.

[TABLE="class: grid, width: 531, align: left"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Fill up Date[/TD]
[TD="align: center"]Diesel Litres[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Price Change Date[/TD]
[TD="align: center"]New Price[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2018/08/01[/TD]
[TD]233,00[/TD]
[TD] [/TD]
[TD][/TD]
[TD]2018/08/01[/TD]
[TD]13,31[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2018/08/15[/TD]
[TD]265,69[/TD]
[TD] [/TD]
[TD][/TD]
[TD]2018/09/12[/TD]
[TD]14,49[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2018/08/20[/TD]
[TD]225,00[/TD]
[TD] [/TD]
[TD][/TD]
[TD]2018/10/18[/TD]
[TD]14,99[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2018/08/27[/TD]
[TD]270,00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2018/08/30[/TD]
[TD]167,00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]2018/09/05[/TD]
[TD]235,80[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2018/09/12[/TD]
[TD]154,82[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]2018/09/19[/TD]
[TD]270,00[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2018/09/27[/TD]
[TD]280,10[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2018/10/04[/TD]
[TD]218,10[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2018/10/11[/TD]
[TD]237,80[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2018/10/18[/TD]
[TD]230,10[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]2018/10/24[/TD]
[TD]320,0[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2018/10/31[/TD]
[TD]208,5[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2018/11/07[/TD]
[TD]272,1[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Hi there. Put this formula in C2 and then copy down: =VLOOKUP(A2,E:F,2,TRUE)

Regards
John

 

[Lazyh0rse]

Thanks John, exactly what I was looking for.

 

[jmacleary]

You're welcome. I should have explained it a bit - the TRUE option tells excel to find an approximate match, so it looks for the highest date less than or equal to the date in A2. Make sure you always keep the price changes in ascending date order or you may get unexpected results.