Lowest value

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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
 
Back
Top