Search for first instance of text & return the value

L

LisaChica

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!!
 
B

Biff

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
 
L

LisaChica

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!
 
L

LisaChica

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....
 
B

Biff

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.
Click to expand...

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

Biff
 
L

LisaChica

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

Dave Peterson

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,false))

to get "DP410W_30_orchid_resize.jpg" back.
 
B

Biff

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
 
L

LisaChica

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...
 
B

Biff

Ok........

A20 = lookup value = DP410W

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

returns: DP410W_27_wild_geranium_resize.jpg

Biff
 
L

LisaChica

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

Biff

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

Thanks for the feedback!

Biff
 
Top