+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP not working when referring to data in separate sheet

  1. #1
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    VLOOKUP not working when referring to data in separate sheet

    I have a simple VLOOKUP function that I want to use to call value information (£) from a separate tab depending on the ID used:

    Sheet1 has 3 columns:

    - Column A: ID
    - Column B: Name
    - Column C: Value

    Sheet 2 has the VLOOKUP: =VLOOKUP(A1,Sheet1!A1:A50,3,FALSE)

    However, '#REF!' comes up when I use the above formula.

    What am I missing?

    Thanks

  2. #2
    Registered User
    Join Date
    01-28-2014
    Location
    Brno
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: VLOOKUP not working when referring to data in separate sheet

    Hi,

    Adjust range in vlookup - =VLOOKUP(A1,Sheet1!A1:C50,3,FALSE)

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: VLOOKUP not working when referring to data in separate sheet

    I think pepe74287 nailed it for you. Your vlookup is looking down column A in sheet1 but the formula is saying once you find the matching value, move over to the third column.
    to get it to work you need to change the formula to ALSO include column C to capture what is in the third column.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    Re: VLOOKUP not working when referring to data in separate sheet

    Brilliant, what a silly mistake on my part! Thanks...

  5. #5
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: VLOOKUP not working when referring to data in separate sheet

    Hi, I must be tired. It took me ages to realise what was wrong with that
    Your array isn't including the other columns. So:-
    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: VLOOKUP not working when referring to data in separate sheet

    Edit...Already answered


    Damn...I did I mention I was tired

+ 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: 01-14-2014, 10:29 AM
  2. Replies: 1
    Last Post: 10-04-2013, 05:54 PM
  3. Hide Rows by referring to separate sheet
    By Oqualli in forum Excel General
    Replies: 2
    Last Post: 06-29-2011, 12:33 PM
  4. Replies: 0
    Last Post: 08-03-2010, 04:11 PM
  5. vlookup formula not working with data on separate sheet
    By akee in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-16-2006, 11:18 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