Help with Excel formula (Maybe Index-Match?)

[jtiong]

Hi all,

I need help with coming up with a formula for Excel to return what I am looking for based on 2 inputs.

I have 3 columns: Start Date, End Date and B. Start Date and End Date are the same, while B has the values that I want to find based on the Start and End dates. Please see below for a snapshot of my data:

[TABLE="width: 278"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2007[/TD]
[TD="align: right"]4/30/2007[/TD]
[TD="align: right"]1.87%[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2007[/TD]
[TD="align: right"]5/31/2007[/TD]
[TD="align: right"]3.56%[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2007[/TD]
[TD="align: right"]6/30/2007[/TD]
[TD="align: right"]5.72%[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2007[/TD]
[TD="align: right"]7/31/2007[/TD]
[TD="align: right"]5.58%[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2007[/TD]
[TD="align: right"]8/31/2007[/TD]
[TD="align: right"]4.62%[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2007[/TD]
[TD="align: right"]9/30/2007[/TD]
[TD="align: right"]9.88%[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2007[/TD]
[TD="align: right"]10/31/2007[/TD]
[TD="align: right"]11.66%[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2007[/TD]
[TD="align: right"]11/30/2007[/TD]
[TD="align: right"]9.36%[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2007[/TD]
[TD="align: right"]12/31/2007[/TD]
[TD="align: right"]9.30%[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2008[/TD]
[TD="align: right"]1/31/2008[/TD]
[TD="align: right"]5.32%[/TD]
[/TR]
[TR]
[TD="align: right"]2/29/2008[/TD]
[TD="align: right"]2/29/2008[/TD]
[TD="align: right"]5.95%[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2008[/TD]
[TD="align: right"]3/31/2008[/TD]
[TD="align: right"]3.44%[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2008[/TD]
[TD="align: right"]4/30/2008[/TD]
[TD="align: right"]1.35%[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2008[/TD]
[TD="align: right"]5/31/2008[/TD]
[TD="align: right"]2.27%[/TD]
[/TR]
</tbody>[/TABLE]

I made another table and validated Start Date and End Date so that Excel will return the B value that I want based on the Start and End dates.

[TABLE="width: 120"]
<tbody>[TR]
[TD="width: 60"]<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]-->[TABLE="width: 120"]
<!--StartFragment--> <colgroup><col width="60" span="2" style="width:45pt"> </colgroup><tbody>[TR]
[TD="width: 60"]Year[/TD]
[TD="width: 60, align: right"]2007[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD="class: xl63, align: right"]04/30/07[/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD="class: xl63, align: right"]09/30/07[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="class: xl64, align: right"]9.88%[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


[/TD]
[TD="class: xl63, width: 60, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
For example, if I choose 4/30/2007 as Start Date and 9/30/2007 as End Date, the formula should return 9.88%. Therefore, the "answer" should always be in the same row as the End Date.

Another complexity that I face is that the Start Date must always be 4/30 (30 April) of every year and can only go up to 4/31 (31 May) of the next year (our year starts in April and ends May the year after). I was thinking of first choosing the year (Again, I used cell validation), and the Start Date will automatically choose 4/30 of whichever year it is, and the End Date will be a dropdown of up till 4/31 of the next year.

I suppose I will need a formula for Start Date, End Date and B. Please kindly advise! If there is an easier way to do it, please also do let me know - I am flexible. And not very good with Excel.

Thank you in advance!

 

[Nishant94]

Assuming your lookup table is in range A1:C15 and the table where you want your result to be in range A18:B21 as per the example given by you above ,

Then put this formula in Start date in result table, that is in B19: =DATE(B18,4,30)

This will put the date to be 30th April of whichever year you specify in Cell B18.

And in cell where you want your result, that is put this in B21: =VLOOKUP(B20,B2:C15,MATCH(A21,B1:C1,0),0)

 

[jtiong]

Thank you Nishant94! It works beautifully now.

For the End Date, how can I make it a dropdown list with dates up till 31st May of the next year?

 

[ButtFace]

Probably the easiest way would be to put today's date in a cell, drag it down until you get to 5/31/2017. Then in the end date column, select all the cells you want to have the dropdown, then on the Data tab, click validation. Change "Allow Any Value" to "List", then under "Source", select the cells with the date range.

 

[Nishant94]

Select the cell B20 and press alt+D+L

In allow option select "list" from drop down
And then, in "Source:" type =$B$2:$B$15


Press Ok

 

[jtiong]

Thank you ButtFace and Nishant94. Sorry if I was unclear earlier.

For the End Date, how can I write the formula such that for each year, in this example 2007, I would like the End Date to show only 30th April 2007, 31st May 2007... up till 31st March 2008. And if I change the year (B18) to 2008, the End Date will only display 30th April 2008-31st March 2009, rather than show all the dates in ButtFace's case, or only April 07-March 08 for Nishant94's case..

 

[jtiong]

Just in case anyone's interested, I managed to get it to work. I created another column with the year and I used offset and match in data validation to create a dropdown list with the months I want that corresponds to the year I chose. Please see below for example:

[TABLE="width: 398"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 327"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Start Date (B1)[/TD]
[TD]Year (C1)[/TD]
[TD]End Date (D1)[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]4/30/2007[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]5/31/2007[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]6/30/2007[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]7/31/2007[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]8/31/2007[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]9/30/2007[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]10/31/2007[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]11/30/2007[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2007[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]12/31/2007[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2008[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]1/31/2008[/TD]
[/TR]
[TR]
[TD="align: right"]2/29/2008[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2/29/2008[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2008[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]3/31/2008[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2008[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]4/30/2008[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2008[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]5/31/2008[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2008[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]6/30/2008[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My "year" starts in April and ends in March the year after.

<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}o\:* {behavior:url(#default#VML);}x\:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</style><![endif]-->[TABLE="width: 120"]
<!--StartFragment--> <colgroup><col width="60" span="2" style="width:45pt"> </colgroup><tbody>[TR]
[TD="width: 60"]Year (K3)[/TD]
[TD="width: 60, align: right"]2012 (L3)[/TD]
[/TR]
[TR]
[TD]Start Date (K4)[/TD]
[TD="class: xl64, align: right"]30/4/12 (L4)[/TD]
[/TR]
[TR]
[TD]End Date (K5)[/TD]
[TD="class: xl64, align: right"]30/9/12 (L5)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
For End Date, my formula in data validation is: =OFFSET($D$1,MATCH($L$3,$C$2:$C$116,0),0,COUNTIF($C$2:$C$116,$L$3),1). Hope this will help others!

Thanks again ButtFace and Nishant94 for the help!