Top 5 values...corresponding cell text
 

In cells B2:B32 I have percentages. I have a formula in cells P2:P6 that
gives me the top 5 percentages in column B. I would like a formula in cells
O2:O6 that give me the text in column K that matches the percentage in column
P. My top 3 percentages are the same though so I need a formula to lookup
all three cases so I can't just use vlookup.

For example, I have 78% in cell P2. I would like O2 to have a formula that
looks for 78% in column B and gives me the corresponding text in column K.

Thanks.

Top 5 values...corresponding cell text
 

Put this in O2...

=INDEX(K$2:K$32,MATCH(P2,B$2:B$32,0))

and copy down to O6.

Rick


"Jambruins" wrote in message
...
In cells B2:B32 I have percentages. I have a formula in cells P2:P6 that
gives me the top 5 percentages in column B. I would like a formula in
cells
O2:O6 that give me the text in column K that matches the percentage in
column
P. My top 3 percentages are the same though so I need a formula to lookup
all three cases so I can't just use vlookup.

For example, I have 78% in cell P2. I would like O2 to have a formula
that
looks for 78% in column B and gives me the corresponding text in column K.

Thanks.

Top 5 values...corresponding cell text
 

that works but if I have two of the same percentages it only finds the first
one twice. How do I get it to skip the first one and find the second one?
Thanks.

"Rick Rothstein (MVP - VB)" wrote:

Put this in O2...

=INDEX(K$2:K$32,MATCH(P2,B$2:B$32,0))

and copy down to O6.

Rick


"Jambruins" wrote in message
...
In cells B2:B32 I have percentages. I have a formula in cells P2:P6 that
gives me the top 5 percentages in column B. I would like a formula in
cells
O2:O6 that give me the text in column K that matches the percentage in
column
P. My top 3 percentages are the same though so I need a formula to lookup
all three cases so I can't just use vlookup.

For example, I have 78% in cell P2. I would like O2 to have a formula
that
looks for 78% in column B and gives me the corresponding text in column K.

Thanks.

Top 5 values...corresponding cell text
 

Here's a link to a sample file I posted several days ago for a similar
question:

xTopN.xls 18kb

http://cjoint.com/?elxqEIRIRX

You should be able to adapt the technique to your needs.

I've used named ranges in the formulas. The formula in column H is an array
formula. Array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Jambruins" wrote in message
...
that works but if I have two of the same percentages it only finds the
first
one twice. How do I get it to skip the first one and find the second one?
Thanks.

"Rick Rothstein (MVP - VB)" wrote:

Put this in O2...

=INDEX(K$2:K$32,MATCH(P2,B$2:B$32,0))

and copy down to O6.

Rick


"Jambruins" wrote in message
...
In cells B2:B32 I have percentages. I have a formula in cells P2:P6
that
gives me the top 5 percentages in column B. I would like a formula in
cells
O2:O6 that give me the text in column K that matches the percentage in
column
P. My top 3 percentages are the same though so I need a formula to
lookup
all three cases so I can't just use vlookup.

For example, I have 78% in cell P2. I would like O2 to have a formula
that
looks for 78% in column B and gives me the corresponding text in column
K.

Thanks.