Ignoring values on a different sheet

[awd1963]

Hi, I am new to this so please be patient.

Want E4 to return the next product with the smallest MBS for the same SA.
For the example, D is the product on this sheet but the product with the min MBS for the same SA is B on Sheet 2. How to get B displayed from the information on Sheet 2?

This same information is also in the Excel cell. Cell E4 is setup as an array.

IgnoreExample.xlsx
	B	C	D	E	F
2	Product	Max SA Tank of Product in Cell B4	MSB of Product in Cell B4	Next Product with MBS That Is Not Product in Cell B4	MBS of Next Product That Is Not Product in Cell B4
3				Want E4 to return the next product with the smallest MBS for the same SA. For the example, D is the product on this sheet but the product with the min MBS for the same SA is B on Sheet 2. How to get B displayed from the information on Sheet 2?
4	D	200	151500	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
C4	C4	=IF(B4="","Need to enter a product that is on Sheet 2 first",INDEX(Sheet2!$F$3:$F$17,MATCH(MAXIFS(Sheet2!$G$3:$G$17,Sheet2!$B$3:$B$17,B4),Sheet2!G3:G17,0)))
D4	D4	=IF(B4="","Need to enter a product that is on Sheet 2 first",INDEX(Sheet2!$E$3:$E$17,MATCH(MAXIFS(Sheet2!$G$3:$G$17,Sheet2!$B$3:$B$17,B4),Sheet2!G3:G17,0)))
E4	E4	=IF(B4="","Need to enter a product that is on Sheet 2 first",INDEX(Sheet2!$B$3:$B17,MATCH(1,(Sheet2!F3:F17=Sheet1!C4)*(Sheet2!B3:B17<>B4)*(MIN(Sheet2!$E$3:$E$17)),0)))
F4	F4	=IF(B4="","Need to enter a product that is on Sheet 2 first",INDEX(Sheet2!E3:E17,MATCH(1,(Sheet2!B3:B17=E4)*(Sheet2!F3:F17=C4),0)))
Press CTRL+SHIFT+ENTER to enter array formulas.

IgnoreExample.xlsx
	B	C	D	E	F	G
2	Product	Density (mg/ml)	Volume (ml)	MBS (mg)	SA (cm2)	MBS/SA
3	A	1000	50	50000	100	500.00
4	A	1000	100	100000	150	666.67
5	A	1000	150	150000	200	750.00
6	B	950	50	47500	100	475.00
7	B	950	100	95000	150	633.33
8	B	950	150	142500	200	712.50
9	C	1000	50	50000	100	500.00
10	C	1000	100	100000	150	666.67
11	C	1000	150	150000	200	750.00
12	D	1010	50	50500	100	505.00
13	D	1010	100	101000	150	673.33
14	D	1010	150	151500	200	757.50
15	E	975	50	48750	100	487.50
16	E	975	100	97500	150	650.00
17	E	975	150	146250	200	731.25
Sheet2

Cell Formulas
Range	Formula
E3:E17	E3	=D3*C3
G3:G17	G3	=E3/F3

Thanks for the help.

 

[awd1963]

Here is as close as I have gotten to ignoring values.

I put everything on one sheet for now to make it simpler to navigate.

IgnoreExample.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Product	Max SA Tank of Product in Cell B4	MBS of Product in Cell B4	Next Product with MBS That Is Not Product in Cell B4	MBS of Next Product That Is Not Product in Cell B4					Product	Density (mg/ml)	Volume (ml)	MBS (mg)	SA (cm2)	MBS/SA
3	A	200	150000	C	150000					A	1000	50	50000	100	500.00
4	B	200	142500	C	150000					A	1000	100	100000	150	666.67
5	C	200	150000	B	142500					A	1000	150	150000	200	750.00
6	D	200	151500	B	142500					B	950	50	47500	100	475.00
7	E	200	146250	B	142500					B	950	100	95000	150	633.33
8										B	950	150	142500	200	712.50
9										C	1000	50	50000	100	500.00
10										C	1000	100	100000	150	666.67
11										C	1000	150	150000	200	750.00
12										D	1010	50	50500	100	505.00
13										D	1010	100	101000	150	673.33
14										D	1010	150	151500	200	757.50
15										E	975	50	48750	100	487.50
16										E	975	100	97500	150	650.00
17										E	975	150	146250	200	731.25
Sheet1

Cell Formulas
Range	Formula
C3:C7	C3	=IF(B3="","Need to enter a product that is in Column K first in cell B4",INDEX($O$3:$O$17,MATCH(MAXIFS($P$3:$P$17,$K$3:$K$17,B3),$P$3:$P$17,0)))
D3:D7	D3	=IF(B3="","Need to enter a product that is in Column K first in cell B4",INDEX($N$3:$N$17,MATCH(MAXIFS($P$3:$P$17,$K$3:$K$17,B3),$P$3:$P$17,0)))
E3:E7	E3	=IF(B3="","Need to enter a product that is in Column K first in cell B4",INDEX(FILTER($K$3:$K$17,($K$3:$K$17<>B3)),MATCH(MIN($N$3:$N$17),$N$3:$N$17,0)))
F3:F7	F3	=IF(B3="","Need to enter a product that is in Colum K first in cell B4",INDEX($N$3:$N$17,MATCH(1,($K$3:$K$17=E3)*($O$3:$O$17=C3),0)))
N3:N17	N3	=M3*L3
P3:P17	P3	=N3/O3

Rows 5, 6, and 7 give me the correct response in Column E but Row 3 should provide B in Column E and Row 4 should provide E in Column E.

Any help that can be provided would be great.

Also, I am trying to stay away from VBA as much as possible.

Thanks

 

[awd1963]

This has been resolved by another person on the MS Excel forums.

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

Please post a link to the question you asked on the other forum.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.