+ Reply to Thread
Results 1 to 4 of 4

VLookup - partial text

  1. 08-01-2016, 05:53 AM #1
    Bryony309 is offline
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    VLookup - partial text

    Hello,
    I have two sheets. One a user input sheet, the second the data sheet.
    I would like the user to be able to enter a five digit student reference into cell A2 on the entry sheet. This would then "lookup" and populate B2 - I2 with different pieces of information relating to that student reference.
    The issue i am having is that on the data sheet the reference is actually 11 characters long with the final five making up this shortened user reference. (the first six are the same for all students)
    At present we tried a =right but the formula will not produce values from the vlookup up, i believe because it is a result of a formula.
    could anyone help with a formula that could work, perhaps with the first six written into it so it concatenates within the formula.
    Sorry for long post.
    Have attached a stripped version to try and help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-01-2016, 06:01 AM #2
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: VLookup - partial text

    On the student list sheet, change the formula in A2 to this and copy down:

    =VALUE(RIGHT(D2,5))

    Change the number formatting of that column not to show decimal places (not necessary but looks better).

    Change the VLOOKUP formula to this and copy down:

    =VLOOKUP(A2,'Student list'!$A$1:$Z$4,2,FALSE)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-01-2016, 06:06 AM #3
    Bryony309 is offline
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: VLookup - partial text

    AliGW you are a genius!
    Thanks so much!!
    :-D
    Reply With QuoteRegister To Reply
  4. 08-01-2016, 06:08 AM #4
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: VLookup - partial text

    You're welcome!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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)

Similar Threads

  1. [SOLVED] Vlookup with partial text VBA help
    By itty in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2015, 10:25 AM
  2. [SOLVED] VLOOKUP HELP! Search for partial text
    By sls1915 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-24-2015, 03:48 PM
  3. VLOOKUP Partial matches when searched text is longer than actual text
    By mockions in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2015, 11:16 PM
  4. [SOLVED] Vlookup partial text in cell
    By rupss01 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-10-2014, 12:11 AM
  5. Vlookup with only partial text match
    By betsy2128 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-18-2013, 08:03 AM
  6. Vlookup Partial Text String
    By dreicer_Jarr in forum Excel General
    Replies: 7
    Last Post: 05-26-2011, 09:23 PM
  7. Partial text vlookup
    By vijanand1279 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2011, 05:35 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1