Questions

MS Excel 2007 Lookup in Another Workbook

Tags:
+
0 Votes
Locked

MS Excel 2007 Lookup in Another Workbook

s2h21
Hello everyone. I have a complicated lookup that I need to do from 2 seperate workbooks. I will try to keep it simple. I am needing to place a value into cell C3 in "Workbook A". In order to get that value, I need to take the part number, for simplicity call it "Part A", found in cell A3 of "Workbook A", search all of Column A in "Workbook B" until it finds the matching "Part A" and return the cooresponding value found in "Column F" of "Workbook B". So if it did the lookup in "Workbook B" and found "Part A" to be in cell A567, I would need the value from F567 to be place into cell C3 in "Workbook A". Basically I am trying to save myself alot of hunting for values, then pasting them from one workbook to another. I am not very experienced in doing lookups and any help would be greatly appreciated.
  • +
    0 Votes
    Darryl~ Moderator

    =LOOKUP(A3,[Book2.xlsx]Sheet1!$A$1:$A$20,[Book2.xlsx]Sheet1!$B$1:$B$20)

    In the above example I'm just searching the first 20 rows in columnA Book2 for the value in A1 Book1 and entering the value in columnB Book2 (from the row containing the value being searched) in columnB Book1

    But you run into issues where the entries in Book2 ColumnA have to be sorted so they are in order (1, 2, 3, etc). you also run into issues if the value you're looking for doesn't exist....it takes the closes value it can find

    Have a look at Microsoft's information on the LOOKUP function....

    http://support.microsoft.com/kb/324986

  • +
    0 Votes
    Darryl~ Moderator

    =LOOKUP(A3,[Book2.xlsx]Sheet1!$A$1:$A$20,[Book2.xlsx]Sheet1!$B$1:$B$20)

    In the above example I'm just searching the first 20 rows in columnA Book2 for the value in A1 Book1 and entering the value in columnB Book2 (from the row containing the value being searched) in columnB Book1

    But you run into issues where the entries in Book2 ColumnA have to be sorted so they are in order (1, 2, 3, etc). you also run into issues if the value you're looking for doesn't exist....it takes the closes value it can find

    Have a look at Microsoft's information on the LOOKUP function....

    http://support.microsoft.com/kb/324986