Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup without repeats
Hi!
Below is a table of data that I have: Name1 5 Name2 10 Name3 15 Name4 20 Name5 5 Name6 5 Name7 25 Name8 30 Name9 40 Name10 50 I am then resorting the second column in descending order and then matching (using INDEX and MATCH) the name to the number. My problem arises where I have the same number (Name1, Name5, and Name6 all have a value of 5). I think I need to use OFFSET here, but I'm not sure exactly how. Thanks in advance! Brett |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup without repeats
It depends on exactly what you're doing.
Are you wanting to lookup a name for *any* value or are you looking up *all* the names based on the vales in descending order? For example, you want to know who had a value of 25, so you do a lookup on the value 25. Or, are you doing this: lookup name for high value 1 lookup name for high value 2 lookup name for high value 3 lookup name for high value 4 lookup name for high value 5 etc etc -- Biff Microsoft Excel MVP "Brett" wrote in message ... Hi! Below is a table of data that I have: Name1 5 Name2 10 Name3 15 Name4 20 Name5 5 Name6 5 Name7 25 Name8 30 Name9 40 Name10 50 I am then resorting the second column in descending order and then matching (using INDEX and MATCH) the name to the number. My problem arises where I have the same number (Name1, Name5, and Name6 all have a value of 5). I think I need to use OFFSET here, but I'm not sure exactly how. Thanks in advance! Brett |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup without repeats
On 21 Jan, 14:29, "T. Valko" wrote:
It depends on exactly what you're doing. Are you wanting to lookup a name for *any* value or are you looking up *all* the names based on the vales in descending order? For example, you want to know who had a value of 25, so you do a lookup on the value 25. Or, are you doing this: lookup name for high value 1 lookup name for high value 2 lookup name for high value 3 lookup name for high value 4 lookup name for high value 5 etc etc -- Biff Microsoft Excel MVP "Brett" wrote in message ... Hi! Below is a table of data that I have: Name1 5 Name2 10 Name3 15 Name4 20 Name5 5 Name6 5 Name7 25 Name8 30 Name9 40 Name10 50 I am then resorting the second column in descending order and then matching (using INDEX and MATCH) the name to the number. *My problem arises where I have the same number (Name1, Name5, and Name6 all have a value of 5). *I think I need to use OFFSET here, but I'm not sure exactly how. Thanks in advance! Brett- Hide quoted text - - Show quoted text - Hi Bill, I am doing your second example: Name for =Large(Names,1) Name for =Large(Names,2) etc... Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup without repeats
Here's one way...
Assume names are in the range A2:A11 (named Names) Numbers in the range B2:B11 (named Nums) D2:D11 = Nums in descending order Enter this array formula** in E2 and copy down to E11: =INDEX(Names,MATCH(LARGE(Nums-ROW(Nums)/10^10,ROWS(E$2:E2)),Nums-ROW(Nums)/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. -- Biff Microsoft Excel MVP "Brett" wrote in message ... On 21 Jan, 14:29, "T. Valko" wrote: It depends on exactly what you're doing. Are you wanting to lookup a name for *any* value or are you looking up *all* the names based on the vales in descending order? For example, you want to know who had a value of 25, so you do a lookup on the value 25. Or, are you doing this: lookup name for high value 1 lookup name for high value 2 lookup name for high value 3 lookup name for high value 4 lookup name for high value 5 etc etc -- Biff Microsoft Excel MVP "Brett" wrote in message ... Hi! Below is a table of data that I have: Name1 5 Name2 10 Name3 15 Name4 20 Name5 5 Name6 5 Name7 25 Name8 30 Name9 40 Name10 50 I am then resorting the second column in descending order and then matching (using INDEX and MATCH) the name to the number. My problem arises where I have the same number (Name1, Name5, and Name6 all have a value of 5). I think I need to use OFFSET here, but I'm not sure exactly how. Thanks in advance! Brett- Hide quoted text - - Show quoted text - Hi Bill, I am doing your second example: Name for =Large(Names,1) Name for =Large(Names,2) etc... Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy and paste VBA repeats and repeats | Excel Discussion (Misc queries) | |||
lookup values vertically in a list and return the repeats | Excel Worksheet Functions | |||
NO REPEATS | Charts and Charting in Excel | |||
No repeats of name in same colum | Excel Discussion (Misc queries) | |||
Count repeats | Excel Worksheet Functions |