Okay, I have a bit of a complicated task...let's see if I can write it out
clearly. I have a report with many model numbers that I receive quarterly
from a third party. Our internal database has a reference to the model
numbers with corresponding part numbers associated with them. I would like to
edit the third party report to search for the model numbers on my list and
return the corresponding part number that goes with it. Tricky thing is,
they're not identical in identifying the part. For example:

Third Party Report:
Model # = ABC-15SP2/DA

Internal Database:
Column A Column B
Part # Model #
TDDCA504 ABC-14SP2, -15SP2, -16SP2

Some jokester thought they'd save time by putting the comma in on our
database and for the reader to assume ABC goes in front of it. The database
is huge and I would not attempt to put each model # in their own row and add
the ABC in front of all them (even by formula manipulation). In this example,
I'm looking for ABC-15SP2. Good news is that there will never be another
entry with '15SP2' in the list. So, is there a formula I could write that
would search for just this criteria and return the value in Column A (Part
#)? Perhaps a formula that would search for four or five consecutive
characters that match. Any help is much appreciated. Thanks!

Corey

Provided that the format for the model numbers from the third party
report is consistent, try the following formula that needs to be
confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A1:A10,MATCH(1,(ISNUMBER(SEARCH(LEFT(D1,3), B1:B10)))*(ISNUMBER(SEA
RCH(MID(D1,5,5),B1:B10))),0))

....where D1 contains the model number of interest, such as ABC-15SP2/DA.

Hope this helps!

In article ,
"Corey" wrote:

Okay, I have a bit of a complicated task...let's see if I can write it out
clearly. I have a report with many model numbers that I receive quarterly
from a third party. Our internal database has a reference to the model
numbers with corresponding part numbers associated with them. I would like to
edit the third party report to search for the model numbers on my list and
return the corresponding part number that goes with it. Tricky thing is,
they're not identical in identifying the part. For example:

Third Party Report:
Model # = ABC-15SP2/DA

Internal Database:
Column A Column B
Part # Model #
TDDCA504 ABC-14SP2, -15SP2, -16SP2

Some jokester thought they'd save time by putting the comma in on our
database and for the reader to assume ABC goes in front of it. The database
is huge and I would not attempt to put each model # in their own row and add
the ABC in front of all them (even by formula manipulation). In this example,
I'm looking for ABC-15SP2. Good news is that there will never be another
entry with '15SP2' in the list. So, is there a formula I could write that
would search for just this criteria and return the value in Column A (Part
#)? Perhaps a formula that would search for four or five consecutive
characters that match. Any help is much appreciated. Thanks!

Corey

Or maybe...

=INDEX(A1:A10,MATCH(1,(LEFT(B1:B10,3)=LEFT(D1,3))* (ISNUMBER(SEARCH(MID(D1
,5,5),B1:B10))),0))

....confirmed with CONTROL+SHIFT+ENTER.

In article ,
Domenic wrote:

Provided that the format for the model numbers from the third party
report is consistent, try the following formula that needs to be
confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=INDEX(A1:A10,MATCH(1,(ISNUMBER(SEARCH(LEFT(D1,3), B1:B10)))*(ISNUMBER(SEA
RCH(MID(D1,5,5),B1:B10))),0))

...where D1 contains the model number of interest, such as ABC-15SP2/DA.

Hope this helps!

In article ,
"Corey" wrote:

Okay, I have a bit of a complicated task...let's see if I can write it out
clearly. I have a report with many model numbers that I receive quarterly
from a third party. Our internal database has a reference to the model
numbers with corresponding part numbers associated with them. I would like
to
edit the third party report to search for the model numbers on my list and
return the corresponding part number that goes with it. Tricky thing is,
they're not identical in identifying the part. For example:

Third Party Report:
Model # = ABC-15SP2/DA

Internal Database:
Column A Column B
Part # Model #
TDDCA504 ABC-14SP2, -15SP2, -16SP2

Some jokester thought they'd save time by putting the comma in on our
database and for the reader to assume ABC goes in front of it. The database
is huge and I would not attempt to put each model # in their own row and
add
the ABC in front of all them (even by formula manipulation). In this
example,
I'm looking for ABC-15SP2. Good news is that there will never be another
entry with '15SP2' in the list. So, is there a formula I could write that
would search for just this criteria and return the value in Column A (Part
#)? Perhaps a formula that would search for four or five consecutive
characters that match. Any help is much appreciated. Thanks!

Corey