lookup latest date Excel 2000

[Wanda Round]

I know I've seen this, but the brain is totally locked.
First column data is area inspected, 2nd column is date
of last inspection.

Need to know date of last inspection for a given area; that
is, date last inspection of JDK area, which is 12/18.

JDK 12/03/03
RUL 12/5/03
JDK 12/10/03
JDK 12/15/03
TWR 12/8/03
JDK 12/18/03
TWR 12/19/03
JDK 12/10/03

Many thanks.

Wanda

 

[Bob Phillips]

Wanda,

Try

=MAX(IF(A1:A8="JDK",B1:B8))
It's an array formula, so enter with Ctrl-Shift-Enter. Also, replace the
ranges to suit your data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I know I've seen this, but the brain is totally locked.
First column data is area inspected, 2nd column is date
of last inspection.

Need to know date of last inspection for a given area; that
is, date last inspection of JDK area, which is 12/18.

JDK 12/03/03
RUL 12/5/03
JDK 12/10/03
JDK 12/15/03
TWR 12/8/03
JDK 12/18/03
TWR 12/19/03
JDK 12/10/03

Many thanks.

Wanda

 

[Wanda Round]

Wanda,

Try

=MAX(IF(A1:A8="JDK",B1:B8))
It's an array formula, so enter with Ctrl-Shift-Enter. Also, replace the
ranges to suit your data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I know I've seen this, but the brain is totally locked.
First column data is area inspected, 2nd column is date
of last inspection.

Need to know date of last inspection for a given area; that
is, date last inspection of JDK area, which is 12/18.

JDK 12/03/03
RUL 12/5/03
JDK 12/10/03
JDK 12/15/03
TWR 12/8/03
JDK 12/18/03
TWR 12/19/03
JDK 12/10/03

Thanks, Bob. I suppose this is one of those things that can
only be done with an array entry. I certainly couldn't make it
work with any non-array entry formulas.

Wanda

 

[Bob Phillips]

Wanda,

This nor array formula works

=SUMPRODUCT(MAX((A1:A8="JDK")*(B1:B8)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Wanda Round]

Wanda,

This nor array formula works

=SUMPRODUCT(MAX((A1:A8="JDK")*(B1:B8)))

Bob,

Thanks for showing this way to do it. I feel silly--I tried almost
every variation on max and sumproduct, EXCEPT for where you put max.

Might as well blame it on getting older

Wanda

 

[Bob Phillips]

Might as well blame it on getting older I always do! <vbg>