So you're essentially wanting to look something up based on two values,
correct?
And, you're wanting to return the rank value from the other sheet?

=SUMPRODUCT(('Sheet2'!$A$2:$A$100=$A2)*('Sheet2'!$ C$2:$C$100=$C2)*('Sheet2'!$D$2:$D$100))

You can then copy this down and have it return the values you want. Couple
of notes: arrays must be of equal size, and don't callout entire column (D:D)
unless using XL 2007 and you are not using headers in Sheet2.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Sam" wrote:

Hello,

I have data in an excel sheet as per the below appended table. Now I'll be
updating the actual data every month in a different sheet; hence I require a
constant formula( whitout changing the reference cells), which I can drag to
the next line, which will in turn show the ranking for that particular actual
value achieved.

Month Parameter Actual Rank
Jan Service Quality 97.00% 3
Jan Service Quality 99.00% 4
Jan Service Quality 100.00% 5
Feb Service Quality 98.00% 3
Feb Service Quality 99.00% 4
Feb Service Quality 100.00% 5

I have tried using reference( concatanation of month & parameter), but it
doesn't work as actual varies. Please help.

Thanks,
Sam