+ Reply to Thread
Results 1 to 15 of 15

Vlookup from a vlookup

  1. #1
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Vlookup from a vlookup

    Hello,
    I'm not sure if this is even possible but let's see.
    I have an 11 digit reference number. Staff input the last five (as the first 6 as the same for everyone) when recording data in sheet 1 column C.
    Sheet 2 then contains the five digit number, the full 11 digits and also the name attached to that reference.
    Sheet 1 has a vlookup in column D which takes the five digits inputted and returns the full name attached from sheet 2.
    However this is currently failing as sheet in sheet 2, column B contains a trim formula to get the five digits from column C's 11. D contains the full name.

    I have attached the document to make it clearer.

    I need to pull the full name from sheet 2 into sheet 1 by using a trimmed version of the reference.
    I cannot use the values or copy and paste the trimmed values as the information in sheet 2 changes frequently.

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Try this:

    =VLOOKUP(C2,Sheet2!$B$2:$D$25,3,0)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Vlookup from a vlookup

    Tried it before, doesn't work. Comes back with #N/A

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    It works on your workbook here!

    Excel 2016 (Windows) 32 bit
    C
    D
    2
    27484 Frank Smith 7
    3
    28220 Frank Smith 8
    4
    28221 Frank Smith 9
    5
    28375 Frank Smith 10
    6
    28491 Frank Smith 11
    7
    28498 Frank Smith 12
    8
    28952 Frank Smith 13
    9
    29132 Frank Smith 14
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    D
    2
    =VLOOKUP(C2,Sheet2!$B$2:$D$25,3,0)
    Sheet: Sheet1

  5. #5
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Vlookup from a vlookup

    It isn't on mine.
    I'll try it on the real version

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Then attach your workbook with the formula in place so I can troubleshoot it.

  7. #7
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Vlookup from a vlookup

    I can't, due to data protection, hence i made a fake replica.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    So your replica is not of any use, since there is something fundamentally different about it.

    Try this:

    =VLOOKUP(VALUE(C2),Sheet2!$B$2:$D$25,3,0)

  9. #9
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Vlookup from a vlookup

    I cannot attach as it has information that cannot be share.
    The replica is exactly the same just a new document, data is in the same place.
    If the formula isn't working on the replica it won't work anyway.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Have you tried the second formula I suggested?

    Bryony - you need to understand that I am trying to help you. There WILL be something fundamentally different between your real data and the sample data (possibly in the set-up of the workbook itself) that means that the suggested formula works on the sample sheet but not on the real data. If you want help, then please work with us, not against us. I understand the reasons why you cannot share the real workbook.

  11. #11
    Registered User
    Join Date
    02-26-2014
    Location
    Cambridge, England
    MS-Off Ver
    Excel 2010
    Posts
    46

    Re: Vlookup from a vlookup

    I am trying it now.
    I am using 2013 not 2016.
    I do understand that I didn't say you were not.
    I am not "working against you", I merely said your first suggestion didn't work on my replica.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Just a quick query - do you have automatic calculation turned off?

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Quote Originally Posted by Bryony309 View Post
    I am using 2013 not 2016.
    Your profile says 2010 - please update it accordingly.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Attached is your replica workbook with the formula I first suggested working. If it is not working at your end, then something is different about your set-up (not the version - 2013 v. 2016 will make no difference here).
    Attached Files Attached Files

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,674

    Re: Vlookup from a vlookup

    Does my copy of the sample workbook work correctly at your end?

+ 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. Replies: 5
    Last Post: 12-07-2016, 09:18 AM
  2. Using vLookup based on results from a vLookup & returning an undetermined list
    By NormalityBan in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-14-2014, 05:02 AM
  3. Replies: 0
    Last Post: 12-26-2013, 01:12 PM
  4. Display Cell within VLookup range that excel thinks matches the VLookup Value
    By headachexcelperson in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 04:56 PM
  5. [SOLVED] Vlookup is not working and giving =vlookup(B2,$T$2:$U$135,2,false) this kind of values.
    By yogeshsharma1981 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2013, 04:08 PM
  6. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  7. Replies: 5
    Last Post: 07-29-2009, 07:53 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1