Excel: Find the minimal value in a column

Question

An Excel table consists of two columns (e.g., A1:B5):

0 10
1 20
3 30
2 20
1 59

I need to get the minimal value in column B for which the corresponding value in column A is greater than zero. In the above example it should be 20.

I tried using various combinations of INDEX(), MIN(), IF(), ROW(), array formulas, etc. - but I just can't figure out how to do it. :-( Any help would be appreciated.

Answer 1

I think you have to make an extra column..

A     B     C     D
0     10    false 20
1     20    20
3     30    30
2     40    40
1     50    50

column C : =IF(A1>0;B1)

cell D1: =MIN(C1:C5)

Answer 2

You need to do it in 2 stages

• First use the MIN function to find the minimum
• Then take that answer and use the LOOKUP function to select the row and column that you need.

Answer 3

Check the "Minimum And Maximum Values In A Range" example in http://www.cpearson.com/Excel/excelF.htm (you can download the same as well from the same section)

HTH

Answer 4

This is not identical, but very similar: http://stackoverflow.com/questions/1876506/excel-vba-find-minimum-of-list-of-values

Answer 5

Grsm almost had it

if you enter the following formula in C1 as an array (Ctrl+Shift+End)

=MIN(IF(A1:A5>0,B1:B5))

That should do the trick.