software

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

Hi

You probably need to add the optional 4th argument to Vlookup, of either
FALSE or 0.
This is necessary when the data is not sorted alphabetically.
C1: =VLOOKUP(A1,CATALOG,2,0)--
Regards

Roger Govierquot;micmedquot; gt; wrote in message
...
gt;I am reposting this to see if I can get another answer.
gt;
gt; I need a formula that will allow me to search a column (B1:B19989)
gt; that
gt; contains text with the data from a column (A1:A32417) that contains a
gt; list of
gt; part numbers and display a result(True/False) into another column (C).
gt; Column
gt; B's text has the part numbers mixed in with the text. I need to know
gt; which
gt; numbers from column A are found within the text of column B.
gt;
gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt;
gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt;
gt; CATALOG=The name of array B1:B19989
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))gt;0)*ROW(CATALOG))

Does that help?

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

XL2002, WinXP-Proquot;micmedquot; wrote:

gt; I am reposting this to see if I can get another answer.
gt;
gt; I need a formula that will allow me to search a column (B1:B19989) that
gt; contains text with the data from a column (A1:A32417) that contains a list of
gt; part numbers and display a result(True/False) into another column (C). Column
gt; B's text has the part numbers mixed in with the text. I need to know which
gt; numbers from column A are found within the text of column B.
gt;
gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt;
gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt;
gt; CATALOG=The name of array B1:B19989

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-Proquot;Ron Coderrequot; wrote:

gt; See if one of these works for you:
gt;
gt; This one returns TRUE/FALSE:
gt; C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1
gt;
gt; This one returns the Row Number of the first matched item in CATALOG:
gt; C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))gt;0)*ROW(CATALOG))
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;micmedquot; wrote:
gt;
gt; gt; I am reposting this to see if I can get another answer.
gt; gt;
gt; gt; I need a formula that will allow me to search a column (B1:B19989) that
gt; gt; contains text with the data from a column (A1:A32417) that contains a list of
gt; gt; part numbers and display a result(True/False) into another column (C). Column
gt; gt; B's text has the part numbers mixed in with the text. I need to know which
gt; gt; numbers from column A are found within the text of column B.
gt; gt;
gt; gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt; gt;
gt; gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt; gt;
gt; gt; CATALOG=The name of array B1:B19989

If I understand your posting correctly, you are saying that you have
something like this in column A:

1234
567
8914
23456

i.e. part numbers - you do not state if these are in order or if they
follow a particular format (eg 6 digit). In column B you have text
which contains the part number, something like:

1234 wheel
567 passenger door
8914 headlamp
23456 rear bumper

Again, you give us no examples of what this is like - are the part
numbers always to the left, or can they be embedded within the text?
Are they always separated from the text by at least one space?

If this is a fair description of your problem, then VLOOKUP won't work
on its own. But, in order to give you some further help you do need to
describe your data a bit more fully with examples.

PeteThanks 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?

quot;Ron Coderrequot; wrote:

gt; Actually, my second formula returns the SUM of the row numbers if there is
gt; more than one match. :\
gt;
gt; If there is the possibility of multiple matches, let us know.
gt;
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; See if one of these works for you:
gt; gt;
gt; gt; This one returns TRUE/FALSE:
gt; gt; C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1
gt; gt;
gt; gt; This one returns the Row Number of the first matched item in CATALOG:
gt; gt; C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))gt;0)*ROW(CATALOG))
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;micmedquot; wrote:
gt; gt;
gt; gt; gt; I am reposting this to see if I can get another answer.
gt; gt; gt;
gt; gt; gt; I need a formula that will allow me to search a column (B1:B19989) that
gt; gt; gt; contains text with the data from a column (A1:A32417) that contains a list of
gt; gt; gt; part numbers and display a result(True/False) into another column (C). Column
gt; gt; gt; B's text has the part numbers mixed in with the text. I need to know which
gt; gt; gt; numbers from column A are found within the text of column B.
gt; gt; gt;
gt; gt; gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt; gt; gt;
gt; gt; gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt; gt; gt;
gt; gt; gt; CATALOG=The name of array B1:B19989

Thanks for asking Pete. The part numbers do not follow any particular format.
They could be:
12345
34-5678
FL-975
3K-23G-85
WPL-1176

Some of the part numbers are embedded, some are to the left and all are
seperated by at least one space.

WPL-1176 • $39.99
6 Ft. FWC-9-9-6 . . . . . . . . . . . . . . . . . . . . . . . . . $44.9510 Ft.
FW6-FW6CM FWA-6F-4M FWA-6M-4F FW-6J-4P FW6-BLCM

quot;Petequot; wrote:

