Index and match question

[Med4040]

Hi guys,

I need to find the smallest batch size of a product that is made on particular equipment
So on the right I have Equipment 1 (Cell K4) and on this equipment, Product A and Product are made
What are the smallest batch sizes of these products? The source table is on the left
The results should be 221.4 in M4 and 64.4 in M5 because these are the only batch sizes

Now, on equipment 3 in, there are 2 products A (with different batch sizes), product B and Product D. In this case for the product A, the smallest batch size should be the result, when I select product A.

I made an attempt to do that but I couldn't do it.

Thank you.

 

[Med4040]

Anyone can help me with this?

 

[Aladin Akyurek]

hat do those Y and X mean?

 

[Med4040]

Y and X indicate which product is made on which equipment (vertically). They mean the same thing but I use X to indicate the this equipment is an alternate equipment.
Horizontally, you see which products made on a given equipment.
On the right I have equipment 1 and on this equipment, Product A and Product C are made. So what is the batch size of each of these products?

 

[Aladin Akyurek]

Y and X indicate which product is made on which equipment (vertically). They mean the same thing but I use X to indicate the this equipment is an alternate equipment.
Horizontally, you see which products made on a given equipment.
On the right I have equipment 1 and on this equipment, Product A and Product C are made. So what is the batch size of each of these products?

In M4 control+shift+enter, not just enter, and copy down:

=MIN(IF(LEN(INDEX($C$4:$G$8,MATCH($K$4,$A$4:$A$8,0),0))>0,IF($C$1:$G$1=$L4,$C$3:$G$3)))

 

[Med4040]

Thanks Aladin,

Product A showing the largest batch size but it should be the smallest. Do you want me to upload the actual file, if you like?

[/IMG]

 

[Marcelo Branco]

Thanks Aladin,

Product A showing the largest batch size but it should be the smallest. Do you want me to upload the actual file, if you like?

I think Aladin's formula is correct - there is neither x nor y in C4, so it seems that Equipment 1 doesn't make the batch with size 110.0, differently than Equipment 3 that has a y in both columns.

M.

 

[Aladin Akyurek]

This is what you wrote: "

The results should be 221.4 in M4 and 64.4 in M5 because these are the only batch sizes​

"!...

If you look at the formula closely, it calculates the smallest (minimum) value for 1, Product A, where the value is associated with Non-empty cell.

Are you wanting 111.0 for the foregoing?

 

[Med4040]

This is what you wrote: "

The results should be 221.4 in M4 and 64.4 in M5 because these are the only batch sizes​

"!...

If you look at the formula closely, it calculates the smallest (minimum) value for 1, Product A, where the value is associated with Non-empty cell.

Are you wanting 111.0 for the foregoing?

My sincere apologies Aladin. I didn't pay attention that only a large batch size of product A made on equipment 1
The results are accurate. I really appreciate your help and sorry for the confusion

 

[Med4040]

I think Aladin's formula is correct - there is neither x nor y in C4, so it seems that Equipment 1 doesn't make the batch with size 110.0, differently than Equipment 3 that has a y in both columns.

M.

Thanks Marcelo, you are absolutely right. My bad.