Help please with Offset/Match formula

[John Clarke]

I am struggling with a formula using Offset and Match.
I have a table of data and I need to read in data into specific cells,
I am using the combination of Offset and Match and everything is OK
for 90% of the time. My problem is the following:-
I have a number of production lines, some products can be manufactured
on more than one line. Where the product/resource combination is
unique then everything is fine. I need my Offset to match against more
than one criteria.
Example

Resource Product Period 1 qty Period 2 qty etc.....

Line 1 1233 1,000 1,000 .........
Line 1 1445 1,000 1,000 .........
Line 1 2331 1,000 1,000 .........
Line 2 1233 0,000 2,000 .........
Line 2 6823 3,000 4,000 .........

With using offset, when I match against the Product description the
formula assigns the Line 1 production to the Line 2 as well against
product 1233. What I need to do is to match the offset against both
line and product. I have tried using the AND function but this errors.
I have also tried to reference the "Reference" field in the Offset
formula using a combination of Index and Match but again this does not
work.
The one solution I do not want to do is to concatenate the
Product/Resource as I want the data sheet not to have any formulas at
all as this spreadsheet is being constantly updated from an external
source.


Thanks in advance

John C

 

[Don Guillett]

I think this might help
=sumproduct((a2:a200="Line 1")*(b2:b200=1223))
to count
=sumproduct((a2:a200="Line 1")*(b2:b200=1223)*c2:c200)
to sum period 1 qty

 

[John Clarke]

Don
I have tried this but does not work. I get an #NUM error

I would ideally like to persue the offset function for this.