Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts

    Excel 2007 Match and Move question

    I have 2 spreadsheets in my D:\ partition at: Data\My_Data\Desktop
    One, called Choices has stock symbols in col. C, named Symbol
    The second, called Scores, has stock symbols in col. A, also named Symbol; it also has a "rating"
    for each stock symbol in col. C, named Rating

    I want to match the stock symbol in Scores with the same one in Choices, and copy the applicable
    rating to col. U, named Dars, in Choices

    Thanks for your help,
    Dick

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand the question, this would seem to work:
    =INDEX('D:\Data\My_Data\Desktop\Scores.xlsx'!Ratin g,MATCH(Symbol,'D:\Data\My_Data\Desktop\Scores.xls x'!Symbol,0))

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Steve:
    Thanks. I get a #Name error when I place that formula in col. U of my Choices spreadsheet.
    Dick

    PS,
    I think I was supposed to remove the space in "Ratin g" and in "xls x"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The spaces seem to come in the parsing by the program. My formula assumes the path you indicate, the file names you indicate (change the XLSX to XLS is your files are pre-2007). If the external file is not scores rename that as well.

    I presumed from your text that you had the named formulas "Symbol" to designate a range of cells in the files and the another named "Symbol". If that is not the case [and I suspect from the #name error that your description of the setup is not accurate], replace the names with the appropriate sheet name and appropriate cell references...

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Steve:
    Sorry, I didn't accurately describe my situation (You can tell I'm Excel-illiterate).
    Where I say "named Symbol" for ex., Symbol is the title of a column, rather than designating a range of cells.

    What I am looking for, then, is the formula that I would place in row 2 of col. U (whose title is Dars).

    Then, I would copy that formula down each of the remaining rows of col. U

    I'm sure there are more elegant ways of doing what I'm trying to do - but I don't know Excel that well.

    Thanks for bearing with me,
    Dick

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like (adapt path, filename, sheet name and cells as appropriate) in the Choices Workbook Cell U2:
    =INDEX('D:\Data\My_Data\Desktop\[Scores.xlsx]Sheet1'!$A$2:$A$100,MATCH(C2,'D:\Data\My_Data\Desk top\[Scores.xlsx]Sheet1'!$C$2:$C$100,0))


    Alternately since you don't have named ranges desired cell to grab is to the right of the cell to lookup, instead of Index/Match combo you could use Vlookup
    =VLOOKUP(C2,'D:\Data\My_Data\Desktop\[Scores.xlsx]Sheet1'!$A$2:$C$100,3,0)

    Steve

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    Dick-Y (2012-04-21)

  8. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    Pittsford,NY
    Posts
    874
    Thanks
    517
    Thanked 35 Times in 27 Posts
    Steve:
    Thank you very much for sticking with me. I was able to make adaptations and get the first formula to work the way I wanted
    it to.
    Dick

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You are welcome. I am glad I was able to help.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •