Formula help

The_Kurgan

Active Member
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!

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.

Oops... that didn't work.

Try:
this is an array formula and must be entered with
Excel Workbook
ABCDE
1IDCreditSign-up agreementSign-up Date
2100\$100.00Standard1/1/2014
3100\$200.00VIP2/18/2014
4100\$300.00VIP3/15/2014
5100\$400.00VIP6/1/2014
6100\$500.00Standard10/1/2014
7200\$600.00Standard1/1/2014
8200\$700.00VIP2/18/2014
9200\$800.00VIP6/1/2014
10200\$900.00Standard10/1/2014
11
12
13ID100
14AgreementVIP
15Date3/20/2014
16Credit300
17
CTRL-SHIFT-ENTER.

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

/SOLVED

You're welcome. Glad that worked for you.

Replies
12
Views
1K
Replies
9
Views
311
Replies
2
Views
744
Replies
0
Views
409
Replies
2
Views
262

1,203,213
Messages
6,054,200
Members
444,708
Latest member
David R__

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.

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