This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list of file
names that contain the product numbers as well as additional characters. I
need a formula that will search col O for the first instance of the text in
cell A2 and return that value.

The next formula will return all instances that contain the text found in A2.

Please help, I have a deadline of this evening for this and I've been
wracking my brain and discussion Groups for this. Thanks so much!!

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY right
for your situation but the basic technique is what you're looking for and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list of
file
names that contain the product numbers as well as additional characters.
I
need a formula that will search col O for the first instance of the text
in
cell A2 and return that value.

The next formula will return all instances that contain the text found in
A2.

Please help, I have a deadline of this evening for this and I've been
wracking my brain and discussion Groups for this. Thanks so much!!

Thank you very much! I'm goin to work with this and see if I can modify it
to work for me. Been a long time since I've done this, I'm pretty rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY right
for your situation but the basic technique is what you're looking for and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list of
file
names that contain the product numbers as well as additional characters.
I
need a formula that will search col O for the first instance of the text
in
cell A2 and return that value.

The next formula will return all instances that contain the text found in
A2.

Please help, I have a deadline of this evening for this and I've been
wracking my brain and discussion Groups for this. Thanks so much!!

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can modify
it
to work for me. Been a long time since I've done this, I'm pretty rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY
right
for your situation but the basic technique is what you're looking for and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text found
in
A2.

Please help, I have a deadline of this evening for this and I've been
wracking my brain and discussion Groups for this. Thanks so much!!

I'm afraid I'm not quite sure what to take out and what to change. I don't
need values from the column next to, so I should take out the parts of the
formula referring to that, but then I still need to search the text strings
in column O for ones that contain (but not limited to EXACT) the text string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can modify
it
to work for me. Been a long time since I've done this, I'm pretty rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY
right
for your situation but the basic technique is what you're looking for and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text found
in
A2.

Please help, I have a deadline of this evening for this and I've been
wracking my brain and discussion Groups for this. Thanks so much!!

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change. I
don't
need values from the column next to, so I should take out the parts of the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY
right
for your situation but the basic technique is what you're looking for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text
found
in
A2.

Please help, I have a deadline of this evening for this and I've
been
wracking my brain and discussion Groups for this. Thanks so much!!

Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file names
that would contain the reference cell's text. I'm trying to get the first
file name to come up that, for example, contains "D600" but isn't necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change. I
don't
need values from the column next to, so I should take out the parts of the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY
right
for your situation but the basic technique is what you're looking for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text
found
in
A2.

Please help, I have a deadline of this evening for this and I've
been
wracking my brain and discussion Groups for this. Thanks so much!!

How about something like:
=if(a1="","",vlookup(a1&"*",sheet2!a:z,15,false))

