look up formula

  • Thread starter Thread starter Ali
  • Start date Start date
A

Ali

How can i create a formula that will give me the latest
date for a look up value example I will like to look up a
in the list and bring back the date 9/14/2003 for a as the
lookup value?

a 1/10/2003
a 1/14/2003
a 9/14/2003
b 8/14/2003
a 1/1/2003


Thanks in advance!
 
How about:

=IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$999,0)),"Missing",
MAX(IF(Sheet1!$A$1:$A$999=A1,Sheet1!$B$1:$B$999)))
(one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Assuming your dates are in cells B1 through B5, thy
entering the following formula in another cell:

=MAX(B1:B5)

Note: this will only work if your date cells are formatted
as such (i.e. date).

Nikos Y.
 
This was it Dave!

Thanks everyone for your help.
-----Original Message-----
How about:

=IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$999,0)),"Missing",
MAX(IF(Sheet1!$A$1:$A$999=A1,Sheet1!$B$1:$B$999)))
(one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)




--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Back
Top