Search & Find Top & Bottom Ranked Items

Hi.

I have a list of values that I have ranked.

Name Value Rank
Apple 100 3
Banana 40 4
Pear 150 2
Watermelon 425 1
Lemon 12 5

What I am trying to do is the following.

Best Rank (1) = output should be Watermelon
Worst Rank (5) = output should be Lemon

Can you please help me with the formula.

thank you in advance for your help

Saar

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. As you are still quite new here, I have done it for you this time.)

Please check the attached file for complete solution of your problem.

There are many ways to do this--

1st Method (With INDEX & MATCH (NON-ARRAY))

2nd Method (With ARRAY FORMULA)

change red part
=INDEX(C$6:C$10,MATCH(SMALL($E$6:$E$10,1),$E$6:$E$10,0)) to 3

to find 3rd smallest value

Small(range of numbers, which smallest value from the range you want to find) is the syntax in general and it changes to large in case of large function