+ Reply to Thread
Results 1 to 9 of 9

look up on sheet 1 match data sheet 2 and fill in cell on first sheet

  1. #1
    Registered User
    Join Date
    07-25-2007
    Posts
    18

    look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    I have two spreadsheets.

    I want to I have a unique name list in column sheet 1 and an empty cell to the left of it.

    I want to match the unique name in column sheet 2 and copy cell to the left of this cell back to he empty cell sheet 1.

    Would save me doing this manually

    Much appreciated

    Robert

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,160

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    sounds like you need to use vlookup
    can you post a sample data

    in
    column sheet 1 and an empty cell to the left of it.
    which is sheet 1 column B - lets assume row 2
    I want to match the unique name in column sheet 2 and copy cell to the left of this cell back to he empty cell sheet 1.
    and lets assume the data is in column A and B - row 2 to 100
    on sheet1
    B2

    =vlookup(A2, sheet2!$A$2:$B$100, 2, false)
    then copy down the column in sheet1

    should pull all the info in column B from sheet2 that matches the data in sheet1 column A to the data in sheet2 column A and return info in column B

  3. #3
    Registered User
    Join Date
    07-25-2007
    Posts
    18

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    CPS full screen shot.jpg

    Sorry for delay in getting back to thread, I have attached (I think) a screen shot.

    bw Robert

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,160

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    a sample spreadsheet with what you want to do also included - would help

    not sure what you are trying to do from the image

  5. #5
    Registered User
    Join Date
    07-25-2007
    Posts
    18

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    Lt sheet - copy E175 - paste into 'find', goto Rt sheet with column H highlighted and click 'find next' , when row is highted take value from column C (3114) and paste back into Lt sheet column D (row175)

    then copy function down the Lt sheet.

    I have another 800 on left sheet to do !!!

    Cheers Robert

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,160

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    cant you use the vlookup i posted

    in Column D175 put
    =vlookup(E175, sheet2!C:C, 1, false)

  7. #7
    Registered User
    Join Date
    07-25-2007
    Posts
    18

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    Sorry I forgot to mention the Rt sheet is in a different workbook. If this makes things complicated I can copy the Rt sheet back to create sheet 2 in first workbook.

  8. #8
    Registered User
    Join Date
    07-25-2007
    Posts
    18

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    it came back with #N/A

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    7,160

    Re: look up on sheet 1 match data sheet 2 and fill in cell on first sheet

    i'm not really 100% on what we are exactly trying to do here - as only have half the picture - vlookup should work for you

    so if we cant see a sample set of spreadsheets

    lets have the exact detail

    The name that is unique is in LT sheet in column E
    you then want to look in an external workbook (which will need to be open)

    Lt sheet - copy E175 - paste into 'find', goto Rt sheet with column H highlighted and click 'find next'
    goto Rt sheet with column H highlighted and click 'find next' , when row is highlight take value from column C

    OK - so vlookup will not goto the left of column H to pick up C

    in D then we need to use index match

    =INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″ ))

    so in D put
    =index(filename_worksheet_range_column_C , ( Match ( worksheet range E, filename_worksheet_range_column_H, 0))

    if the file name has spaces
    so for
    filename_worksheet_range_column_C
    =INDEX('[Book 2.xlsx]Sheet1'!C:C,MATCH(E2,'[Book 2.xlsx]Sheet1'!$H$2:$H$1000,0))
    you need to change the values
    fo book , sheet, range

+ 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