If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Lookup and Offset (Excel 2000)
I have tried a zillion times to lookup values and get
numbers returned from two columns that apply to the same item. So far, no luck. Here's the scenario: 009475 009475 009478 009478 2/16/04 8749 2/16/04 8750 3/8/04 8680 3/8/04 8681 3/8/04 8830 3/8/04 8963 3/22/04 8706 3/8/04 8831 3/22/04 8707 Row "A" is part numbers of products that we manufacture. A2 is the date a production work order has been scheduled and B2 is the work order number generated by the ERP system and to which we will build the quantity called for. C1 is the next part number, under which are all open work orders associated with that part number. There may be 100+ part numbers with open work orders going across the spreadsheet. In a separate spreadsheet I want to look up from the above table - by part number AND date and return both the date and any open work orders for each part number for the specific date. This forms the production schedule. It should look something like this: 2/16/04 2/23/04 3/1/04 3/8/04 Part No. 9475 8479 8680,8830 Part No. 9478 8750 8681,8963 Part No. XXXX I've tried doing hlookups with an offset to pull the work order number in but I can't seem to get it to work. It doesn't seem that complicated but I guess I'm not quite grasping the offset syntax. I simply want it to lookup the part number, look up the date below it, and then look up the work order number that is one column to the right of that date. For example, Part No. 9475 has an open work order to be built on 2/16 and two more work orders on 3/8. I am pulling each date into a separate cell in the target spreadsheet, so the lookup itself is easy. But why is the offset giving me such a hard time (#Value, #N/A, - you name it)? Thanks for any help! Bill |
#2
|
|||
|
|||
Lookup and Offset (Excel 2000)
I should have browsed before posting: I found the answer from Peo in response to a virtually identical question posed earlier in the day. Thanks Peo - it works great! Bill |
#3
|
|||
|
|||
Lookup and Offset (Excel 2000)
Hi Bill
try the following to get your work number for a specific part-no. and date: =INDEX(A1999,MATCH(G2,OFFSET(A1:A999,0,MATCH(G1, A11,0)-1)),MATCH(G1 ,A11,0)+1) where G1: part-no. to search G2: date to search Note: This will return only the first occurence (not multiple) HTH Frank Bill J. wrote: I have tried a zillion times to lookup values and get numbers returned from two columns that apply to the same item. So far, no luck. Here's the scenario: 009475 009475 009478 009478 2/16/04 8749 2/16/04 8750 3/8/04 8680 3/8/04 8681 3/8/04 8830 3/8/04 8963 3/22/04 8706 3/8/04 8831 3/22/04 8707 Row "A" is part numbers of products that we manufacture. A2 is the date a production work order has been scheduled and B2 is the work order number generated by the ERP system and to which we will build the quantity called for. C1 is the next part number, under which are all open work orders associated with that part number. There may be 100+ part numbers with open work orders going across the spreadsheet. In a separate spreadsheet I want to look up from the above table - by part number AND date and return both the date and any open work orders for each part number for the specific date. This forms the production schedule. It should look something like this: 2/16/04 2/23/04 3/1/04 3/8/04 Part No. 9475 8479 8680,8830 Part No. 9478 8750 8681,8963 Part No. XXXX I've tried doing hlookups with an offset to pull the work order number in but I can't seem to get it to work. It doesn't seem that complicated but I guess I'm not quite grasping the offset syntax. I simply want it to lookup the part number, look up the date below it, and then look up the work order number that is one column to the right of that date. For example, Part No. 9475 has an open work order to be built on 2/16 and two more work orders on 3/8. I am pulling each date into a separate cell in the target spreadsheet, so the lookup itself is easy. But why is the offset giving me such a hard time (#Value, #N/A, - you name it)? Thanks for any help! Bill |
#4
|
|||
|
|||
Lookup and Offset (Excel 2000)
Hi Frank, Thanks for the help. It works. In fact, after studying the formula for 10 minutes, I think I even get it! Bill P.S. Sind Sie Deutsch? |
#5
|
|||
|
|||
Lookup and Offset (Excel 2000)
Bill J. wrote:
Hi Frank, Thanks for the help. It works. In fact, after studying the formula for 10 minutes, I think I even get it! Bill P.S. Sind Sie Deutsch? Hi Bill Ja :-) |
Thread Tools | |
Display Modes | |
|
|