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

[ferg]

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]

assuming data starts in A2, try this in B2 and drag down

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

 

[ferg]

I tip my hat to you sir... Now I can Vlookup to return the desired value and presto! Thank you so much!

 

[ferg]

So I need help on the next step of this if that is all good

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

File ID	Type	Name		Type	Name
800GR_top.psd	_top	?		_top	TOP Image
800GR_fop.psd	_fop	?		_fop	FOP Image
800GR_BOP.psd	_BOP	?		_BOP	BOP 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]

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]

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