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]

Marecelo, I have another question. It is not exactly the same but similar. Should I put it here or I should start a new post?

 

[Marcelo Branco]

Marecelo, I have another question. It is not exactly the same but similar. Should I put it here or I should start a new post?

I think you should create another post. This one is becoming too long.

M.