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

robfoster2000

New Member
Joined
Dec 14, 2009
Messages
8
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Sandeep Warrier

Well-known Member
Joined
Oct 31, 2008
Messages
2,677
Hi & Welcome to the Board!!

If the dates are in ascending order then..
Excel Workbook
ABCDE
1DateValue
204/12/200910Today=14/12/2009
311/12/200920Value20
418/12/200930
Sheet1
Excel 2003
Cell Formulas
RangeFormula
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">
 

Forum statistics

Threads
1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top