+ Reply to Thread
Results 1 to 5 of 5

VLOOKUP-type formula required for partial text matche in multiple spreadsheets

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    VLOOKUP-type formula required for partial text matche in multiple spreadsheets

    Hi all

    I can't get my head round this one, and Googling solutions I'm beginning to wonder if it is possible without VBA...

    I have a bibliography in Excel with referencing information in each cell - for example: 'Drugs. 2012 Sep 10;72(13):1739-53.' There are a couple of thousand of these.

    On another sheet, I have a list of different journal titles in range A2:A239 including 'Drugs', and in column E I have an 'Impact Factor' number that corresponds with those eg. '4.633' (this does not have to be formatted as a number - it could just as easily be a text string).

    So on the bibliography sheet, I would like to create a formula that allows me to lookup the journal 'Drugs' (using a partial text match due to the other information in the cell) from the second sheet and return the Impact Factor value that corresponds with it to the bibliography sheet.

    Once I have a formula that can actually achieve this I will be able to work out the wildcard values to make sure I don't get erroneous returns on the lookups.

    Is this possible? Looking forward to your thoughts!

    Cheers,
    James

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: VLOOKUP-type formula required for partial text matche in multiple spreadsheets

    So for 'Drugs. 2012 Sep 10;72(13):1739-53.' you want to perform a lookup to find the Impact Factor from another sheet using Drugs as the lookup value? If so how will the formula know you are looking for the word Drugs?

    Will every entry on the Bibliography sheet be in the same format (Drugs. 2012 Sep 10;72(13):1739-53.)? If so I assume that you want to extract the text before the first full stop and then use this as your lookup value.

    If so the attached file will help you. Very basic to show you what to do, you just need to apply it to your work.
    Attached Files Attached Files
    Say thanks, click *

  3. #3
    Registered User
    Join Date
    07-09-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: VLOOKUP-type formula required for partial text matche in multiple spreadsheets

    Spectacular - that worked perfectly!

    Thanks very much for your help.

    Cheers,
    James

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: VLOOKUP-type formula required for partial text matche in multiple spreadsheets

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  5. #5
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: VLOOKUP-type formula required for partial text matche in multiple spreadsheets

    Always glad to help. Good luck with the rest of your work!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. vlookup partial text in a given text string
    By Jeetheone in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-29-2013, 09:02 AM
  2. Vlookup with only partial text match
    By betsy2128 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2013, 08:03 AM
  3. Vlookup Partial Text String
    By dreicer_Jarr in forum Excel General
    Replies: 7
    Last Post: 05-26-2011, 09:23 PM
  4. Partial text vlookup
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2011, 05:35 AM
  5. Help with type of formula required
    By chrisquinze in forum Excel General
    Replies: 4
    Last Post: 06-08-2009, 10:16 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1