This may take some Work so I appreciate all the help i can get. I am trying to create a living master list for our team leaders. Each Account Manager has their own inventory list that they work on. They enter notes on their inventory list. I have pulled over the Part Number and Note by referencing the file location and columns for each acct manager so that when they add new infomation it automatically goes to the masterfile and set it up like below on sheet 2:

A B C D E F
JohnID JohnNotes MarkIDs MarkNotes BobIDs BobNotes
123 WorkingOn 456 Shipping 4/6 789 Received Commitment for 5000

The team leader wants to have this Info pulled on sheet one like so:

ItemID Price MonthsOnhand Notes
123 .5 6
456 .6 7
789 1.30 1

I want the formula to search for the item id and return the note tied to it. I've been trying to use Index match like so....

=INDEX(ITEMNOTES,LOOKUP(9000000000+307,CHOOSE({1,2,3},MATCH(Sheet1!$A7,Sheet2!$I$2:$I$100,0),MATCH(Sheet1!$A7,Sheet2!$A$2:$A$100,0),MATCH(Sheet1!$A7,Sheet2!$C$2:$C$100,0))))

But on 456 and 789, it'll return the note for 123 ONLY. Please help!