Lookup closest to

[sgl]

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

 

[Jarek Kujawa]

say yr data is in A1:C7
product (i.e. Anemi Test) being in E1
1 Dec 11 in F1

would this:

=SUM(IF(E1=$A$1:$A$7,IF(F1-$B$1:$B$7=MIN(IF(F1>$B$1:$B$7,F1-$B$1:$B
$7,"")),$C$1:$C$7,),))

be helpful?
CTRL+SHIFT+ENTER this formula as this is an array-formula

 

[Jarek Kujawa]

or simpler:

=SUM(IF((E1=$A$1:$A$7)*(F1-$B$1:$B$7=MIN(IF(F1>$B$1:$B$7,F1-$B$1:$B
$7,""))),$C$1:$C$7,),)

also CTRL+SHIFT+ENTER this

 

[Ashish Mathur]

Hi,

You may use this array formula (Ctrl+Shift+Enter). D26 will hold Anemi Test
and E26 will hold 1 Jan 12

=SUMPRODUCT(--(E18:E24=MAX(IF(($D$18:$D$24=D26)*($E$18:$E$24<=E26),E18:E24))*(D1824=D26)),F18:F24)

I have feeling that I have complicated the solution. I am sure there is an
easier non-array formula solution available as well. Nevertheless this
solution will work for you.

By the way the first answer should be 575 and not 375.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[sgl]

Ashish thank you very much that worked very well
sgl

 

[Ashish Mathur]

You are welcome. Thank you for the feedback

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com