Return a value right before and after a specified date

[Drwats0n]

Hi Guys, need your help with the following requirement.

I wish to return a value of 0.1 and 0.2 based on the specified date of 27th July.
As you can see 0.1 and 0.2 is in between the specified date of 27th July.
Can you please help me with this please? Thanks in advance.


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Period[/TD]
[TD]Rate[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]27 July 18[/TD]
[/TR]
[TR]
[TD]10 July 18[/TD]
[TD]0.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 August 18[/TD]
[TD]0.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4 Sept 18[/TD]
[TD]0.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Drwats0n]

Anyone please help

 

[Aladin Akyurek]

Looks like you are asking for the following...

Book1
	A	B	C	D	E	F	G
1	Period	Rate		Date	27-Jul-18	0.1	0.2
2	10-Jul-18	0.1
3	3-Aug-18	0.2
4	4-Sep-18	0.3
Sheet1

In F1 control+shift+enter, not just enter:

=VLOOKUP(MAX(IF($A$2:$A$4<=E1,$A$2:$A$4)),$A$2:$B$4,2,0)

In G1 control+shift+enter, not just enter:

=VLOOKUP(MIN(IF($A$2:$A$4>=E1,$A$2:$A$4)),$A$2:$B$4,2,0)

If the MAXIFS and MINIFS functions are available on your Excel system...

In F1 just enter:

=VLOOKUP(MAXIFS($A$2:$A$4,$A$2:$A$4,"<="&E1),$A$2:$B$4,2,0)

In G1 just enter:

=VLOOKUP(MINIFS($A$2:$A$4,$A$2:$A$4,">="&E1),$A$2:$B$4,2,0)