Index adn match with max and min criteria

[Med4040]

Hello everyone,

I am glad to be here. I need some help with this table please.
I have different lots made on different machines and the corresponding cells are marked x or y

In G6 I need to find out which lot has the largest risk number and smallest potency.
In H6 I need to find out which lot has the largest dose and smallest lot excluding the lot in G6.



For example in G6, lot 1 and lot 2 have highest risk number, then the second criterion should apply (smallest potency of lot 1 and lot 2). The output should be lot 1. Now in H6 formula should exclude lot 1 and look for other lots. So lot 4 and lot 5 have the highest dose and therefore the second criterion should apply (smallest lot size). The output should be lot 4.

I appreciate your help
P.S. I would have uploaded an Excel file but there is no option to upload a file.

 

[Med4040]

Sorry for the late reply WBD,

You are absolutely right, the output in H6 is lot 2. I made a typo at the beginning. Please see thread 16.

I really appreciate your help

Regards,

 

[Med4040]

I encountered this situation where I had to add another row (API) which requires modification of the formula in H7.
Cell H7 displays “lot 2” because it has the max dose and smallest lot size, excluding the product in G7
With the new row I added, H7 should display max dose and smallest lot size, excluding the product in G7 and the API of G7. The output in H7 should be lot 4,
The idea is that G7 and H7 cannot be of the same API.

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​	E​	F​	G​	H​
1​
2​		lot 1​	lot 2​	lot 3​	lot 4​	lot 5​	Worst case A​	Worst case B​
3​	API​	C​	C​		D​	E​
4​	lot size​	5000​	8000​	2000​	7500​	9500​
5​	Dose​	800​	800​	600​	700​	700​
6​	Potency​	5​	10​	50​	40​	2​
7​	Equipment 1​	x​	x​		x​	x​	lot 1​	lot 4​
8​	Equipment 2​			x​	x​	x​	lot 5​	lot 4​
9​	Equipment 3​	x​	x​		x​		lot 1​	lot 4​
10​	Equipment 4​		x​	y​		y​	lot 2​	lot 5​
11​	Risk Number​	90​	90​	20​	20​	60​

Array formula in G7 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B7:F7<>"",B$11:F$11-B$6:F$6/1000)),IF(B7:F7<>"",B$11:F$11-B$6:F$6/1000),0))
Ctrl+Shift+Enter

Array formula in H7 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$5:F$5-B$4:F$4/(10^6)))),IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$5:F$5-B$4:F$4/(10^6))),0))
Ctrl+Shift+Enter

M.

 

[Med4040]

You did it again

Thank you so much Marcelo. I really appreciate your quick response.

 

[Marcelo Branco]

You are welcome.

M.

 

[Med4040]

Sorry, I created a thread in error.

 

[Med4040]

Hi again Marcelo,

I need to make new criteria for product B in H7. It should be the lowest value in row 12 (lot size/ Dose) excluding the product an API in G7
I couldn't modify your last formula to this criteria as it resulted in different results. When you get a chance, can you please look at it.

Thank you so much

INDEX(B$2:F$2,MATCH(MAX(IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$5:F$5-B$4:F$4/(10^6)))),IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$5:F$5-B$4:F$4/(10^6))),0))

[/IMG]

 

[Med4040]

So with this new criteria, the result in H10 for example, should be "lot 3"

 

[Marcelo Branco]

Try

H7 copied down
=INDEX(B$2:F$2,MATCH(MIN(IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$12:F$12))),IF(B7:F7<>"",IF(B$3:F$3<>INDEX(B$3:F$3,MATCH(G7,B$2:F$2,0)),B$12:F$12)),0))
Ctrl+Shift+Enter

M.

 

[Med4040]

Thank you so much Marcelo. You are a life saver