How to ignore inputs when looking for data in a table

[awd1963]

Hi all, I would like some help trying to code in Excel without using VBA if at all possible. 

Data

Product and Next Product Table						Data Table
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
A	200	150000	C	150000		A	1000	50	50000	100	500.00
						A	1000	100	100000	150	666.67
						A	1000	150	150000	200	750.00
						B	950	50	47500	100	475.00
						B	950	100	95000	150	633.33
						B	950	150	142500	200	712.50
						C	1000	50	50000	100	500.00
						C	1000	100	100000	150	666.67
						C	1000	150	150000	200	750.00
						D	1010	50	50500	100	505.00
						D	1010	100	101000	150	673.33
						D	1010	150	151500	200	757.50
						E	975	50	48750	100	487.50
						E	975	100	97500	150	650.00
						E	975	150	146250	200	731.25

Equations

Product and Next Product Table						Data Table
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
A	=INDEX($L$4:$L$18,MATCH(MAXIFS($M$4:$M$18,$H$4:$H$18,B4),$M$4:$M$18,0))	=INDEX($K$4:$K$18,MATCH(MAXIFS($M$4:$M$18,$H$4:$H$18,B4),$M$4:$M$18,0))	=INDEX(FILTER($H$4:$H$18,($H$4:$H$18<>B4)),MATCH(MIN($K$4:$K$18),$K$4:$K$18,0))	=INDEX($K$4:$K$18,MATCH(1,($H$4:$H$18=E4)*($L$4:$L$18=C4),0))		A	1000	50	=J4*I4	100	=K4/L4
						A	1000	100	=J5*I5	150	=K5/L5
						A	1000	150	=J6*I6	200	=K6/L6
						B	950	50	=J7*I7	100	=K7/L7
						B	950	100	=J8*I8	150	=K8/L8
						B	950	150	=J9*I9	200	=K9/L9
						C	1000	50	=J10*I10	100	=K10/L10
						C	1000	100	=J11*I11	150	=K11/L11
						C	1000	150	=J12*I12	200	=K12/L12
						D	1010	50	=J13*I13	100	=K13/L13
						D	1010	100	=J14*I14	150	=K14/L14
						D	1010	150	=J15*I15	200	=K15/L15
						E	975	50	=J16*I16	100	=K16/L16
						E	975	100	=J17*I17	150	=K17/L17
						E	975	150	=J18*I18	200	=K18/L18

I would like to look up the product information in the Data Table based on the Product in the Product and Next Product Table, return the the product with the minimum MBS based on the SA and ignore the Product input on the Product and Next Product Table. 

For example, the Product in the Product and Next Product Table is A, I want the Next Product with MBS That Is Not Product in Cell B4 in Product and Next Product Table to return from the Data Table the next product that has the MBS with the same SA as in Max SA Tank of Product in Cell B4 in Product and Next Product Table.  In this case, the return value should be B.  However, if the Product is B, then the return value should be E. 

The formula I have for Next Product with MBS That Is Not Product in Cell B4 does not work correctly.  As shown, if A is entered, it returns C and not B.  I also know it is not based on the SA but the minimum MBS value in the Data Table that is not excluded.  If B is entered, the return is C and not E, if C, D, or E is entered, the return value is B.

I hope that all made sense and any help that can be provided would be most appreciative. 

Thanks

[#2]

@awd1963 

You would help us help you by posting a copy of the actual workbook on OneDrive or GoogleDrive with a link pasted here that grants access.

[#3]

@awd1963 

See this discussion where I replied. Why did you ask the same question again?