gt; If I understand your posting correctly, you are saying that you have
gt; something like this in column A:
gt;
gt; 1234
gt; 567
gt; 8914
gt; 23456
gt;
gt; i.e. part numbers - you do not state if these are in order or if they
gt; follow a particular format (eg 6 digit). In column B you have text
gt; which contains the part number, something like:
gt;
gt; 1234 wheel
gt; 567 passenger door
gt; 8914 headlamp
gt; 23456 rear bumper
gt;
gt; Again, you give us no examples of what this is like - are the part
gt; numbers always to the left, or can they be embedded within the text?
gt; Are they always separated from the text by at least one space?
gt;
gt; If this is a fair description of your problem, then VLOOKUP won't work
gt; on its own. But, in order to give you some further help you do need to
gt; describe your data a bit more fully with examples.
gt;
gt; Pete
gt;
gt;

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-Proquot;micmedquot; wrote:

gt; Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
gt; Ron's returns FALSE for all rows in column A. I tried both on cells that I
gt; knew did and did not exist in CATALOG. Any other ideas?
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Actually, my second formula returns the SUM of the row numbers if there is
gt; gt; more than one match. :\
gt; gt;
gt; gt; If there is the possibility of multiple matches, let us know.
gt; gt;
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; See if one of these works for you:
gt; gt; gt;
gt; gt; gt; This one returns TRUE/FALSE:
gt; gt; gt; C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1
gt; gt; gt;
gt; gt; gt; This one returns the Row Number of the first matched item in CATALOG:
gt; gt; gt; C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))gt;0)*ROW(CATALOG))
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;micmedquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am reposting this to see if I can get another answer.
gt; gt; gt; gt;
gt; gt; gt; gt; I need a formula that will allow me to search a column (B1:B19989) that
gt; gt; gt; gt; contains text with the data from a column (A1:A32417) that contains a list of
gt; gt; gt; gt; part numbers and display a result(True/False) into another column (C). Column
gt; gt; gt; gt; B's text has the part numbers mixed in with the text. I need to know which
gt; gt; gt; gt; numbers from column A are found within the text of column B.
gt; gt; gt; gt;
gt; gt; gt; gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt; gt; gt; gt;
gt; gt; gt; gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt; gt; gt; gt;
gt; gt; gt; gt; CATALOG=The name of array B1:B19989

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.

quot;Ron Coderrequot; wrote:

gt; I just tested my formulas with your posted data formats.
gt;
gt; Just to reiterate:
gt; You have a part number list in Col_A.
gt; You have a catalog list in Col_B of the same sheet.
gt; You want to know if each part number in Col_A exists on any line in the
gt; catalog.
gt;
gt; If that Is that correct, then both formulas in C1 and copied down worked fine.
gt;
gt; I suspect that something else is impacting your results. Perhaps the
gt; references need to be adjusted.
gt;
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;micmedquot; wrote:
gt;
gt; gt; Thanks Ron and Roger for responding. Rogers solution still produces #N/A and
gt; gt; Ron's returns FALSE for all rows in column A. I tried both on cells that I
gt; gt; knew did and did not exist in CATALOG. Any other ideas?
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Actually, my second formula returns the SUM of the row numbers if there is
gt; gt; gt; more than one match. :\
gt; gt; gt;
gt; gt; gt; If there is the possibility of multiple matches, let us know.
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; See if one of these works for you:
gt; gt; gt; gt;
gt; gt; gt; gt; This one returns TRUE/FALSE:
gt; gt; gt; gt; C1: =SUMPRODUCT(--ISNUMBER(SEARCH(A1,CATALOG)))=1
gt; gt; gt; gt;
gt; gt; gt; gt; This one returns the Row Number of the first matched item in CATALOG:
gt; gt; gt; gt; C1: =SUMPRODUCT((--ISNUMBER(SEARCH(A1,CATALOG))gt;0)*ROW(CATALOG))
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;micmedquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am reposting this to see if I can get another answer.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need a formula that will allow me to search a column (B1:B19989) that
gt; gt; gt; gt; gt; contains text with the data from a column (A1:A32417) that contains a list of
gt; gt; gt; gt; gt; part numbers and display a result(True/False) into another column (C). Column
gt; gt; gt; gt; gt; B's text has the part numbers mixed in with the text. I need to know which
gt; gt; gt; gt; gt; numbers from column A are found within the text of column B.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; This is what I have tried in C1: =VLOOKUP(A1,CATALOG,2)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; All I get is #N/A - I know that the value in A1 exists in CATALOG
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; CATALOG=The name of array B1:B19989

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)))lt;gt;0

HTH
Kostis Vezerides