SOLVED

# How to ignore values when searching the data

Copper Contributor

# How to ignore values when searching the data

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 Replies
best response confirmed by awd1963 (Copper Contributor)
Solution

# Re: How to ignore values when searching the data

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.

# Re: How to ignore values when searching the data

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.

1 best response

Accepted Solutions
best response confirmed by awd1963 (Copper Contributor)
Solution

# Re: How to ignore values when searching the data

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.