A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup and Offset (Excel 2000)



 
 
Thread Tools Display Modes
  #1  
Old February 2nd, 2004, 10:41 PM
Bill J.
external usenet poster
 
Posts: n/a
Default 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  
Old February 2nd, 2004, 11:49 PM
Bill J.
external usenet poster
 
Posts: n/a
Default 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  
Old February 3rd, 2004, 01:10 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old February 3rd, 2004, 02:23 AM
Bill J.
external usenet poster
 
Posts: n/a
Default 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  
Old February 3rd, 2004, 10:15 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.