This will pick up the the first match in column A that starts with D600 (or
whatever's in A1).

or
If you put 480 in A1, you could use:
=if(a1="","",vlookup("*"&a1&"*",sheet2!a:z,15,fals e))

to get "DP410W_30_orchid_resize.jpg" back.


LisaChica wrote:

Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file names
that would contain the reference cell's text. I'm trying to get the first
file name to come up that, for example, contains "D600" but isn't necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change. I
don't
need values from the column next to, so I should take out the parts of the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be EXACTLY
right
for your situation but the basic technique is what you're looking for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text
found
in
A2.

Please help, I have a deadline of this evening for this and I've
been
wracking my brain and discussion Groups for this. Thanks so much!!










--

Dave Peterson

Wow, clear as mud, huh?

Yuck!

Ok here's what I see:

The values in column A have some letters and a 3 digit number. So, my guess
is that you're only interested in the 3 digit number.

So, the lookup value is:

A1 = 600

The file names also start with some letters and a 3 digit number. So, my
guess is that you want file names in the 600 series.

These are the "matches" as I "see 'em":

DP630W_0_White_resize.jpg
DP630W_27_WildGeranium_resize.jpg
DP630W_55_Peony_resize.jpg
DP630WPeony.jpg
DP630WWhite.jpg
DP630WWildGeranium.jpg

Is this correct?

Biff

"LisaChica" wrote in message
...
Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file names
that would contain the reference cell's text. I'm trying to get the first
file name to come up that, for example, contains "D600" but isn't
necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the
lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change. I
don't
need values from the column next to, so I should take out the parts of
the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the
value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be
EXACTLY
right
for your situation but the basic technique is what you're looking
for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a
list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of
the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text
found
in
A2.

Please help, I have a deadline of this evening for this and I've
been
wracking my brain and discussion Groups for this. Thanks so
much!!

Actually, its more along the lines of A20=DP410W so my cell would come back
with a value of DP410W_27_wild_geranium_resize.jpg

yeah this is a weird one...

i really appreciate the help...


"Biff" wrote:

Wow, clear as mud, huh?

Yuck!

Ok here's what I see:

The values in column A have some letters and a 3 digit number. So, my guess
is that you're only interested in the 3 digit number.

So, the lookup value is:

A1 = 600

The file names also start with some letters and a 3 digit number. So, my
guess is that you want file names in the 600 series.

These are the "matches" as I "see 'em":

DP630W_0_White_resize.jpg
DP630W_27_WildGeranium_resize.jpg
DP630W_55_Peony_resize.jpg
DP630WPeony.jpg
DP630WWhite.jpg
DP630WWildGeranium.jpg

Is this correct?

Biff

"LisaChica" wrote in message
...
Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file names
that would contain the reference cell's text. I'm trying to get the first
file name to come up that, for example, contains "D600" but isn't
necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the
lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change. I
don't
need values from the column next to, so I should take out the parts of
the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the
value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be
EXACTLY
right
for your situation but the basic technique is what you're looking
for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have a
list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance of
the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the text
found
in
A2.

Please help, I have a deadline of this evening for this and I've
been
wracking my brain and discussion Groups for this. Thanks so
much!!

Ok........

A20 = lookup value = DP410W

=INDEX(O2:O36,MATCH("*"&A20&"*",O2:O36,0))

returns: DP410W_27_wild_geranium_resize.jpg

Biff

"LisaChica" wrote in message
...
Actually, its more along the lines of A20=DP410W so my cell would come
back
with a value of DP410W_27_wild_geranium_resize.jpg

yeah this is a weird one...

i really appreciate the help...


"Biff" wrote:

Wow, clear as mud, huh?

Yuck!

Ok here's what I see:

The values in column A have some letters and a 3 digit number. So, my
guess
is that you're only interested in the 3 digit number.

So, the lookup value is:

A1 = 600

The file names also start with some letters and a 3 digit number. So, my
guess is that you want file names in the 600 series.

These are the "matches" as I "see 'em":

DP630W_0_White_resize.jpg
DP630W_27_WildGeranium_resize.jpg
DP630W_55_Peony_resize.jpg
DP630WPeony.jpg
DP630WWhite.jpg
DP630WWildGeranium.jpg

Is this correct?

Biff

"LisaChica" wrote in message
...
Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file
names
that would contain the reference cell's text. I'm trying to get the
first
file name to come up that, for example, contains "D600" but isn't
necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the
lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change.
I
don't
need values from the column next to, so I should take out the parts
of
the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the
text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the
value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm
pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be
EXACTLY
right
for your situation but the basic technique is what you're
looking
for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in
message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have
a
list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance
of
the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the
text
found
in
A2.

Please help, I have a deadline of this evening for this and
I've
been
wracking my brain and discussion Groups for this. Thanks so
much!!

OMG!!!! That worked! You are the bomb, thank you so much for making my life
a whole lot easier!


"Biff" wrote:

Ok........

A20 = lookup value = DP410W

=INDEX(O2:O36,MATCH("*"&A20&"*",O2:O36,0))

returns: DP410W_27_wild_geranium_resize.jpg

Biff

"LisaChica" wrote in message
...
Actually, its more along the lines of A20=DP410W so my cell would come
back
with a value of DP410W_27_wild_geranium_resize.jpg

yeah this is a weird one...

i really appreciate the help...


"Biff" wrote:

Wow, clear as mud, huh?

Yuck!

Ok here's what I see:

The values in column A have some letters and a 3 digit number. So, my
guess
is that you're only interested in the 3 digit number.

So, the lookup value is:

A1 = 600

The file names also start with some letters and a 3 digit number. So, my
guess is that you want file names in the 600 series.

These are the "matches" as I "see 'em":

DP630W_0_White_resize.jpg
DP630W_27_WildGeranium_resize.jpg
DP630W_55_Peony_resize.jpg
DP630WPeony.jpg
DP630WWhite.jpg
DP630WWildGeranium.jpg

Is this correct?

Biff

"LisaChica" wrote in message
...
Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file
names
that would contain the reference cell's text. I'm trying to get the
first
file name to come up that, for example, contains "D600" but isn't
necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the
lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to change.
I
don't
need values from the column next to, so I should take out the parts
of
the
formula referring to that, but then I still need to search the text
strings
in column O for ones that contain (but not limited to EXACT) the
text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the
value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in message
...
Thank you very much! I'm goin to work with this and see if I can
modify
it
to work for me. Been a long time since I've done this, I'm
pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be
EXACTLY
right
for your situation but the basic technique is what you're
looking
for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in
message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I have
a
list
of
file
names that contain the product numbers as well as additional
characters.
I
need a formula that will search col O for the first instance
of
the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the
text
found
in
A2.

Please help, I have a deadline of this evening for this and
I've
been
wracking my brain and discussion Groups for this. Thanks so
much!!

Turned out to be a lot easier than where I thought it was leading!

Thanks for the feedback!

Biff

"LisaChica" wrote in message
...
OMG!!!! That worked! You are the bomb, thank you so much for making my
life
a whole lot easier!


"Biff" wrote:

Ok........

A20 = lookup value = DP410W

=INDEX(O2:O36,MATCH("*"&A20&"*",O2:O36,0))

returns: DP410W_27_wild_geranium_resize.jpg

Biff

"LisaChica" wrote in message
...
Actually, its more along the lines of A20=DP410W so my cell would come
back
with a value of DP410W_27_wild_geranium_resize.jpg

yeah this is a weird one...

i really appreciate the help...


"Biff" wrote:

Wow, clear as mud, huh?

Yuck!

Ok here's what I see:

The values in column A have some letters and a 3 digit number. So, my
guess
is that you're only interested in the 3 digit number.

So, the lookup value is:

A1 = 600

The file names also start with some letters and a 3 digit number. So,
my
guess is that you want file names in the 600 series.

These are the "matches" as I "see 'em":

DP630W_0_White_resize.jpg
DP630W_27_WildGeranium_resize.jpg
DP630W_55_Peony_resize.jpg
DP630WPeony.jpg
DP630WWhite.jpg
DP630WWildGeranium.jpg

Is this correct?

Biff

"LisaChica" wrote in message
...
Ok, let's see:
A.................O
D600 DP405W_51_black_resize.jpg
D600W DP405W_55_peony_resize.jpg
D350 DP405Wazalea.jpg
D350W DP405Wblack.jpg
D360 DP405Wpeony.jpg
D360W DP410W_27_wild_geranium_resize.jpg
D480 DP410W_30_orchid_resize.jpg
D480W DP410W_46_azalea_resize.jpg
D965 DP410W_51_black_resize.jpg
DP630W DP410W_55_peony_resize.jpg
DP450W DP410Wazalea.jpg
DP620W DP410Wblack.jpg
DP640W DP410Worchid.jpg
DP135W DP410Wpeony.jpg
DP145W DP410Wwildgeranium.jpg
DP155W DP450W_0_White.jpg
DP305W DP450W_27_Wild_Geranium.jpg
DP310W DP450W_51_Black.jpg
DP405W DP450W_55_Peony.jpg
DP410W DP630W_0_White_resize.jpg
DP925W DP630W_27_WildGeranium_resize.jpg
DP935W DP630W_55_Peony_resize.jpg
DP770W DP630WPeony.jpg
dp955w DP630WWhite.jpg
dp965w DP630WWildGeranium.jpg
D735 DP770W_51_Black.jpg
D730 DP770W_53_French_Blue.jpg
D730W DP770W_58_Willow.jpg
D700 DP770W_61_French_Raspberry.jpg
D750 DP925W_19_SilverGrey.jpg
D750W DP925W_27_Wild_Geranium.jpg
D760 DP925W_46_Azalea.jpg
D970 DP925W_51_Black.jpg
D980 DP925W_55_Peony.jpg
D990 DP935W_19_SilverGrey.jpg

This is a very small chunk of the list and I purposely selected file
names
that would contain the reference cell's text. I'm trying to get the
first
file name to come up that, for example, contains "D600" but isn't
necessarily
an exact match.

Wow, clear as mud, huh?

"Biff" wrote:

I still need to search the text strings
in column O for ones that contain (but not limited to EXACT)
the text string from A2.

Post some examples. What is the lookup value and what values in the
lookup
table "match".

Biff

"LisaChica" wrote in message
...
I'm afraid I'm not quite sure what to take out and what to
change.
I
don't
need values from the column next to, so I should take out the
parts
of
the
formula referring to that, but then I still need to search the
text
strings
in column O for ones that contain (but not limited to EXACT) the
text
string
from A2.

I've gotten very close with: =VLOOKUP(A5,A2:O4977,15,TRUE)
but I think the formula is looking to a particular row for the
value....

"Biff" wrote:

If you get stuck just post back to this thread.

Biff

"LisaChica" wrote in
message
...
Thank you very much! I'm goin to work with this and see if I
can
modify
it
to work for me. Been a long time since I've done this, I'm
pretty
rusty.
Thanks so much for the help!

"Biff" wrote:

Sounds like this:

http://tinyurl.com/lrrpt

There's a sample file included in that thread. It may not be
EXACTLY
right
for your situation but the basic technique is what you're
looking
for
and
you can modify the formula to better suit your needs.

Biff

"LisaChica" wrote in
message
...
This is a two part question...
Here's what I have:
I have a list of product numbers in col. A. In col. O I
have
a
list
of
file
names that contain the product numbers as well as
additional
characters.
I
need a formula that will search col O for the first
instance
of
the
text
in
cell A2 and return that value.

The next formula will return all instances that contain the
text
found
in
A2.

Please help, I have a deadline of this evening for this and
I've
been
wracking my brain and discussion Groups for this. Thanks
so
much!!