index match & lowest postive number

[Dave R.]

Hi everybody. I am trying to do a formula which looks at data, and returns
the name (column A) of the person with the lowest non-negative number next
to their name.

Col A = Names
Cob B = Numbers, 1, 15,21,-3,-42,65,32 etc.

To get the lowest non-negative number, I have used

MIN(IF(B1:B10>0,B1:B10)) (array entered)

for the name of the person with the lowest number, I have used
INDEX(A1:A10, MATCH(MIN(B1:B10), B1:B10,0)) (not array entered)

when I try to dump formula 1 onto the index/match formula there, and array
enter it, it gives the #N/A errror.
INDEX(A1:A10,MATCH(MIN(IF(B1:B10>0,B1:B10)),0))

Thanks

 

[Kieran]

Dave R.

use

=INDEX(A1:A10,MATCH(MIN(IF(B1:B10>0,B1:B10,0)),B1:B10,0))

It was just missing one of the MATCH arguments.

 

[Earl Kiosterud]

Dave,

=INDEX(A2:A13,MATCH(MIN(IF(B2:B13>=0,B2:B13,"")),B2:B13,0))

It must be entered as an array formula (Ctrl-Shift-Enter) any time it's been
edited. It will include 0, unless you change the >=0 to >0.

 

[Dave R.]

Did it! Thanks!

 

[Aladin Akyurek]

Given

{"Name","Score";"A",1;"B",15;"C",21;"D",-3;"E",-42;"F",65;"G",32;"H",1}

in A2:B10, the standard solution...

=INDEX(A3:A10,MATCH(MIN(IF(B3:B10>0,B3:B10)),B3:B10,0))

which must be confirmed with control+shift+enter,

will give you "A" as result. But, What about H whose score is equal to A's?

If you also care about H, try the following:

In A1 enter: Lowest [ just a label ]

In B1 type:

=MIN(IF(B3:B10>0,B3:B10))

and confirm with control+shift+enter instead of just with enter.

In C1 type:

=MAX(IF(INDEX(B3:B10,MATCH(D1,D310,0))=B3:B10,D310))-D1

and confirm with control+shift+enter instead of just with enter.

In D1 enter: 1 [ which indicates Top N, with N set to 1 ]

In C2 enter: I-Rank [ just a label, abbrev of intermediate rank ]

In C3 enter & copy down:

=IF(B3>0,RANK(B3,$B$3:$B$10,1)+COUNTIF($B$3:B3,B3)-1,"")

In D2 enter: F-Rank [ just a label for final rank ]

In D3 enter & copy down:

=IF(N(C3),RANK(C3,$C$3:$C$10,1),"")

In E2 enter: Result [ just a label ]

In E3 enter & copy down:

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

The entire area of data and processing would now look like:

{"Lowest",1,1,1,"";
"Name","Score","I-Rank","F-Rank","Result";
"A",1,3,1,"A";
"B",15,5,3,"H";
"C",21,6,4,"";
"D",-3,"","","";
"E",-42,"","","";
"F",65,8,6,"";
"G",32,7,5,"";
"H",1,4,2,""}

where "" stands either for an empty cell or a blank cell of a formula
result.

 

[Dave R.]

Thanks Aladin, great input.