Need help finding the right function, please

  • Thread starter Thread starter Ed from AZ
  • Start date Start date
E

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
 
Try

=MAX(IF(A1:A50="x",D1:D50))


entered with ctrl + shift & enter

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

--


Regards,


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

=MAX(IF($a$1:$a$14="x",$d$1:$d$14))
 
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
 
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
 
Back
Top