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.

	Month:	3
	Year:	2013
	NEEDED FORMULA:	1563
Data:
Helper Column (if needed)	Date	Value
03201301	3/1/2013	1500
03201304	3/4/2013	1502
03201315	3/15/2013	1505
03201328	3/28/2013	1563
04201303	4/3/2013	1600
etc...

<tbody>
</tbody>

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.