Lookup and Offset (Excel 2000)

  • Thread starter Thread starter Bill J.
  • Start date Start date
B

Bill J.

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
 
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
 
Hi Bill
try the following to get your work number for a specific part-no. and
date:
=INDEX(A1:D999,MATCH(G2,OFFSET(A1:A999,0,MATCH(G1,A1:D1,0)-1)),MATCH(G1
,A1:D1,0)+1)
where
G1: part-no. to search
G2: date to search

Note: This will return only the first occurence (not multiple)

HTH
Frank
 
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?
 
Bill said:
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?
Click to expand...

Hi Bill
Ja :-)
 
Back
Top