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