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.

 

[Special-K99]

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.

 

[Special-K99]

What happens if there's a tie in the final result, ie more than one lot has the same largest risk and smallest potency?
Could there be a tie or is this not possible?
Same goes for the value in H6.

in G6
=INDEX(B2:F2,1,MATCH(MIN(IF(B10:F10=MAX(B10:F10),B5:F5))
Array formula, use Ctrl-Shift-Enter

Second part is more tircky.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Question:
Why the result in H6 should be lot 4?
Excluding the result in G6 (lot 1), lot 2 is the one with the largest Dose (800).
Could you clarify?

M.

 

[wideboydixon]

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 ... !

Book1
	A	B	C	D	E	F	G	H
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
Sheet1

Cell Formulas
Range	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)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[wideboydixon]

What happens if there's a tie in the final result, ie more than one lot has the same largest risk and smallest potency?
Could there be a tie or is this not possible?
Same goes for the value in H6.

in G6
=INDEX(B2:F2,1,MATCH(MIN(IF(B10:F10=MAX(B10:F10),B5:F5))
Array formula, use Ctrl-Shift-Enter

Second part is more tircky.

I think the "x" and "y" are significant. For example, lot 1 is not made on Equipment 1 and 4.

WBD

 

[Marcelo Branco]

If you do want to exclude all the lots with same risk than the risk of G6 (results in column G) maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td][/td][td]

lot 1​

[/td][td]

lot 2​

[/td][td]

lot 3​

[/td][td]

lot 4​

[/td][td]

lot 5​

[/td][td]

Worst case A​

[/td][td]

Worst case A​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

lot size​

[/td][td]

5000​

[/td][td]

8000​

[/td][td]

2000​

[/td][td]

7500​

[/td][td]

9500​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Dose​

[/td][td]

800​

[/td][td]

800​

[/td][td]

600​

[/td][td]

700​

[/td][td]

700​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Potency​

[/td][td]

5​

[/td][td]

10​

[/td][td]

50​

[/td][td]

40​

[/td][td]

2​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Equipment 1​

[/td][td]

x​

[/td][td]

x​

[/td][td][/td][td]

x​

[/td][td]

x​

[/td][td]

lot 1​

[/td][td]

lot 4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Equipment 2​

[/td][td][/td][td][/td][td]

x​

[/td][td]

x​

[/td][td]

x​

[/td][td]

lot 5​

[/td][td]

lot 4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Equipment 3​

[/td][td]

x​

[/td][td]

x​

[/td][td][/td][td]

x​

[/td][td][/td][td]

lot 1​

[/td][td]

lot 4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

Equipment 4​

[/td][td][/td][td]

x​

[/td][td]

y​

[/td][td][/td][td]

y​

[/td][td]

lot 2​

[/td][td]

lot 5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

Risk Number​

[/td][td]

90​

[/td][td]

90​

[/td][td]

20​

[/td][td]

20​

[/td][td]

60​

[/td][td][/td][td][/td][/tr]
[/table]

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$10:F$10<>INDEX(B$10:F$10,MATCH(G6,B$2:F$2,0)),B$4:F$4-B$3:F$3/(10^6)))),IF(B6:F6<>"",IF(B$10:F$10<>INDEX(B$10:F$10,MATCH(G6,B$2:F$2,0)),B$4:F$4-B$3:F$3/(10^6))),0))
Ctrl+Shift+Enter

Hope this helps

M.

 

[Med4040]

Welcome to Mr Excel forum

Question:
Why the result in H6 should be lot 4?
Excluding the result in G6 (lot 1), lot 2 is the one with the largest Dose (800).
Could you clarify?

M.

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.

 

[Marcelo Branco]

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.

Still not clear, at least for me.
Lot 2 has a dose of 800 that is larger than the doses of lots 4 and 5 (700).

M.

 

[Med4040]

If you do want to exclude all the lots with same risk than the risk of G6 (results in column G) maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD]

lot 1​

[/TD]
[TD]

lot 2​

[/TD]
[TD]

lot 3​

[/TD]
[TD]

lot 4​

[/TD]
[TD]

lot 5​

[/TD]
[TD]

Worst case A​

[/TD]
[TD]

Worst case A​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

lot size​

[/TD]
[TD]

5000​

[/TD]
[TD]

8000​

[/TD]
[TD]

2000​

[/TD]
[TD]

7500​

[/TD]
[TD]

9500​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Dose​

[/TD]
[TD]

800​

[/TD]
[TD]

800​

[/TD]
[TD]

600​

[/TD]
[TD]

700​

[/TD]
[TD]

700​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Potency​

[/TD]
[TD]

5​

[/TD]
[TD]

10​

[/TD]
[TD]

50​

[/TD]
[TD]

40​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Equipment 1​

[/TD]
[TD]

x​

[/TD]
[TD]

x​

[/TD]
[TD][/TD]
[TD]

x​

[/TD]
[TD]

x​

[/TD]
[TD]

lot 1​

[/TD]
[TD]

lot 4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Equipment 2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

x​

[/TD]
[TD]

x​

[/TD]
[TD]

x​

[/TD]
[TD]

lot 5​

[/TD]
[TD]

lot 4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Equipment 3​

[/TD]
[TD]

x​

[/TD]
[TD]

x​

[/TD]
[TD][/TD]
[TD]

x​

[/TD]
[TD][/TD]
[TD]

lot 1​

[/TD]
[TD]

lot 4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Equipment 4​

[/TD]
[TD][/TD]
[TD]

x​

[/TD]
[TD]

y​

[/TD]
[TD][/TD]
[TD]

y​

[/TD]
[TD]

lot 2​

[/TD]
[TD]

lot 5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

Risk Number​

[/TD]
[TD]

90​

[/TD]
[TD]

90​

[/TD]
[TD]

20​

[/TD]
[TD]

20​

[/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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$10:F$10<>INDEX(B$10:F$10,MATCH(G6,B$2:F$2,0)),B$4:F$4-B$3:F$3/(10^6)))),IF(B6:F6<>"",IF(B$10:F$10<>INDEX(B$10:F$10,MATCH(G6,B$2:F$2,0)),B$4:F$4-B$3:F$3/(10^6))),0))
Ctrl+Shift+Enter

Hope this helps

M.

Thanks so much Marcelo. You got the criteria exactly right. It is almost perfect but in H8, the output should be lot 2 because it has a larger dose (800) as compared to lot 4 (700)