Kostis is correct...I wasn't aware that a part number could appear in the
CATALOG range more than once and I didn't allow for that in my formula.

This one allows for mutiple occurrences:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))0
copy down as far as needed.

(Nice catch, Kostis)
***********
Regards,
Ron

XL2002, WinXP-Pro


"micmed" wrote:

Does it matter that some of the cells in Col_A have a warning stating the
number is stored as text? I have tried changing all of Col_A to General but
that doasn't get rid of the warning.

"Ron Coderre" wrote:

I just tested my formulas with your posted data formats.

Just to reiterate:
You have a part number list in Col_A.
You have a catalog list in Col_B of the same sheet.
You want to know if each part number in Col_A exists on any line in the
catalog.

If that Is that correct, then both formulas in C1 and copied down worked fine.

I suspect that something else is impacting your results. Perhaps the
references need to be adjusted.


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

XL2002, WinXP-Pro


"micmed" wrote:

Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
Ron's returns FALSE for all rows in column A. I tried both on cells that I
knew did and did not exist in CATALOG. Any other ideas?

"Ron Coderre" wrote:

Actually, my second formula returns the SUM of the row numbers if there is
more than one match. :\

If there is the possibility of multiple matches, let us know.


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

XL2002, WinXP-Pro


"Ron Coderre" wrote:

See if one of these works for you:

This one returns TRUE/FALSE:
C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1

This one returns the Row Number of the first matched item in CATALOG:
C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))0)*ROW(CATALOG))

Does that help?

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

XL2002, WinXP-Pro


"micmed" wrote:

I am reposting this to see if I can get another answer.

I need a formula that will allow me to search a column (B1:B19989) that
contains text with the data from a column (A1:A32417) that contains a list of
part numbers and display a result(True/False) into another column (C). Column
B's text has the part numbers mixed in with the text. I need to know which
numbers from column A are found within the text of column B.

This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)

All I get is #N/A - I know that the value in A1 exists in CATALOG

CATALOG=The name of array B1:B19989