Lookup closest to
 

I have the following table

Name Date Amount
Anemi Test 31-Dec-11 500
Apnea 1 31-Dec-11 300
NB 1 Panamax 31-Dec-13 275
NB 2 Supramax 15-Nov-11 300
Anemi Test 19 Nov 11 123
Anemi Test 30-Nov-11 200
Anemi Test 31-Dec-11 75

I also have a Starting Date which say in this first instance is 1 Jan 12.
I want to find and SUM the value for each item nearest to the starting date.
In the above the result for Anemi Test would be 375, the SUM for NB 2
Supramax would be 300 and so on. If I were to make the starting date 1 Dec 11
then the result for Anemi Test would be 200 and for NB 2 Supramax would again
be 300.

Thanks in advance/sgl

Lookup closest to
 

I think you need to define what defines a "closest match" value. When
you write "SUM the value for each item nearest to the starting date",
how many items might that be, and how "nearest" do you need to go.

You might be able to adapt the formulas at
http://www.cpearson.com/Excel/TablesAndLookups.aspx in the "Closest
Match" section near the bottom of the page.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 16 Mar 2009 06:05:04 -0700, sgl
wrote:

I have the following table

Name Date Amount
Anemi Test 31-Dec-11 500
Apnea 1 31-Dec-11 300
NB 1 Panamax 31-Dec-13 275
NB 2 Supramax 15-Nov-11 300
Anemi Test 19 Nov 11 123
Anemi Test 30-Nov-11 200
Anemi Test 31-Dec-11 75

I also have a Starting Date which say in this first instance is 1 Jan 12.
I want to find and SUM the value for each item nearest to the starting date.
In the above the result for Anemi Test would be 375, the SUM for NB 2
Supramax would be 300 and so on. If I were to make the starting date 1 Dec 11
then the result for Anemi Test would be 200 and for NB 2 Supramax would again
be 300.

Thanks in advance/sgl