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]

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

Thanks Special-K99.

 

[Med4040]

You'll need to check if this logic is valid but here's what I came up with. I hope you have MINIFS and MAXIFS ... !

Array Formulas Cell Formula G6 {=INDEX($B$2:$F$2,MIN(IF($B$5:$F$5=MINIFS($B$5:$F$5,$B6:$F6,"<>",$B$10:$F$10,MAXIFS($B$10:$F$10,$B6:$F6,"<>")),COLUMN($B6:$F6)-COLUMN($B6)+1)))} H6 {=INDEX($B$2:$F$2,MIN(IF($B$3:$F$3=MINIFS($B$3:$F$3,$B6:$F6,"<>",$B$2:$F$2,"<>"&$G6,$B$4:$F$4,MAXIFS($B$4:$F$4,$B6:$F6,"<>",$B$2:$F$2,"<>"&$G6)),COLUMN($B6:$F6)-COLUMN($B6)+1)))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

WBD

Thanks WBD for your help. The outputs in col G are correct but they are not in col H

 

[Med4040]

Thanks WBD for your help. The outputs in col G are correct but they are not in col H

My apologies WBD. all outputs are correct. I am using Excel 2010. I thank it doesn't have MINIFS and MAXIFS. is there anything compatible with Excel 2010?

Thank you so much.

 

[Marcelo Branco]

Thanks so much Marcelo. All outputs are correct except H8. The output should be lot 2 because it has a larger dose (800) than lot 4 (700)

Have you seen post 9?
Repeating: i don't understand why H6 should be Lot 4 (Dose = 700) since Lot 2 has a larger dose (800).

M.

 

[Marcelo Branco]

My apologies WBD. all outputs are correct. I am using Excel 2010. I thank it doesn't have MINIFS and MAXIFS. is there anything compatible with Excel 2010?

Thank you so much.

If the results provided by WBD (post 5) are all correct you can try this in Excel 2010

	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​	lot size​	5000​	8000​	2000​	7500​	9500​
4​	Dose​	800​	800​	600​	700​	700​
5​	Potency​	5​	10​	50​	40​	2​
6​	Equipment 1​	x​	x​		x​	x​	lot 1​	lot 2​
7​	Equipment 2​			x​	x​	x​	lot 5​	lot 4​
8​	Equipment 3​	x​	x​		x​		lot 1​	lot 2​
9​	Equipment 4​		x​	y​		y​	lot 2​	lot 5​
10​	Risk Number​	90​	90​	20​	20​	60​

<tbody>
</tbody>

Array formula in G6 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B6:F6<>"",B$10:F$10-B$5:F$5/1000)),IF(B6:F6<>"",B$10:F$10-B$5:F$5/1000),0))
Ctrl+Shift+Enter

Array formula in H6 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B6:F6<>"",IF(B$2:F$2<>K6,B$4:F$4-B$3:F$3/(10^6)))),IF(B6:F6<>"",IF(B$2:F$2<>K6,B$4:F$4-B$3:F$3/(10^6))),0))
Ctrl+Shift+Enter

M.

 

[Med4040]

Have you seen post 9?
Repeating: i don't understand why H6 should be Lot 4 (Dose = 700) since Lot 2 has a larger dose (800).

M.

I am sorry Marcelo for the confusion. I typed lot 4 in error instead of lot 2. H6 should be lot 2. You are right

so the outputs should be:
H6: Lot 2
H7: Lot 4
H8: Lot 2
H8: Lot 5


Thanks Marcelo for modifying WBD's formulas
=INDEX(B$2:F$2,MATCH(MAX(IF(B6:F6<>"",B$10:F$10-B$5:F$5/1000)),IF(B6:F6<>"",B$10:F$10-B$5:F$5/1000),0))
Ctrl+Shift+Enter. This is perfect

Array formula in H6 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B6:F6<>"",IF(B$2:F$2<>K6,B$4:F$4-B$3:F$3/(10^6)))),IF(B6:F6<>"",IF(B$2:F$2<>K6,B$4:F$4-B$3:F$3/(10^6))),0))
Ctrl+Shift+Enter. This gave different outputs.

 

[Marcelo Branco]

Sorry my second formula has a typo (i was making tests with first result in K6...)

Correction
H6 copied down
=INDEX(B$2:F$2,MATCH(MAX(IF(B6:F6<>"",IF(B$2:F$2<>G6,B$4:F$4-B$3:F$3/(10^6)))),IF(B6:F6<>"",IF(B$2:F$2<>G6,B$4:F$4-B$3:F$3/(10^6))),0))
Ctrl+Shift+Enter

M.

 

[Med4040]

Awesome
Thank you so much Marcelo. I really appreciate it
Thanks to WBD as well.
You guys are awesome.

Regards,

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[wideboydixon]

Thanks Marcelo,

So since the output in G6 is lot 1 (max risk number and lowest potency), lot 1 should be excluded in G6. Then, the remaining lots are lot 2, lot 3, lot 4 and lot 5. The lots with largest dose are lot 4 and lot5 but lot 4 has a smaller lot and therefore, the output should be lot 4.

Nope; the lot with the largest dose is lot 2 so I'm not sure why it's not the answer.

WBD