Excel 2007 : Look up of a word in a column of URLs and return the URL.

**ElonMusk** · 05-31-2012, 08:41 AM

Hello Guys
Thanks for all the great solutions to my problem. This is the first time I've not found the answer to a query. My basic problem is the following:

I have a document with two tabs. Tab 1 has a column of 300 URLs in the following fashion website.com/category/london-centre/london-centre and website.com/category/bristol-centre/bristol-centre.

The second tab has a column of new URLs off which I have trimmed off the root domain and category leaving just brighton-centre, manchester-centre.

In the second tab along the new URLs I want to return the old ones i.e I want Excel to look up machester-centre in tab one and return whatever cell contains the URL with macnhester-centre in it.

Thank you very much for your help.

**rvasquez** · 05-31-2012, 08:45 AM

Hello there,

Could you please provide a sample of you worksheet?

Thanks!

**abousetta** · 05-31-2012, 08:49 AM

If the first tab is Sheet1 and the websites are in column A, you can use something like:

Please Login or Register  to view this content.

**ElonMusk** · 05-31-2012, 09:00 AM

abousetta: Thank you very much I've tried VLookup for the past hour or so but couldn't make it work for this purpose.

rvasquez: Here's an attached working sheet. I would like the Old URLs(Sheet1) returned in the New URLs(Sheet2) tab in column C. Cheers

**abousetta** · 05-31-2012, 09:12 AM

Have a look at this:

Lookup table:

Please Login or Register  to view this content.

Results table (results):

Please Login or Register  to view this content.

Results table (formulas)

Please Login or Register  to view this content.

**rvasquez** · 05-31-2012, 09:19 AM

Nice job abousetta!

**abousetta** · 05-31-2012, 11:10 AM

Thanks @rvasquez

**ElonMusk** · 05-31-2012, 11:14 AM

Absolutely wonderful. That was mighty helpful. I'm delighted but also needs to work on advancing my Vlookup knowledge!

Kind regards

**abousetta** · 05-31-2012, 11:17 AM

Hi @ElonMusk,

Glad it all worked out. If you think about it, the formula is pretty simple. I just used a vlookup and instead of specific lookup value I used two wildcards sandwiching a trimmed down (e.g. no leading or trailing spaces) version of the lookup value. I probably learned this from another thread on this site.

Good luck.

abousetta

**ElonMusk** · 05-31-2012, 11:33 AM

I use vlookup a lot but never made use of trimming and wildcards. Will search for a few articles that can shed some spotlight on what those parameters are designed for and off course, how they can aid me in slicing and dicing huge sets of data efficiently.

**abousetta** · 05-31-2012, 12:00 PM

One of the biggest problems I find with lookup in general is that it looks for an exact value. One extra space here or there will throw it off. Therefore trim removes the leading the trailing spaces. Adding the wildcards in allow you to get a fuzzy approach to vlookup. If you really want to go 'fuzzy' check out Alan's code. Towards the end of the thread he gives the most recent fuzzy vlookup code for the UDF and even has added soundex to it. Good stuff if you really need to get creative.

Hope this helps.

abousetta

Excel 2007 : Look up of a word in a column of URLs and return the URL.

LinkBack

Thread Tools

Rate This Thread

Display

Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Re: Look up of a word in a column of URLs and return the URL.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions