Need help finding the right function, please

[Ed from AZ]

The cells in Col A contain one of four values. The cells in Col D
contain dates. I want to return the highest date for each of the four
values. I'm having a difficult time understanding how the different
look up functions work to allow me to get =(MAX(all dates in Cold D
where Col A = "x")).

The Help files for both LOOKUP and VLOOKUP indicate the values in the
column to be searched must be sorted in ascending order. That can't
happen in this sheet. I'm stuck with them as they are.

A drop-kick in the right direction would be much appreciated!!

Ed

 

[Peo Sjoblom]

Try

=MAX(IF(A1:A50="x",D150))


entered with ctrl + shift & enter

format result as date or you'll get a serial number

--


Regards,


Peo Sjoblom

 

[Don Guillett]

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($a$1:$a$14="x",$d$1:$d$14))

 

[Ed from AZ]

Works like a champ! All these look-ups, and all I needed was IF?!

Is there a primer somewhere that gives real-world data examples
(unlike the Help files!!) and shows which function best returns the
data needed?

Ed

 

[Peo Sjoblom]

There are some, I might create one on my homepage though

http://www.cpearson.com/excel/ArrayFormulas.aspx

--


Regards,


Peo Sjoblom

Works like a champ! All these look-ups, and all I needed was IF?!

Is there a primer somewhere that gives real-world data examples
(unlike the Help files!!) and shows which function best returns the
data needed?

Ed