+ 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
    6

    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,314

    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
    6

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

    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!

    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.

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

    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
    6

    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