+ Reply to Thread
Results 1 to 10 of 10

Coppying data from sheet1 to sheet2 using (VLOOKUP)

  1. #1
    Registered User
    Join Date
    08-29-2008
    Location
    US
    Posts
    6

    Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Hello,

    Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP?

    I had an interview where they asked me to copy data from sheet1 to sheet2.
    I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function.

    Please confirm if VLOOKUP can be used in this regard.

    Thanks in advance,
    Realty
    Last edited by Realty; 04-14-2009 at 10:44 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Did you read up on VLOOKUP in Excel Help files?

    They wanted you to lookup a key piece of information that was on both sheets, matching that piece of info from Sheet2 back on Sheet1 and VLOOKUP then will bring over the matching piece of data from the same row in a different column.

    It's very common. Press F1 in Excel and read up on it.

    Now, if you're looking to impress next time, use INDEX/MATCH instead which is faster and more robust.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    08-29-2008
    Location
    US
    Posts
    6

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Quote Originally Posted by JBeaucaire View Post
    Did you read up on VLOOKUP in Excel Help files?

    They wanted you to lookup a key piece of information that was on both sheets, matching that piece of info from Sheet2 back on Sheet1 and VLOOKUP then will bring over the matching piece of data from the same row in a different column.

    It's very common. Press F1 in Excel and read up on it.

    Now, if you're looking to impress next time, use INDEX/MATCH instead which is faster and more robust.

    Thanks a lot for your response,

    Yes I read the VLOOKUP Help files from excel and I did not see any answer to what I was looking for.

    There was no two sheets with data, it was only one sheet with content and they wanted me to copy it from sheet1(with data) to sheet2 (no data).

    Thanks again
    Realty


  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Well, if the quiz was that basic, your answer was better than theirs, then. People constructing quizzes need to clarify the expectation regarding the functions to use...

    ...unless they actually did that and you left that part out.

  5. #5
    Registered User
    Join Date
    08-29-2008
    Location
    US
    Posts
    6

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Hello,

    Thanks again.

    So in the end, is there any way to copy data from sheet1 to sheet2 using VLOOKUP function?

    Regards,
    Realty

  6. #6
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    One example would be:
    Please Login or Register  to view this content.
    Where A1 is on your current sheet (sheet 1)
    Sheet2!A1:C10 contains the table your are referencing
    3 is the column you are inquiring
    False tells Excel you want an exact match only for the value in A1.

    see attachment for example
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-29-2008
    Location
    US
    Posts
    6

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Thanks,

    It works for one specific cell.
    What if I want to copy a list of names(say, over 20000) from an entire column to the next sheet?

    You see, I never had to use VLOOKUP function before, at least to copy data from a sheet1 to sheet2.

    I use to use the function "=SHEET1!A1" and drag it way down to the bottom.
    But I have to admit that it takes long if what I had was over 30000 clients.

    Regards,
    Realty
    Last edited by Realty; 04-13-2009 at 06:23 PM. Reason: Modification

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    You need to let go of the VLOOKUP thing, my friend. Someone is putting one over on you. VLOOKUP is designed to search for ONE value in a column of values and return ONE value from another cell in the same row.

    If you need to copy 20,000 names from one sheet to another, you just copy them, quickly and simply. Your =Sheet1!A1 is perfectly fine, and it would take me all of 10 seconds to copy that formula to 30,000 other cells.

    If you're leaving out critical information about this "copy 20,000 names" process, then be clearer. Are you talking about 20,000 names out of 30,000? It's a subset of some kind? What is being used to flag which of the names should go into the new list? Is it some matching value in another column that signifies something in common?

    Once those values are identified, I can promise you that VLOOKUP would then be the LEAST-suited method of trying to pull multiple values, especially on a scale of 20,000.

    If you're looking for sheet functions to do this, I would suggest INDEX/MATCH coupled with a helper column, or a macro that creates your list on demand.

    attached is a sample sheet showing how a helper column on Sheet1 can watch your "search criteria" on the summary page and create an index that can then be used by the summary page to construct a list in realtime using the fastest function for the job...INDEX/MATCH.

    Try changing the yellow search cell and watch the chart reassert.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-29-2008
    Location
    US
    Posts
    6

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Quote Originally Posted by JBeaucaire View Post
    You need to let go of the VLOOKUP thing, my friend. Someone is putting one over on you. VLOOKUP is designed to search for ONE value in a column of values and return ONE value from another cell in the same row.

    If you need to copy 20,000 names from one sheet to another, you just copy them, quickly and simply. Your =Sheet1!A1 is perfectly fine, and it would take me all of 10 seconds to copy that formula to 30,000 other cells.

    If you're leaving out critical information about this "copy 20,000 names" process, then be clearer. Are you talking about 20,000 names out of 30,000? It's a subset of some kind? What is being used to flag which of the names should go into the new list? Is it some matching value in another column that signifies something in common?

    Once those values are identified, I can promise you that VLOOKUP would then be the LEAST-suited method of trying to pull multiple values, especially on a scale of 20,000.

    If you're looking for sheet functions to do this, I would suggest INDEX/MATCH coupled with a helper column, or a macro that creates your list on demand.

    attached is a sample sheet showing how a helper column on Sheet1 can watch your "search criteria" on the summary page and create an index that can then be used by the summary page to construct a list in realtime using the fastest function for the job...INDEX/MATCH.

    Try changing the yellow search cell and watch the chart reassert.

    Hello JBeaucaire,

    You are right about that.
    I just wanted an answer from someone who knows better than me on the subject so I can make the right decisions.

    Thanks a lot to all of you.

    Regards,
    Realty

  10. #10
    Registered User
    Join Date
    12-15-2012
    Location
    Washington D.C., USA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Coppying data from sheet1 to sheet2 using (VLOOKUP)

    Thank you very much mdbct. This VLOOKUP formula made life quite easy.
    Thanks to REALTY posting this question as well.
    Please Login or Register  to view this content.
    Quote Originally Posted by mdbct View Post
    One example would be:
    Please Login or Register  to view this content.
    Where A1 is on your current sheet (sheet 1)
    Sheet2!A1:C10 contains the table your are referencing
    3 is the column you are inquiring
    False tells Excel you want an exact match only for the value in A1.

    see attachment for example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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