lookup latest date Excel 2000

  • Thread starter Thread starter Wanda Round
  • Start date Start date
W

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
 
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
 
Bob Phillips said:
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
Click to expand...

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
 
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)
 
Bob Phillips said:
Wanda,

This nor array formula works

=SUMPRODUCT(MAX((A1:A8="JDK")*(B1:B8)))
Click to expand...

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
 
Back
Top