returning a text cell based on a number cell

[Josh7777777]

I'm having trouble finding a way to perform a function in excel.
hopefully someone here can help me out.

I have a spreadsheet with several hundred rows of data in it. Column
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 numerica
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 t
stay in alphabetical order.

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

Any ideas?

THanks in advance

Jos

 

[Frank Kabel]

Hi
=INDEX(A1:A100,MATCH(MIN(B1:B100),B1:B100,0))

 

[Aladin Akyurek]

What you need is a Top N list, with N=1, based on Min value.

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 wit
enter.

Note that this formula calculates the number of the ties of the Mi
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 A411.
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 an
copying these formulas down. All this will happen fully automatically.