Lookup for most relevant (recent) date and return corresponding values

[robfoster2000]

Hi all.

This is my first post so please be gentle!

I have a list of dates in column A which range from a few months ago and into next year. For each date, there is a corresponding value in column B.

I need a formula which, based on today's date, finds the most recent date and returns the corresponding value from column B.

E.G.

Today's date = 14th Dec 2009

Dates Values

04th Dec 10
11th Dec 20
18th Dec 30

Need the formula to return 20 as this is the most relevant value.

Any help would be much appreciated!

Cheers,
Rob.

 

[Sandeep Warrier]

Hi & Welcome to the Board!!

If the dates are in ascending order then..

Excel Workbook
	A	B	C	D	E
1	Date	Value
2	04/12/2009	10		Today=	14/12/2009
3	11/12/2009	20		Value	20
4	18/12/2009	30
Sheet1

Excel 2003

Cell Formulas
Range	Formula
E3	=INDEX($B$2:$B$4,MATCH(E2,$A$2:$A$4,1))

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[robfoster2000]

Great - thanks Sandeep!

Rob.

 

[Sandeep Warrier]

Great - thanks Sandeep!

Rob.

You're welcome
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">