VLOOKUP - if column A contains text string then return new value to column B

ferg

New member
Joined
Oct 14, 2013
Messages
8
Reaction score
0
Points
0
Hi all, spent nearly a day on this one and was hoping someone could help me out.

I am currently doing some data management for a company and need to get a specific function working with Vlookup which should save a few days work.

I currently have all the file names in a single column e.g.

800GR_FOP.psd
800GR_TOP.psd

I need to be able to isolate the _FOP and _TOP part so that when it looks at my Vlookup table, it returns a value such as 'FOP Image' into a new column and 'TOP Image" into the same column (but horizontal to its original file name.

There are a few variations, so maybe an OR function will come in handy e.g. _TOP, _BOP - what I am having trouble with is isolating multiple isolated parts of strings in order to return the desired value.

Thanks
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
assuming data starts in A2, try this in B2 and drag down

=MID($A2,FIND("_",$A2),FIND(".",$A2)-FIND("_",$A2))
 

ferg

New member
Joined
Oct 14, 2013
Messages
8
Reaction score
0
Points
0
I tip my hat to you sir... Now I can Vlookup to return the desired value and presto! Thank you so much!
 

ferg

New member
Joined
Oct 14, 2013
Messages
8
Reaction score
0
Points
0
So I need help on the next step of this if that is all good :)

DATA (Column 1-3) Table (Column 4-5)

File IDTypeNameTypeName
800GR_top.psd_top?_topTOP Image
800GR_fop.psd_fop?_fopFOP Image
800GR_BOP.psd_BOP?_BOPBOP Image
800GR_top.psd_top?
800GR_BOP.psd_BOP?
800GR_Prawn.psd_Prawn?

I need a formula which identifies the type in data (now possible thanks to NoS) and return a value in the Name Column. The values are in the table I have put next to it... so if I pull the formula down, any type e.g such as _Top, populates the cell next to it with TOP Image and so forth

Any further help would be great :) Thanks,
Ferg
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Did you try the VLOOKUP you identified?

e.g.

=VLOOKUP(B2,$D$2:$E$4,2,FALSE) copied down

where B2 contains the first lookup value of _top and D2:E4 contains the lookup table.
 

ferg

New member
Joined
Oct 14, 2013
Messages
8
Reaction score
0
Points
0
You guys are awesome... really appreciate the help with this one! Thanks so much - learning as I go! Hopefully will be able to contribute in the future!

Cheers
 
Top