Look up values in one column based on values in another without repeating

Hello,

This looks like a very helpful forum and I tried looking for a couple
of hours for an existing post that addressed my specific question. No
luck, so here is my dilemma: Column B has mean ratings for 67 items.
Column C has the item numbers (1-67). So, for example:

3.2 12
3.1 2
2.9 67
3.1 22

On another sheet I would like to present the 10 highest values in one
column and the pertaining item number in another column. I’ve gotten
the ten largest item means using the LARGE function, that is no
problem. I get:

3.2
3.1
3.1
2.9

I’m stuck on retrieving the pertaining item number. I have used the
following function in the cell to the right of the top ten values
(changing the kth value in the LARGE statement accordingly) and it
almost works: =VLOOKUP(LARGE('Item-Level (Raw)
Data'!$B$86:$B$152,1),'Item-Level (Raw) Data'!$B$86:$C$152,2,FALSE).
The problem is I get the following:

3.2 12
3.1 2
3.1 2
2.9 67

This function fails to give me item number 22 for either of the two
values of 3.1 (the order is not important). The function does not
recognize that it has already produced item number 2 for a value of 3.1
and that it should find another one, namely item number 22. This is
what I don’t know what to tell Excel to do. It looks as if Excel says:
“what is the 3rd largest value? Oh, 3.1. What is the first value in the
second column that matches 3.1? Oh, 2, so put 2.” I would like it to
say: “…Oh, 2, but we’ve already called up 2, so what is the next value?
Oh, 22, put 22.”

I guess what I would like it to do is similar to sampling without
replacement. If the next largest item mean is the same as the previous,
to give me the next item number with that item mean.

Thank you very much in advance. I apologize for the length, but I hope
I hope the length paid off in its clarity of the problem.


---
Message posted from http://www.ExcelForum.com/