# Thread: Return Most Recent Date Entered

1. red 4/6/2009
red 3/7/2009
red 3/7/2009
red 5/5/2009
red 4/6/2009
I need a formula, to return the most recent date entered for each color. The list below are in columns B and C, I need the range to cover the entire column as it continuously gets longer as I enter new colors (which are Locations)

red 4/6/2009
red 5/5/2009
green 3/7/2009
green 3/7/2009
green 4/6/2009
green 4/6/2009
blue-5 3/7/2009
blue-5 3/7/2009
blue-5 5/5/2009
blue-5 4/6/2009
black 4/6/2009
black 4/6/2009
black 4/6/2009
black 5/5/2009
black 3/7/2009
black 3/7/2009
black 5/5/2009

2. Let's say you enter "red" (without the quotes) in F2, and the other color names below it.
In G2, enter the following array formula (confirm with Ctrl+Shift+Enter):

=MAX(IF(\$B\$2:\$B\$65536=F2,\$C\$2:\$C\$65536))

Fill down as far as needed.

See the attached sample workbook.

3. [quote name='HansV' post='774549' date='10-May-2009 22:59']Let's say you enter "red" (without the quotes) in F2, and the other color names below it.
In G2, enter the following array formula (confirm with Ctrl+Shift+Enter):

=MAX(IF(\$B\$2:\$B\$65536=F2,\$C\$2:\$C\$65536))

Fill down as far as needed.

See the attached sample workbook.[/quote]

Array formula will affect the performance if you have a long list going into
ten of thousands.

Using Han's sample,

here a shorter version, but this is still an array formula. ( confirm with Ctrl, Shift and Enter )

=MAX((B2:B10000=F2)*(C2:C10000))

another non array formula but you can't use whole column

=LOOKUP(2,1/(\$B\$2:\$B\$10000=\$F2),\$C\$2:\$C\$10000)

HTH

4. Thanks, I believe these formulas will solve my problem.

5. Using this Array formula:
{=MAX(IF(Tracker!\$D\$11:Tracker!\$D\$65488=D15,Tracke r!\$P\$11:Tracker!\$P\$65488))}

If P11 to P65488 is blank, I get a date 1/0/1900. Instead of the date, how can I get NONE to be displayed in the cell?

I tried:
{=MAX(IF(Tracker!\$D\$11:Tracker!\$D\$65488=D15,Tracke r!\$P\$11:Tracker!\$P\$65488,"NONE"))}

but no luck, the date still appears???

6. Try

=IF(MAX(IF(Tracker!\$D\$11:Tracker!\$D\$65488=D15,Trac ker!\$P\$11:Tracker!\$P\$65488))=0,"None",MAX(IF(Track er!\$D\$11:Tracker!\$D\$65488=D15,Tracker!\$P\$11:Tracke r!\$P\$65488)))

as an array formula, of course. It may be slow to recalculate, though.

7. No that's perfect and the speed is hardly noticeable.

Thanks

8. [quote name='stans' post='798132' date='15-Oct-2009 15:34']... and the speed is hardly noticeable.[/quote]

Now, that's what I call an ambiguous phrase!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•