Lowest value

[Guest]

If I got the following in my excelsheet:
A B
1 20 #VALUE!
2 35,77708764 38683,28157
3 50 31260

I want to sort out the lowest value in the B column and type the value of
the corresponding A column. In this case I want to get the function to find
B3 as min and type (=A3) in another cell.

Thanks

 

[Guest]

Bert i dont understand your question can you be more specific?

 

[Don Guillett]

try this where g is your a and h is your b

=INDEX(G:H,MATCH(MIN(H:H),H:H,0),1)

 

[Ron Rosenfeld]

If I got the following in my excelsheet:
A B
1 20 #VALUE!
2 35,77708764 38683,28157
3 50 31260

I want to sort out the lowest value in the B column and type the value of
the corresponding A column. In this case I want to get the function to find
B3 as min and type (=A3) in another cell.

Thanks

The **array** formula:

=INDEX(A1:A3,MATCH(MIN(IF(ISERR(B1:B3),"",B1:B3)),B1:B3,0))

To enter an array formula, after typing/pasting it into the cell, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

If some of the cells might be blank and should be ignored, then the array
formula:

=INDEX(A1:A10,MATCH(MIN(IF(ISERR(B1:B10)+
ISBLANK(B1:B10),"",B1:B10)),B1:B10,0))

should work.


--ron