index and match closest
 

example data:
a1 b1 c1 d1 e1 f1
S-213A 10/19/2006 60. 10/20/2006 S-213A
S-213A 11/3/2006 66. 1/2/2007 S-200A
S-200A 11/4/2006 50. .
S-200A 12/3/2006 69. .

I need help with formula in f2 that would:
Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB, then
return value in column C. So in this case f2=60 and f3=69.
THANKS!

index and match closest
 

Try this array formula**:

=INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mona" wrote in message
...
example data:
a1 b1 c1 d1 e1 f1
S-213A 10/19/2006 60. 10/20/2006 S-213A
S-213A 11/3/2006 66. 1/2/2007 S-200A
S-200A 11/4/2006 50. .
S-200A 12/3/2006 69. .

I need help with formula in f2 that would:
Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB,
then
return value in column C. So in this case f2=60 and f3=69.
THANKS!

index and match closest
 

T. Valko-
Thank you so much. This is working. I have a change. Instead of "closest"
date I need the closest date but not greater than. I modified my example a
bit to explain.

"T. Valko" wrote:

Try this array formula**:

=INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mona" wrote in message
...
example data:
a1 b1 c1 d1 e1 f1
S-213A 8/19/2006 60. 10/20/2006 S-213A
S-213A 11/3/2006 66. 1/2/2007 S-200A
S-200A 11/4/2006 50. .
S-200A 11/3/2007 69. .

I need help with formula in f2 that would:
Find S-213A in columnA, then match CLOSEST date from D2 with ColumnB,
then
return value in column C. So in this case f2=60 and f3=50.
THANKS!

index and match closest
 

I need the closest date but not greater than.

If there is no date that meets the condition then the formula will return a
#N/A error.

Try this array formula** :

=INDEX(C$2:C$5,MATCH(1,--(B$2:B$5=MAX(IF((A$2:A$5=E2)*(B$2:B$5<D2),B$2:B$5) )),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mona" wrote in message
...
T. Valko-
Thank you so much. This is working. I have a change. Instead of
"closest"
date I need the closest date but not greater than. I modified my example
a
bit to explain.

"T. Valko" wrote:

Try this array formula**:

=INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mona" wrote in message
...
example data:
a1 b1 c1 d1 e1 f1
S-213A 8/19/2006 60. 10/20/2006 S-213A
S-213A 11/3/2006 66. 1/2/2007 S-200A
S-200A 11/4/2006 50. .
S-200A 11/3/2007 69. .

I need help with formula in f2 that would:
Find S-213A in columnA, then match CLOSEST date from D2 with
ColumnB,
then
return value in column C. So in this case f2=60 and f3=50.
THANKS!