+ Reply to Thread
Results 1 to 7 of 7

VLOOKUP function not working :/

  1. #1
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Question VLOOKUP function not working :/

    G'day All,

    I am having issues using a VLOOKUP function to produce a summary table as you will see in the spreadsheet below. I have a gut feeling its the fact that my lookup value is not situated in the first column of my lookup table array, rather midway through it. I hope this is not the case as I am not able to change the position or arrangement of data in the 'Data' sheet. Or alternatively maybe I have made some dumb mistake that I cant find.

    This is the spreadsheet;
    VLOOKUP.xlsx

    Alternatively, I would have liked to create the same table using pivot tables, as this is more user friendly in allowing the date to be selected, however for the life of me I cannot work out how to get pivot tables to display text rather and numbers, I dont want a count of the XX comments etc, I want to display them like the above spreadsheet is meant to. So if any genius' have ideas on that, let me know.

    Cheers in advance.

    Spicey

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: VLOOKUP function not working :/

    I have a gut feeling its the fact that my lookup value is not situated in the first column of my lookup table array, rather midway through it
    Go with the gut generally teh search criteria (L4 in your case) needs to be in the 1st column of the vlookup range. If it is not in the 1st column, and you cant change your layount, you need to go with the INDEX/MATCH combo...

    =INDEX(Data!$A$2:$S$44,MATCH('Prod Summary'!$L4,Data!$P$2:$P$25,0),MATCH('Prod Summary'!E$3,Data!$A$1:$S$1,0))
    copied down and across
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: VLOOKUP function not working :/

    Awesome,

    Do i need to do this as an array?

    I get one row of data fill in with #N/A for some reason
    INDEX NA.xlsx

    Is this just an issue with the function not working on occasion?

  4. #4
    Registered User
    Join Date
    03-26-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: VLOOKUP function not working :/

    double check cell D6 on 'Prod Summary' sheet... you've a space after day which is mucking up the index function

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: VLOOKUP function not working :/

    Good catch Q&A

    Also, you can do away with that helper column, and just use this...
    =INDEX(Data!$A$2:$S$44, MATCH($D$1&"/"&$C4&"/"&$D4,Data!$P$2:$P$25,0),MATCH(E$3,Data!$A$1:$S$1,0))

  6. #6
    Registered User
    Join Date
    05-26-2013
    Location
    Australia
    MS-Off Ver
    Excel 2016
    Posts
    79

    Re: VLOOKUP function not working :/

    Ahh awesome,

    Nice pick-up. Applied it to my actual master workbook, works a charm. Thanks for help!

    Spicey

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,118

    Re: VLOOKUP function not working :/

    Happy to help and thanks for the feedback

+ 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. [SOLVED] CountBLANK not working with Vlookup function
    By kj2607 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2014, 06:18 AM
  2. VLOOKUP Function not working
    By exposure31 in forum Excel General
    Replies: 2
    Last Post: 08-28-2012, 04:32 PM
  3. Vlookup function not working
    By rahulkrishnanr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-07-2012, 06:53 AM
  4. VLOOKUP function not working with table
    By conphill in forum Excel General
    Replies: 2
    Last Post: 02-28-2011, 12:40 PM
  5. Excel 2007 : VLookup Function Not Working
    By LAB2007 in forum Excel General
    Replies: 4
    Last Post: 06-30-2010, 03:23 PM

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