=MIN(IF((A2:A20="SPY")*(B2:B20<TODAY()),B2:B20))


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
Thanks Again Bob.

I did not explain my problem very well.

The new table

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

For the formula in B2. I need it to look at A2 and find the cooresponding
Max or Min Date in the data table. I tried your formula but could not get
it
to return the correct values.

Here's the data table again:

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan


Thanks Again.



"Bob Phillips" wrote:

=MIN(IF(B2:B20<TODAY(),B2:B20))

HTHwhich is an array formula, it should be committed with
Ctrl-Shift-Enter,
not just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

and similar for MAX

=MAX(IF(B2:B20TODAY(),B2:B20))

--
---

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"carl" wrote in message
...
My data table is like so. My date format is YY/M

Today 7-Aug

Stock Date
SPY 7-Oct
SPY 7-Dec
SPY 10-Dec
OIH 7-Aug
OIH 9-Jan

Trying to create this table. Basically I am trying to find the date in
the
data table that is the closest and furthest away from today's date.

DateMax DateMin
SPY 10-Dec 7-Oct
OIH 9-Jan 7-Aug

Thank you in advance.