Formula help

[The_Kurgan]

I'm going slightly insane with this. Basically, I need to know if there is a way to choose a value based on the closest date that's >= a given date.

Here's an example:
If my Customer ID = 100, the sign-up agreement = VIP, and the sign-up date = 03/20/2014, I need a formula to produce a $300 Credit.

Col A​	Col B​	Col C​	Col D​
Customer ID​	Credit​	Sign-up agreement​	Sign-up Date​
100​	$100.00​	Standard​	1/1/2014​
100​	$200.00​	VIP​	2/18/2014​
100​	$300.00​	VIP​	3/15/2014​
100​	$400.00​	VIP​	6/1/2014​
100​	$500.00​	Standard​	10/1/2014​
200​	$600.00​	Standard​	1/1/2014​
200​	$700.00​	VIP​	2/18/2014​
200​	$800.00​	VIP​	6/1/2014​
200​	$900.00​	Standard​	10/1/2014​

<tbody>
</tbody>

I've found a formula to pull the max date given ID & agreement, but not "the next closest & under" the given date. Any help would be greatly appreciated!

 

[The_Kurgan]

I think I got this to work. For anyone interested, I first find the corresponding date using {=MAX(IF(D2:D9<03/20/2014,D2:D9))}. I then use INDEX & SUMPRODUCT to arrive at $300.

 

[The_Kurgan]

Oops... that didn't work.

 

[AhoyNC]

Try:
this is an array formula and must be entered with

Excel Workbook
	A	B	C	D	E
1	ID	Credit	Sign-up agreement	Sign-up Date
2	100	$100.00	Standard	1/1/2014
3	100	$200.00	VIP	2/18/2014
4	100	$300.00	VIP	3/15/2014
5	100	$400.00	VIP	6/1/2014
6	100	$500.00	Standard	10/1/2014
7	200	$600.00	Standard	1/1/2014
8	200	$700.00	VIP	2/18/2014
9	200	$800.00	VIP	6/1/2014
10	200	$900.00	Standard	10/1/2014
11
12
13	ID	100
14	Agreement	VIP
15	Date	3/20/2014
16	Credit	300
17
CTRL-SHIFT-ENTER.

 

[The_Kurgan]

That is truly impressive. I can't thank you enough!

/SOLVED

 

[AhoyNC]

You're welcome. Glad that worked for you.