Return Max with multiple criteria including date - array?

Hello all,
I'm sure that there's an easy answer to my problem but for the likes of me I can't figure it out. I'm crunching some data and could use a bit of help here.

I have 4 columns: EmployeeID, Classification, DateofHire, Salary
I want to create a formula that returns the highest salary of a person in a classification that was hired after the employee in question.

Here are the results I'd like to see for the entries below:

3100
0
3200
0
3200
0

EmployeeID,Classification,DateofHire,Salary
1,janitor,1/1/2002,3000
2,cook,2/1/2002,2500
3,maid,3/1/2002,2600
4,janitor,4/1/2002,3100
5,maid,5/1/2002,2800
6,maid,6/2/2002,3200


I was trying to create an array using the formula: =MAX(IF(B2:B7,=B2,If(C2:C7>C2),D2:D7))


If my explanation doesn't make sense please let me know and I can try again.

Well, you've actually got a comma and a closed bracket in the wrong place there. Try it like this:

=MAX(IF((B2:B7=B2)*(C2:C7>C2),D2:D7))

Use CSE to commit the formula.

Note: the * is equivalent to AND for arrays, so you can join multiple conditions in that way.

Hope this helps.

Pete

Or try this ...

=AGGREGATE(14,6,D2:D7/(B2:B7=B2)/(C2:C7>C2),1)

Normal enter.

Thanks Pete!!!

This looks to be on the right track. Any ideas on how to lock down the ranges? It looks like it's moving up by 1 every time I drag it down . I tried using the $ to lock down the formula to a specific cell but it kills the process.

This is important because there's no direct correlation to when people were hired and what classification they're in.

Thanks,
Andy

Phuocam - Works like a champ from what I can see right now because I know how to lock in the range with this method. I need to do a random spot check of the 15,000 employee entries I have, but on the surface it looks great!

1 question though, the last option selected, the ",1"; what does that indicate?

Thanks,
Andy

You should be able to do this:

=MAX(IF((B$2:B$7=B2)*(C$2:C$7>C2),D$2:D$7))

if you want to copy the formula down. Again, use CSE to commit.

Hope this helps.

Pete