+ Reply to Thread
Results 1 to 9 of 9

VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

  1. #1
    Registered User
    Join Date
    11-17-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    7

    VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    Hello,

    Can anyone help me with this. VLOOKUP doesn't work on sheet "2016" but works fine for sheet "2015". Greatly appreciate the help!

    Here is the code:

    =IFERROR(IFERROR(VLOOKUP(D14,'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2016'!B1:C300,2,FALSE),VLOOKUP(D14,'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2015'!B1:C300,2,FALSE)), " ")

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    Well, can't review the data without a workbook. Maybe the sheet, 2016, doesn't exist, or maybe it has a trailing (or leading) space.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-17-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    7

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    Here is a screenshot, can't really attach the workbook, my company won't allow it, but any troubleshooting tips?

    the empty space in column C is actually bunch of company information I had to cut out for privacy.

    sheet.jpg

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    For troubleshooting purposes only, I'm looking at just the vlookup without the iferror.
    VLOOKUP(D14,'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2016'!B1:C300,2,FALSE)

    Do either of these work
    VLOOKUP(D14+0,'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2016'!B1:C300,2,FALSE)
    VLOOKUP(D14&"",'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2016'!B1:C300,2,FALSE)

  5. #5
    Registered User
    Join Date
    11-17-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    7

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    Thank you so much Jonmo1. This worked: VLOOKUP(D14&"",'\\SERVER\data\Company Standard\TIME SHEET\Jobs\[Jobs.xlsx]2016'!B1:C300,2,FALSE)

    But I do not understand what the issue is, all other sheets, 2015, 2014, 2013 work with my original code except for 2016?
    Last edited by A2G; 01-19-2016 at 10:57 AM.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    OK, that was just troubleshooting, Don't use that as solution.

    What that tells us is the values in column B of sheet 2016 are NOT really numbers.
    They're "Numbers Stored As Text"

    So if D14 is a real number, but the matching number in column B is a Number Stored As Text
    Then they DO NOT Match.

    You'll need to convert them to real numbers.
    Highlight Column B.
    On the Data Tab, click Text to Columns
    Chose Delmininated - Next
    UNcheck all options and click Finish.

    See if that helps.

  7. #7
    Registered User
    Join Date
    11-17-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    7

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    I see the difference between the sheets now Columb B for other sheets had =Value(.......), while 2016 did not.




    Quote Originally Posted by Jonmo1 View Post
    OK, that was just troubleshooting, Don't use that as solution.

    What that tells us is the values in column B of sheet 2016 are NOT really numbers.
    They're "Numbers Stored As Text"

    So if D14 is a real number, but the matching number in column B is a Number Stored As Text
    Then they DO NOT Match.

    You'll need to convert them to real numbers.
    Highlight Column B.
    On the Data Tab, click Text to Columns
    Chose Delmininated - Next
    UNcheck all options and click Finish.

    See if that helps.

  8. #8
    Registered User
    Join Date
    11-17-2015
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    7

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    Thank you so much Jonmo1, you always come through.

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP not working for sheet 1 called "2016" in "Jobs.xlsx"

    You're welcome

+ 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: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  3. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  4. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  5. Replies: 5
    Last Post: 06-26-2006, 09:23 PM
  6. Replies: 7
    Last Post: 05-13-2006, 05:02 PM
  7. [SOLVED] IF(VLOOKUP("MYDATA", MYNAME, 4) = 0, "TRUE", "FALSE")
    By Souris in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2005, 01:05 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