Return text value from Col A, based on Rank From Col B

[Future]

I have non-sorted positive and negative percentages in column B. In a cell I wish to return a text value from column A that corresponds with a specified ranking from Column B.

If the largest number in Column B is 6.5% in Row 32 (I don't know where the largest number is), then the value to return is ABC which is located in Column A Row 32. Would like to have the capability to revise the formula for the second highest or the third lowest, etc.

Many thanks for any ideas/suggestions.

 

[VoG]

Try

=INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0))

Change the 1 to 2, 3 etc.

 

[Future]

VoG, appreciate the quick reply and suggestion. Will spend some time working with this and then report back.....

Chris

 

[Future]

Works real good...changing the red number gives the different ranks..
Changing Large to Small gives the lower ranking...

Thanks again...

 

[theta]

Just a thought on this guys

When a number appears more than once e.g. 9 and 9, they are both given a shared rank

So how would you obtain the true rank in this instance (so that each item would have a true rank ID?)

 

[theta]

Current rank works like :

1 - 6
2 - 5
3 - 4
4 - 3
5 - 1
5 - 1

How could you instead get :

1 - 6
2 - 5
3 - 4
4 - 3
5 - 2
5 - 1

 

[khoala]

BUMP!

Digging up an old thread as I am too not sure what to do here...
Can anyone assist?

I also have data with with values that are the same, thus there are two values both are equal 3rd, How do i make them display in the 3rd and 4th position?

Current rank works like :

1 - 6
2 - 5
3 - 4
4 - 3
5 - 1
5 - 1

How could you instead get :

1 - 6
2 - 5
3 - 4
4 - 3
5 - 2
5 - 1

 

[Aladin Akyurek]

BUMP!

Digging up an old thread as I am too not sure what to do here...
Can anyone assist?

I also have data with with values that are the same, thus there are two values both are equal 3rd, How do i make them display in the 3rd and 4th position?

Does...

=RANK(A2,$A$2:$A$7,1)+COUNTIF($A$2:A2,A2)-1

achieve what is desired?