# How to ignore inputs when looking for data in a table

Copper Contributor

# How to ignore inputs when looking for data in a table

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 Replies

# Re: How to ignore inputs when looking for data in a table

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.

# Re: How to ignore inputs when looking for data in a table

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