If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
Thread Tools | |
Display Modes | |
|
|