Forum Discussion
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.
- 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.
- 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.
- See below for an example.
Example,
- The Results Table contains A in the Product column and the corresponding SA and MBS for this product is 200 and 150000 respectively.
- 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.
- The current formulas does not seem to work, Product A returns the Next Product as C instead of B.
- Product B returns the next product as C instead of E.
- 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.
- Does not matter if I turn it into an array or not (CSE)
- 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
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.
2 Replies
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.
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.
