lookup without repeats

[Brett]

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

 

[T. Valko]

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

 

[Brett]

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











- Show quoted text -

Hi Bill,

I am doing your second example:
Name for =Large(Names,1)
Name for =Large(Names,2)
etc...

Thanks!

 

[T. Valko]

Here's one way...

Assume names are in the range A2:A11 (named Names)
Numbers in the range B2:B11 (named Nums)

D211 = 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

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











- Show quoted text -

Hi Bill,

I am doing your second example:
Name for =Large(Names,1)
Name for =Large(Names,2)
etc...

Thanks!