returning a text cell based on a number cell

I have a spreadsheet with several hundred rows of data in it. Column A
of each row is a text name, and Column B is a numerical value.

At the end of the worksheet, I'd like to display the name and numerical
value of the row with the lowest number in column B.

I can't simply sort the entire worksheet though, because I'd like it to
stay in alphabetical order.

Returning the lowest numerical value is easy. I can just take the
minimum of column B. What I'm having trouble with, is finding a way to
return the text value in column A based on that number in B.

Any ideas?

THanks in advance

Josh

--
Josh7777777
------------------------------------------------------------------------
Josh7777777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15996
View this thread: http://www.excelforum.com/showthread.php?threadid=274631

--
Regards
Frank Kabel
Frankfurt, Germany

"Josh7777777" <Josh77777...@excelforum-nospam.com> schrieb im
Newsbeitrag news:Josh77777...@excelforum-nospam.com...

Let A4:B11 house the following sample:

{"Name","Score";"dawn",23;"damon",21;"bob",25;"chris",22;"christine",21;"ian",32;"john",35}

In C4 enter: Rank.

In C5 enter & copy down:

=RANK(B4,$B$4:$B$10,1)+COUNTIF(B4:$B$4,B4)-1

D1:

=MIN(B5:B11)

D2: 1 (the value of N in Top N)

D3:

=MAX(IF(INDEX(B5:B11,MATCH(D2,C5:C11,0))=B5:B11,C5:C11))-D2

which must be confirmed with control+shift+enter instead of just with
enter.

Note that this formula calculates the number of the ties of the Min
value.

In D4 enter: Top List

In D5 enter & copy down:

=IF(ROW()-ROW(D$5)+1<=$D$2+$D$3,INDEX($A$5:$A$11,MATCH(ROW()-ROW($D$5)+1,$C$5:$C$11,0)),"")

The Top List will consist of:

{"damon";"christine"}

If you're on Excel 2003, do the following...

Change the rank formula in C4 to:

=RANK(B5,$B$5:$B$14,1)+COUNTIF($B$5:OFFSET(B5,0,0),B5)-1

Select A4:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Now, you don't have to adjust the ranges the formulas refer to and
copying these formulas down. All this will happen fully automatically.

--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4165