It looks like you have 3 ranges involved. You're returning data from cols A
& B based on numeric data in col C.

Let's do this:

1. Tell me the *exact* location of the names
2. Tell me the *exact* location of the numeric amounts
3. Tell me *exactly* where you want the results to appear

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
In regards to question 3, I am not doing something right to change the
references and the amounts.

In column C this is the formula to return the name:
=INDEX('Quality Measure Rankings'!A$64:A$74,MATCH(LARGE('Quality Measure
Rankings'!$C$64:$C$74-ROW('Quality Measure
Rankings'!C$64:C$74)/10^10,ROWS(A$64:A64)),'Quality Measure
Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!C$64:C$74)/10^10,0))

In column D this is the formula to return the amount (percentage):
=INDEX('Quality Measure Rankings'!B$64:B$74,MATCH(LARGE('Quality Measure
Rankings'!$C$64:$C$74-ROW('Quality Measure
Rankings'!D$64:D$74)/10^10,ROWS(B$64:B64)),'Quality Measure
Rankings'!$C$64:$C$74-ROW('Quality Measure Rankings'!D$64:D$74)/10^10,0))

Which of the formulas in column C are referring to the name and which are
referring to the amounts? Do some of the references in column C need to be
changed to absolute so they do not change when copied to column D with the
autofill handle?

Thank you for the clarification.

"T. Valko" wrote:

1.Does Rows(A$2:A2) refer to sheet 1 or sheet 2
or does it make any difference?

Rows(A$2:A2) refers to the first cell the formula is entered in. In this
example the first formula was entered in cell A2 hence: Rows(A$2:A2).
From a
purely technical standpoint, you don't need to include the sheet name in
that type of expression. For example, this expression:

ROW(Sheet1!B$2:B$9)

Can be written as:

ROW(B$2:B$9)

The sheet name is irrelavent to what the function is doing.. The function
is
returning the array {2;3;4;5;6;7;8;9}. The function returns the *exact*
same
array with or without the sheet name:

ROW(Sheet1!B$2:B$9) = {2;3;4;5;6;7;8;9}
ROW(B$2:B$9) = {2;3;4;5;6;7;8;9}

The sheet name is included to reduce confusion and let's the user know
that
the function is refering to data on the specified sheet.

2.What does 10^10 mean?

10 to the 10th power or 10*10*10*10*10*10*10*10*10*10 or =POWER(10,10) or
10,000,000,000

A typical lookup formula will *always* only find the first instance of
the
lookup_value and this poses a problem when there are multiple instances
of
the lookup_value and we need to find all of them. So, we need some method
of
making every lookup_value unique. Consider this:

B2 = 10
B3 = 10

Ok, we have a tie. Here's how we break that tie and make each value
unique.

B2:B3-ROW(B2:B3)/10^10

cell_value-(row_number/10^10)

B2 = 10-(2/10,000,000,000) = 9.9999999998
B3 = 10-(3/10,000,000,000) = 9.9999999997

Now we have all unique numbers to look for!

3.Will this array formula work if the column for
the accounts and the percentages are in
nonadjacent columns

Yes, but then you'd need to use 2 formulas. One for the name and one for
the
amount. You simply change the the indexed range.

With the names in column A and the amounts in column C:

For the names:

=INDEX(Sheet1!A$2:A$9.....

For the amounts:

=INDEX(Sheet1!C$2:C$9.....


--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
Impressive formula. Thank you. Now three questions:
1. Does Rows(A$2:A2) refer to sheet 1 or sheet 2 or does it make any
difference?

2. What does 10^10 mean?

3. Will this array formula work if the column for the accounts and the
percentages are in nonadjacent columns on sheet 1, such as the name of
the
account in column A and the percentage in column C? The percentages in
column
B are for January; the percentages in column C are for February. The
same
rank and sort is needed for the percentages in column C and subsequent
columns so that one can quickly see which account is at the top for
each
month. Or does the account name have to be adjacent to the percentage
for
the
array formula to function properly?

Thank you.

"T. Valko" wrote:

Try this...

Data in the range Sheet1A2:B9

Array entered** on Sheet2 in cell A2:

=INDEX(Sheet1!A$2:A$9,MATCH(LARGE(Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,ROWS(A$2:A2)),Sheet1!$B$2:$B$9-ROW(Sheet1!B$2:B$9)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy across to B2 then down to A9:B9. Accounts for possible ties.

--
Biff
Microsoft Excel MVP


"Basenji" wrote in message
...
On sheet 1 in column A is the name of the account; same sheet column
B
is
a
percentage.

Account Percentage
Mercy 80.4%
Henry 89.6%
Fred 0.00%
Chris 92.70%
Connie 92.00%
Alex 92.60%
Larry 89.80%
Ollie 0.00%

On a second sheet (a summary sheet) a formula is needed to sort and
rank
the
accounts so that the account with the highest percentage is at the
top
of
the
list, like this,

Chris 92.70%
Alex 92.60%
Connie 92.00%
etc

I have tried a combination of formulas but have been unsuccessful.
Thank you.