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!
|