I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code. But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the hundreds
I can not hardcode all of the keywords into the formulas. Plus, the keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would need to
copy this formula down column "B" so it can perform the same process against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not get
them to work.

Any help and guidance would be very much appreciated.

Hi Morton,

It looks as you have forgotten something to add to your request which make
it difficult for us to understand. Please provide some sample data and what
you want to become.

Wkr,

JP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not
get
them to work.

Any help and guidance would be very much appreciated.

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Note that if a cell contains more than one keyword the formula will "find"
the keyword that appears furthest down in the keyword list in C2:C200. For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and return
the corresponding value from column D.

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not
get
them to work.

Any help and guidance would be very much appreciated.

Hi Biff,
I was able to use Ron's solution. You've helped me in the past and would
like to try your suggestion, but could not get it to work. Should the LOOKUP
be a VLOOKUP? And the "1E100", should that be the range in column A of
A2:A500?

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Thanks for your time.....

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Note that if a cell contains more than one keyword the formula will "find"
the keyword that appears furthest down in the keyword list in C2:C200. For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and return
the corresponding value from column D.

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the products
contain a different keyword. I have been able to isolate the keywords from
each of the product descriptions and assign a unique manufacturing code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not
get
them to work.

Any help and guidance would be very much appreciated.

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

A2 is the cell that contains the text string
C2:C200 is your list of keywords
D2:D200 are the codes that correspond to the keywords

Let's assume A2 contains the text string Super Delta V1 Rocket

...........C..........D
2......Sun........AA1
3......Delta......BC1
4......Moon....X1C

=LOOKUP(1E100,SEARCH(C$2:C$4,A2),D$2:D$4)

Returns BC1

It found the keyword "Delta" in the string in cell A2.


--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Hi Biff,
I was able to use Ron's solution. You've helped me in the past and would
like to try your suggestion, but could not get it to work. Should the
LOOKUP
be a VLOOKUP? And the "1E100", should that be the range in column A of
A2:A500?

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Thanks for your time.....

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Note that if a cell contains more than one keyword the formula will
"find"
the keyword that appears furthest down in the keyword list in C2:C200.
For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and
return
the corresponding value from column D.

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the
products
contain a different keyword. I have been able to isolate the keywords
from
each of the product descriptions and assign a unique manufacturing
code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to
search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would
need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not
get
them to work.

Any help and guidance would be very much appreciated.

Thank you very much.....your solution worked. Although I am familiar with
the LOOKUP function, I have never seen "1E100" used before. But, I just read
your explanation for its use on eggheadcafe and it made perfect sense.
Thanks again!

"T. Valko" wrote:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

A2 is the cell that contains the text string
C2:C200 is your list of keywords
D2:D200 are the codes that correspond to the keywords

Let's assume A2 contains the text string Super Delta V1 Rocket

...........C..........D
2......Sun........AA1
3......Delta......BC1
4......Moon....X1C

=LOOKUP(1E100,SEARCH(C$2:C$4,A2),D$2:D$4)

Returns BC1

It found the keyword "Delta" in the string in cell A2.


--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Hi Biff,
I was able to use Ron's solution. You've helped me in the past and would
like to try your suggestion, but could not get it to work. Should the
LOOKUP
be a VLOOKUP? And the "1E100", should that be the range in column A of
A2:A500?

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Thanks for your time.....

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Note that if a cell contains more than one keyword the formula will
"find"
the keyword that appears furthest down in the keyword list in C2:C200.
For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and
return
the corresponding value from column D.

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the
products
contain a different keyword. I have been able to isolate the keywords
from
each of the product descriptions and assign a unique manufacturing
code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to
search
cell A2 for the keyword from the range of C2:C200, and then assign the
associated manufacture code from the range of D2:D200. And, I would
need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can not
get
them to work.

Any help and guidance would be very much appreciated.

Good deal! Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Thank you very much.....your solution worked. Although I am familiar with
the LOOKUP function, I have never seen "1E100" used before. But, I just
read
your explanation for its use on eggheadcafe and it made perfect sense.
Thanks again!

"T. Valko" wrote:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

A2 is the cell that contains the text string
C2:C200 is your list of keywords
D2:D200 are the codes that correspond to the keywords

Let's assume A2 contains the text string Super Delta V1 Rocket

...........C..........D
2......Sun........AA1
3......Delta......BC1
4......Moon....X1C

=LOOKUP(1E100,SEARCH(C$2:C$4,A2),D$2:D$4)

Returns BC1

It found the keyword "Delta" in the string in cell A2.


--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Hi Biff,
I was able to use Ron's solution. You've helped me in the past and
would
like to try your suggestion, but could not get it to work. Should the
LOOKUP
be a VLOOKUP? And the "1E100", should that be the range in column A of
A2:A500?

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Thanks for your time.....

"T. Valko" wrote:

Try this:

=LOOKUP(1E100,SEARCH(C$2:C$200,A2),D$2:D$200)

Note that if a cell contains more than one keyword the formula will
"find"
the keyword that appears furthest down in the keyword list in C2:C200.
For
example:

A2 = Super Duper Delta v1 Galaxy

Keyword list =

C2 = Galaxy
C3 = Delta

A2 contains both keywords Delta and Galaxy. In the keyword list Delta
appears furthest down the list so the formula will "find" Delta and
return
the corresponding value from column D.

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I deal with many manufactures providing our products. Each of the
products
contain a different keyword. I have been able to isolate the
keywords
from
each of the product descriptions and assign a unique manufacturing
code.
But
here is the issue a can not solve.

In column "A" I have the list of product descriptions from each of
the
manufacturers
In column "B" I need to perform the search listed below
In column "C" I have the list of keywords; i.e. Delta, Galaxy, etc.
In column "D" I have the associated manufacture code to the keywords

Because the list of product descriptions and keywords number in the
hundreds
I can not hardcode all of the keywords into the formulas. Plus, the
keywords
can be anywhere within the text string. In cell B2 I would like to
search
cell A2 for the keyword from the range of C2:C200, and then assign
the
associated manufacture code from the range of D2:D200. And, I would
need
to
copy this formula down column "B" so it can perform the same process
against
the range of entries in A2:A500.

I've tried nested if, countif, vlookup, and array formulas, but can
not
get
them to work.

Any help and guidance would be very much appreciated.