Rick and Jim,

Thanks for those quick replies.

Actually, I posted this problem before. Here's an accurate, albeit
more involved example.

A grocery store is going through its inventory and is interested in
tracking only certain types of items.

For example, each type of fruit is assigned a code. When a
different vendor is used to obtain that type of fruit, the previous
vendor's code is retired by appending a letter or number to that code
(i.e., OA changes to OA1). The new vendor is assigned the original
code (i.e., OA). Over time, retired codes appear many times in the
inventory.

The goal is to look for duplicates and return a value of
"Duplicate" where that code will be ignored in other formulas.


A B
1 Code Name
2 A Apple
3 P Pear
4 OA Orange
5 OA1 Orange


Two named ranges exist:

Codes = $A$2:$A$5

Names = $B$2:$B$5


In C2:C5, the following formula is entered:

=IF(COUNTIF(Names,$B2)=1,"",IF(SUMPRODUCT(SMALL(IN DEX(Names=
$B2,)*INDEX(LEFT(Codes,LEN($A2))=$A2,)*INDEX(LEN(C odes),),
1))0,"","Duplicate"))

In cells C2 and C3, the formula correctly returns "".

However, in cell C4, the formula returns "Duplicate", because out
of {0,0,1,2}, 0 is the smallest number.

What I'm trying to do is get the formula to exclude the 0's and
return the smallest number (greater than zero) in that set.


- Ronald K.