Rank with conditional array

I'm having some trouble with the Rank function in Excel 2007.
I have a set of over 5100 crop production records with the critical fields being CROP, YEAR, CLIENT_ID, and ACT_YIELD. I would like to rank (descending) ACT_YIELD of the individual CLIENT_IDs by CROP and YEAR. I tried {=RANK(D2,IF(AND(A$2:A$88=A2,B$2:B$88=B2),D$2:D$88))} (with C.S.E.), but get the #VALUE! error. I've checked that the formatting is consistent in the columns, but maybe I've overlooked something(?).
I'm attaching a dummy spreadsheet to demonstrate the issue. Any help with this problem would be greatly appreciated.
Bob

CropSample.xlsx

Thanks, Siva. As long as I first sort the data by CROP, YEAR, and ACT_YIELD, this approach works great! A clever approach.
Regards,
Bob

Originally Posted by boopathiraja

May be this

Formula:

Please Login or Register  to view this content.

Sorry, but I don't follow this. What are the "--" in the formula? The solution offered by Siva worked fine, but I wanted to be clear on other possible approaches, too.
Regards,
Bob