How to ignore values when searching the data

[awd1963]

Hi all,

I believe I am currently using Excel 2019 but it might be 2016.

I posted earlier but  it was deleted for some reason and not sure why.

1. I want to lookup a product in the Data Table that has the minimum MBS for the same SA as the product in the Results Table but ignore the results for the product in the Results Table in the Data Table. 
2. Once it has found the MBS based on the SA in the Data Table, return the product in the Product column of the Data Table to the Results Table in the yellow filled cell. 
3. See below for an example.

Example,

1. The Results Table contains A in the Product column and the corresponding SA and MBS for this product is 200 and 150000 respectively. 
2. In the Yellow filled cell, I want to search the Data Table for the product with the minimum MBS for the same SA but ignore any results associated with product A. 
   1. The current formulas does not seem to work, Product A returns the Next Product as C instead of B. 
   2. Product B returns the next product as C instead of E. 
   3. If I make Product D with the lowest Density such as 800 in the Data Table, then products A through D all return E instead of the product with the nest lowest MBS and Product E returns D. 
   4. Does not matter if I turn it into an array or not (CSE)
3. I also want to avoid VBA/Macros is possible.

Results Table

Results Table
Product	Max SA Tank of the Product	MBS of the Product	Next Product with MBS (Ignore the product in the Product Column of this Table)	MBS of Next Product
A	200	150000	C	146250

Results Table Equations

Results Table
Product	Max SA Tank of the Product	MBS of the Product	Next Product with MBS (Ignore the product in the Product Column of this Table)	MBS of Next Product
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))

Data Table

Data Table
Product	Density (mg/ml)	Volume (ml)	MBS (mg)	SA (cm2)	MBS/SA
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	40000	100	400.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

Data Table Equations

Data Table
Product	Density (mg/ml)	Volume (ml)	MBS (mg)	SA (cm2)	MBS/SA
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	800	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

Thanks for any and all assistance

[#2 · Accepted solution]

@awd1963 

In F4:

=MINIFS($K$4:$K$18,$H$4:$H$18,"<>"&B4,$L$4:$L$18,C4)

This calculates the lowest MBS for the same SA for products not equal to that in B4.

In E4:

=INDEX($H$4:$H$18,MATCH(1,($L$4:$L$18=C4)*($K$4:$K$18=F4),0))

This looks up the product with the same SA, and with the minimum MBS calculated in F4.

[#3]

Thank you.

I see what you did. Calculated the minimum MBS for the same SA as in C4 that was not the same product as in B4 first, then used that result to calculate the product that is associated with the MBS value.

Also, I did not know you could do this within the function,  "<>"&B4.  Thanks for the education.