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

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

Hi Luke,

Am actually looking to pull the ranking that I have posted earlier into the
sheet where I am going to populate the actuals everymonth.

Thanks,
Sam

"Luke M" wrote:

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

I'm afraid I'm confused. Which values are you using as the reference/lookup
(whihc sheet?), and which values are you wanting returned? The formula I
posted earlier used the Month and Percentage as the references to give you
the rank number.
--
Best Regards,

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


"Sam" wrote:

Hi Luke,

Am actually looking to pull the ranking that I have posted earlier into the
sheet where I am going to populate the actuals everymonth.

Thanks,
Sam

"Luke M" wrote:

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

I'm using the values in %(actual) as lookup. Lets say that the table I had
provided below is in sheet 1 and the actual data that I am populating is in
sheet 2. I want to pull up the rankings in sheet 2 from sheet 1, also keeping
in mind that we might has decimals after the values( eg:98.78%) in the actual
data.

Thanks,
Sam

"Luke M" wrote:

I'm afraid I'm confused. Which values are you using as the reference/lookup
(whihc sheet?), and which values are you wanting returned? The formula I
posted earlier used the Month and Percentage as the references to give you
the rank number.
--
Best Regards,

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


"Sam" wrote:

Hi Luke,

Am actually looking to pull the ranking that I have posted earlier into the
sheet where I am going to populate the actuals everymonth.

Thanks,
Sam

"Luke M" wrote:

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