Latest date with condition

[casey]

I have 3 columns, example:

Col A Col B Col C
Row 1 Name Category of Sale Date of Sale
Row 2 Jane 4 07/25/07
Row 3 Jane 5 10/17/07
Row 4 Jane 4 11/11/07
Row 5 Jane 5 06/30/07

I want to bring back the Name (Jane) and latest Date of an invididual's
latest sale from a specific category. (Information is not entered in date
order.)

I have used the array formula {=MAX(IF(A2:A4="Jane",C2:C4))} which gives me
the date of Jane's latest sale, nonspecific of which department. Now, how
can I bring back 11/11/07 for Jane's latest sale of Category 4?

 

[Don Guillett]

try

=INDEX(H4:H14,MATCH(MAX(IF(G4:G14="Jane",I4:I14)),I4:I14))

 

[casey]

Thanks, Don for your quick response. I may be missing something, but I
believe I need Category 4 ID'd somehow. I have hundreds of names and ten
different categories.

 

[casey]

I see now what your formula does. It will bring back the category itself
(4), or at least from how I interpolated your columns with mine. I've tried
different combos to bring the date itself but haven't succeeded.

 

[Don Guillett]

Sorry, I misread and was giving you the number 4 as a result when your
request was easier to get the DATE
You wanted to know the latest date for cat 4 and Jane.CSE and format as date
=MAX(IF((G4:G14="Jane")*(H4:H14=4),I4:I14))

 

[casey]

Perfect! Exactly what I needed.

Thanks!
Casey

Sorry, I misread and was giving you the number 4 as a result when your
request was easier to get the DATE
You wanted to know the latest date for cat 4 and Jane.CSE and format as date
=MAX(IF((G4:G14="Jane")*(H4:H14=4),I4:I14))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

Glad to help