How to get the name related to the price?

[billj]

Dear all,

I have a task to rank the stocks from the highest price to the lowest price. I've made a template and managed to get the ranking right. I used the LARGE() function.

But now the difficulty is to get the 'Stock name' related to the ranked price. I may do it with the MATCH() function. But the problem is there is an equal number 15%.

Can you have a look at the example attached here?

[benishiryo]

hi billj, try this array formula:
=INDEX($A$2:$A$11,SMALL(IF($C$2:$C$11=H6,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF(H$6:H6,H6)))

[billj]

hi billj, try this array formula:
=INDEX($A$2:$A$11,SMALL(IF($C$2:$C$11=H6,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF(H$6:H6,H6)))

Hi benishiryo, your formula works nicely thanks for your great idea and help.

[Tony Valko]

Hi benishiryo, your formula works nicely thanks for your great idea and help.

Hmmm...

Are you sure that formula works? I get these results with that formula:

Microsoft
Johnson & Johnson
IBM

I think these are the correct results:

Microsoft
Johnson & Johnson
Goldman Sachs

This array formula** entered in I6 and copied down:

=IF(H6="","",INDEX(A:A,SMALL(IF(B$2:B$11=H$2,IF(D$2:D$11=H$3,IF(E$2:E$11=H$1,IF(C$2:C$11=H6,ROW(C$2:C$11))))),COUNTIF(H$6:H6,H6))))

** 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.

[billj]

Hmmm...

Are you sure that formula works? I get these results with that formula:

Microsoft
Johnson & Johnson
IBM

I think these are the correct results:

Microsoft
Johnson & Johnson
Goldman Sachs

This array formula** entered in I6 and copied down:

=IF(H6="","",INDEX(A:A,SMALL(IF(B$2:B$11=H$2,IF(D$2:D$11=H$3,IF(E$2:E$11=H$1,IF(C$2:C$11=H6,ROW(C$2:C$11))))),COUNTIF(H$6:H6,H6))))

** 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.

Hi, Tony. Yes, I noticed that the formula needs to be adjusted. I did that and got the right answer. The principle is right though.

Thanks for help

[Tony Valko]

Good deal. Thanks for the feedback!