+ Reply to Thread
Results 1 to 12 of 12

Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

  1. 04-03-2013, 04:19 PM #1
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    I have 2 worksheets (Sheet 1 and Sheet 2). I need to manually enter a value in sheet 1 cell A1 based on values on sheet 2 cells D1 thru O1.
    If Sheet 1 cell A1 = Sheet 2 cell D1 then copy Sheet 2 Cell D2 to Sheet 1 Cell D17 and Sheet 2 cell D3 to Sheet 1 cell D18 and Sheet 2 cell D4 to Sheet 1 cell D19 and Sheet 2 cell D5 to Sheet 1 cell D25..... (Notice the gap in cell range from D19 to D25). Any assistance would be appreciated. Thank you in advance.

    Not sure if VLOOKUP or INDEX/MATCH is the right function. That's why I'm posting for help here.
    Last edited by leog1969; 04-03-2013 at 04:49 PM. Reason: Was advised to do so by other forum user
    Reply With QuoteRegister To Reply
  2. 04-03-2013, 04:33 PM #2
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Need Excel function help!!!

    Hi leog,

    First off, you should change your Title to something more appropriate...

    Second, have you looked into VLOOKUP or INDEX/MATCH?
    Reply With QuoteRegister To Reply
  3. 04-03-2013, 04:47 PM #3
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Need Excel function help!!!

    djapigo, how's the title now? Better?.........

    I've looked at VLOOKUP but the cell in sheet 1, as I indicated, will be changed manually. Therefore, I cannot put a formula in sheet 1 cell A1.
    Reply With QuoteRegister To Reply
  4. 04-03-2013, 04:54 PM #4
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    In D17:

    =IF(A1=Sheet2!D1,Sheet!D2,"")

    Use the same method for other cells changing as required.
    Say thanks, click *
    Reply With QuoteRegister To Reply
  5. 04-03-2013, 04:56 PM #5
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Hi leog,

    Title is much better, hate for a moderator come down on you...

    My mistake about the VLOOKUP comment, didn't see it in your first post...

    Can you post a sample workbook with results that you want to see?

    Also, how were you using the VLOOKUP and INDEX/MATCH?
    Reply With QuoteRegister To Reply
  6. 04-03-2013, 05:22 PM #6
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Harribone,
    Thank you!!!!!
    Worked AWESOME!!!!
    Reply With QuoteRegister To Reply
  7. 04-03-2013, 05:29 PM #7
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    WAIT!!!!!

    What if Sheet1 Cell A1 is a different value from Sheet2......
    If you look at my original post..... I have data on Sheet2 Cells D1 thru O1.

    D1 = home
    E1 = car
    F1 = boat

    so, if sheet1 cell a1 = car then sheet1 D17 will have a different value.
    Last edited by leog1969; 04-03-2013 at 05:31 PM.
    Reply With QuoteRegister To Reply
  8. 04-03-2013, 05:30 PM #8
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  9. 04-03-2013, 05:38 PM #9
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Here is my sample file.
    Notice sheet2 has different values in cells D1 thru O1.
    Based on those values, in sheet1, if C8 equals any value in sheet2 cells D1 thru O1, then I need to copy the appropriate rates to sheet1 in cells D17, D18, D19, D20, D21, D22 and D25.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 04-03-2013, 05:53 PM #10
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Hi leog,

    You should use HLOOKUP instead... check out my attachment... what's the deal with colum I and J?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 04-03-2013, 05:55 PM #11
    Harribone
    Harribone is offline
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    In D17:

    =IFERROR(HLOOKUP(C8,Sheet2!1:8,2,0),"")

    Use this for all other cells you need to show a value for. Change the 2 the row which holds the value you need (e.g changing to 8 will return the Business Value).
    Reply With QuoteRegister To Reply
  12. 04-03-2013, 05:58 PM #12
    leog1969
    leog1969 is offline
    Registered User
    Join Date
    04-03-2013
    Location
    McAllen, TX
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Create VLOOKUP or INDEX/MATCH to copy values from Sheet 2 to Sheet 1

    Thank you djapigo!!!!
    I really appreciate it!!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1