lookup without repeats

B

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

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
 
B

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

T. Valko

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


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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top