Lookup Values

[Sam]

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

 

[Luke M]

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)
unless using XL 2007 and you are not using headers in Sheet2.

 

[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]

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.

 

[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