Formula to lookup last day of a month and return the value next to it (Max + If function?)

[Mr Retirement]

Hi Everyone -

This might be explained easiest by the sample table below. I'm looking for a formula that will take my inputs (Month & Year) and return the value from the last available date in that month/year from the table.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Month:
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Year:
[/TD]
[TD]2013
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NEEDED FORMULA:
[/TD]
[TD]1563
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data:
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Helper Column (if needed)
[/TD]
[TD]Date
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]03201301
[/TD]
[TD]3/1/2013
[/TD]
[TD]1500
[/TD]
[/TR]
[TR]
[TD]03201304
[/TD]
[TD]3/4/2013
[/TD]
[TD]1502
[/TD]
[/TR]
[TR]
[TD]03201315
[/TD]
[TD]3/15/2013
[/TD]
[TD]1505
[/TD]
[/TR]
[TR]
[TD]03201328
[/TD]
[TD]3/28/2013
[/TD]
[TD]1563
[/TD]
[/TR]
[TR]
[TD]04201303
[/TD]
[TD]4/3/2013
[/TD]
[TD]1600
[/TD]
[/TR]
[TR]
[TD]etc...
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated

Thanks,
Mr R.

 

[sheetspread]

Excel 2010
	A	B	C	D
1		Month:	3
2		Year:	2013
3		NEEDED FORMULA:	1563	1563
4
5
6	Data:
7	Helper Column (if needed)	Date	Value
8	3201301	3/1/2013	1500
9	3201304	3/4/2013	1502
10	3201315	3/15/2013	1505
11	3201328	3/28/2013	1563
12	4201303	4/3/2013	1600
Sheet2

Cell Formulas
Range	Formula
D3	=LOOKUP(2,1/(MONTH($B$8:$B$12)=C1),$C$8:$C$12)

=LOOKUP(2,1/IF(MONTH($B$8:$B$13)=C1,IF(YEAR($B$8:$B$13)=C2,1)),$C$8:$C$13) ctrl-shift-enter if you want different years

 

[sheetspread]

This is actually better:

Excel 2010
	B	C	D
1	Month:	3
2	Year:	2013
3	NEEDED FORMULA:	1563	1563
4
5
6
7	Date	Value
8	3/1/2013	1500
9	3/4/2013	1502
10	3/15/2013	1505
11	3/28/2013	1563
12	4/3/2013	1600
13	3/4/2014	1622
Sheet2

Cell Formulas
Range	Formula
D3	=LOOKUP(2,1/(TEXT($B$8:$B$13,"myyyy")=C1&C2),$C$8:$C$13)

 

[Mr Retirement]

Thank you! Works absolutely perfect - much appreciated.

Mr R.