+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP on another sheet not working as expected

  1. #1
    Registered User
    Join Date
    07-07-2020
    Location
    Plymouth, England
    MS-Off Ver
    2016
    Posts
    11

    Question VLOOKUP on another sheet not working as expected

    Hi,

    I'm having an issue with a spreadsheet im attempting to put together, I'm receiving N/A errors as its failing to look up the value from another sheet.

    Basically, I have a 'Staff' sheet, with staff in my department and a second sheet of 'Results' with everyone's course results, exported from an online system. The trouble is, there is no way to filter out my specific department from the original list, so I've put together a list of my staff that I can then lookup in the original list.

    Using other formulas, i've determined that the helper columns are displaying the same value and are also not duplicated. But still nothing is retrieved. I'm also aware that one of the courses, has an odd character in it, but this doesnt change if it were removed.

    Attached is a sample of my sheet and also anonymised too.

    Thanks in advance, Paul.
    Attached Files Attached Files

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: VLOOKUP on another sheet not working as expected

    Hi

    Vlookup uses the first column of its search key range. Thus, the helper must be in column A

  3. #3
    Registered User
    Join Date
    07-07-2020
    Location
    Plymouth, England
    MS-Off Ver
    2016
    Posts
    11

    Re: VLOOKUP on another sheet not working as expected

    Would that be for all helper columns? Or just the one in the results spreadsheet?
    Last edited by AliGW; 09-30-2020 at 09:01 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: VLOOKUP on another sheet not working as expected

    You can use INDEX MATCH instead to avoid having to move any helper columns.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: VLOOKUP on another sheet not working as expected

    As Ali said

    Try this in D2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and this in E2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-07-2020
    Location
    Plymouth, England
    MS-Off Ver
    2016
    Posts
    11

    Re: VLOOKUP on another sheet not working as expected

    Thank you all for your suggestions, all worked but settled with the INDEX MATCH method to avoid reordering columns.

+ 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. worksheetfunction.vlookup not working as expected.
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-08-2020, 02:22 PM
  2. [SOLVED] IF Sheet Exists not working as expected
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2018, 03:28 PM
  3. [SOLVED] vlookup not working as expected
    By dmcgov in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-12-2016, 11:54 AM
  4. [SOLVED] VLOOKUP not working as expected.
    By mithandir1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2014, 06:44 PM
  5. Cell/sheet protection not working as expected
    By LAF in forum Excel General
    Replies: 3
    Last Post: 09-24-2010, 01:04 AM
  6. Cell/sheet protection not working as expected
    By LAF in forum Excel General
    Replies: 1
    Last Post: 06-08-2010, 01:09 PM
  7. vlookup/if formula not working as expected
    By Shocked in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2008, 10:06 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