Use VLOOKUP to retrieve a value based on a word within a cell

**Nelzon** · 04-09-2016, 06:42 PM

Hello,

First of all, I thank in advance for any possible help.

I attach here an example of a file for my problem.

This file has 2 sheets, and I want to retrieve in Sheet A using VLOOKUP Function a value from Sheet B. For example, in cell A6 the correct value from sheet B it should display would be: http://example.com/news-usa and I would have to search and retrieve the value based on the word "USA" and not the whole sentence from Sheet B cell B5.

Does anyone know how to properly do this? Please have in mind that the original file I have to work has thousand lines so I should be able to find a method that could speed this up.

Thank you!

**AlKey** · 04-09-2016, 07:06 PM

Enter formula in cell A2 on Sheet A and copy down

Formula:

Please Login or Register  to view this content.

Or this

Formula:

Please Login or Register  to view this content.

v	A	B	C
1	URL New	URL	Title
2	http://example.com/home	http://oldsiteexample.com/home	Home
3	http://example.com/products	http://oldsiteexample.com/products	Products
4	http://example.com/news	http://oldsiteexample.com/newsflash	Newsflash from the World
5	http://example.com/news-usa	http://oldsiteexample.com/newsflash-usa	Newsflash from USA
6	http://example.com/news-germany	http://oldsiteexample.com/newsflash-germany	Newsflash from Germany
7	http://example.com/news-uk	http://oldsiteexample.com/newsflash-uk	Newsflash from UK