While it may be possible to delineate ALL of the rules regarding part
numbers, I suspect it may not be practical. Is what you already have
acceptable or do you need to trap the most common patterns or negative
indicators (no leading $, etc)? If you want the latter, give some thought to
what you must have and post back with the new rules. That way you can get one
comprehensive solution.

***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Thanks to Ron and Kostis. These worked with one caveat. Can the formula be
modified so that only exact matches are returned. For example A1 is 602, I
want it only to search for 602 and not $602.35 or CR602-12 etc.



"vezerid" wrote:

I think the solution is in a minor modification of Ron's formula. The
suggested formula

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

returns TRUE only if the code in A1 appears exactly once in CATALOG and
I suspect this is not the case. I suggest you modify to:

=SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))<0

HTH
Kostis Vezerides