+ Reply to Thread
Results 1 to 11 of 11

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

Hybrid View

  1. 05-31-2012, 08:41 AM #1
    ElonMusk
    ElonMusk is offline
    Registered User
    Join Date
    05-31-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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.
    Reply With QuoteRegister To Reply
  2. 05-31-2012, 08:45 AM #2
    rvasquez
    rvasquez is offline
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

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

    Hello there,

    Could you please provide a sample of you worksheet?

    Thanks!
    Reply With QuoteRegister To Reply
  3. 05-31-2012, 08:49 AM #3
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

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

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

    =VLOOKUP("*"&A1,Sheet1!A:A,1,0)
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  4. 05-31-2012, 09:00 AM #4
    ElonMusk
    ElonMusk is offline
    Registered User
    Join Date
    05-31-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 05-31-2012, 09:12 AM #5
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

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

    Have a look at this:

    Lookup table:
          ---------------------------------A---------------------------------
  1   Old URLs                                                           
  2   http://www.website.com/category/manchester-centre/manchester-centre
  3   http://www.website.com/category/bristol-centre/bristol-centre      
  4   http://www.website.com/category/jersey-centre/Jersey-centre        
  5   http://www.website.com/category/ashford-centre/ashford-centre      
  6   http://www.website.com/category/cambridge-centre/cambridge-centre  
  7   http://www.website.com/category/kent-centre/kent-centre            
  8   http://www.website.com/category/luton-centre/luton-centre
    Results table (results):
          --------A-------- -------B------- ---------------------------------C---------------------------------
  1   New URL URLs      301 Redirect To Old URLs                                                           
  2   luton-centre             >        http://www.website.com/category/luton-centre/luton-centre          
  3   woking-centre            >                                                                       #N/A
  4   manchester-centre        >        http://www.website.com/category/manchester-centre/manchester-centre
  5   maidenhead-centre        >                                                                       #N/A
  6   kent-centre              >        http://www.website.com/category/kent-centre/kent-centre            
  7   oxford-centre            >                                                                       #N/A
    Results table (formulas)
          --------A-------- -------B------- ------------------------C------------------------
  1   New URL URLs      301 Redirect To Old URLs                                         
  2   luton-centre             >        =VLOOKUP("*"&TRIM(A2)&"*",'Expired URLs'!A:A,1,0)
  3   woking-centre            >        =VLOOKUP("*"&TRIM(A3)&"*",'Expired URLs'!A:A,1,0)
  4   manchester-centre        >        =VLOOKUP("*"&TRIM(A4)&"*",'Expired URLs'!A:A,1,0)
  5   maidenhead-centre        >        =VLOOKUP("*"&TRIM(A5)&"*",'Expired URLs'!A:A,1,0)
  6   kent-centre              >        =VLOOKUP("*"&TRIM(A6)&"*",'Expired URLs'!A:A,1,0)
  7   oxford-centre            >        =VLOOKUP("*"&TRIM(A7)&"*",'Expired URLs'!A:A,1,0)
    Last edited by abousetta; 05-31-2012 at 09:15 AM.
    Reply With QuoteRegister To Reply
  6. 05-31-2012, 09:19 AM #6
    rvasquez
    rvasquez is offline
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

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

    Nice job abousetta!
    Reply With QuoteRegister To Reply
  7. 05-31-2012, 11:10 AM #7
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

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

    Thanks @rvasquez
    Reply With QuoteRegister To Reply
  8. 05-31-2012, 11:14 AM #8
    ElonMusk
    ElonMusk is offline
    Registered User
    Join Date
    05-31-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

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

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

    Kind regards
    Reply With QuoteRegister To Reply
  9. 05-31-2012, 11:17 AM #9
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

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

    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
    Reply With QuoteRegister To Reply
  10. 05-31-2012, 11:33 AM #10
    ElonMusk
    ElonMusk is offline
    Registered User
    Join Date
    05-31-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

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

    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.
    Reply With QuoteRegister To Reply
  11. 05-31-2012, 12:00 PM #11
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

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

    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
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1