Lookup or IF or Index not sure what to do

[americanpie3]

Hi everyone,

As you can see by the table below I have the names, averages and then the rank. I use the rank formula so that I can have numbers depending on average.

Rank Name Averages
7 Pat 0.00
6 Dan 2.00
7 Steve 0.00
3 Donald 5.00
7 Jim 0.00
7 Shelley 0.00
7 Manon 0.00
7 John 0.00
7 Bob 0.00
1 Tom 7.00
4 Carlos 4.00

What I would love to do in another cell is to do a search that find the 1 in the Rank column and the result is the name category. Please assume that Rank is the A column, Name is the B and Averages are C.

Thanks for your input.

 

[JackDanIce]

Try:

=VLOOKUP(1,$A$1:$B$11,2,0)

 

[americanpie3]

Giving me #REF !

 

[americanpie3]

Sorry, should've copy pasted instead of typing. Thanks!

 

[JackDanIce]

You're welcome

 

[Aladin Akyurek]

Since ranks can repeat, a VLOOKUP formula would be inadequate.

In E1 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$12,SMALL(IF($A$2:$A$12=1,ROW($B$2:$B$12)-ROW($B$2)+1),ROWS($E$1:E1))),"")