How do I eliminate 0 when using MIN with IF?
I have a book with two sheets, Sheet1
and Sheet2
.
Sheet1
has a value (product code) to search for and an empty cell to fill:
A B
A100
A200
B150
C3AB
Sheet2
has a bunch of product and cost codes:
A B
A100 35
A100 14
A100 0
A200 10
A200 12
etc, etc, etc
I am using the following formula in Sheet1 B1 to find the matching MIN value from Sheet2:
=MIN(IF(Sheet2!$A$1:$A$5=A1,Sheet2!$B$1:$B$5))
How can I also exclude a return of 0? In the case of the A100 product, I would like to return the smallest non-zero match, which would be 14.
source to share
It would be faster if you add column C on Sheet 1 and fill it with
=IF(B1=0," ",B1)
Then your original formula, entered as a matrix, will work instead of referencing column C:
=MIN(IF(Sheet2!$A$1:$A$5=A1,Sheet2!$C$1:$C$5))
Can I comment on the speed of adding another column? It looks like the sheet has up to ~ 20,000 lines.
source to share
Array formula:
{=SMALL(IF(Sheet2!$B$1:$B$5<>0;IF(A3=Sheet2!$A$1:$A$5;Sheet2!$B$1:$B$5;"");"");1)}
Shift-Ctrl-Enter in the Formula to Paste window, the beveled brackets are inserted by Excel, not by the user.
MIN
behaved strangely, outputting 0
also for non-numeric values in an array. It was used instead SMALL
, indicating an error #NUM!
for non-existent links.
Sample table: http://www.bumpclub.ee/~jyri_r/Excel/MInimal_value_excluding_0.xls
source to share