Hi all,
I hope everyone is safe in this pandemic.
I am trying to use index and match to get a list of values inside one cell in an array and then use min function to get the minimum value of that array.
Match can only pick one value. This is where I am stuck now. I used below array formula to get the values inside a cell array.
{=AGGREGATE(15,6,IF(ISERROR(MATCH('Priority Components'!$D$2:$D$33,Plan!$B77,0)),N/a,'Priority Components'!$A$2:$A$33),ROW(
INDIRECT("1:"&COUNT(IF('Priority Components'!$D$2:$D$33=Plan!$B77,ROW('Priority Components'!$D$2:$D$33)-1)))))}
The output is {42633;42634}
I need to use these values to find the row numbers from the code column and then pick the corresponding values from the date column on the right. I will then use a MIN formula to pick the minimum value and use that for conditional formatting.
I am using excel 2016. Any suggestion to solve this will be great. I am open to non-array formulas as well.
Regards,
Abrar
I hope everyone is safe in this pandemic.
I am trying to use index and match to get a list of values inside one cell in an array and then use min function to get the minimum value of that array.
Match can only pick one value. This is where I am stuck now. I used below array formula to get the values inside a cell array.
{=AGGREGATE(15,6,IF(ISERROR(MATCH('Priority Components'!$D$2:$D$33,Plan!$B77,0)),N/a,'Priority Components'!$A$2:$A$33),ROW(
INDIRECT("1:"&COUNT(IF('Priority Components'!$D$2:$D$33=Plan!$B77,ROW('Priority Components'!$D$2:$D$33)-1)))))}
The output is {42633;42634}
I need to use these values to find the row numbers from the code column and then pick the corresponding values from the date column on the right. I will then use a MIN formula to pick the minimum value and use that for conditional formatting.
Code | Component Description | 20-Apr |
42371 | BOTTLE PLAIN WHITE HDPE (28MM) 1250ML | 28,958 |
42372 | PUMP QV 1250ML 28/410 SMOOTH WALL | 293,730 |
42633 | BOTTLE PLAIN WHITE HDPE QV 28MM 1L | - 16,479 |
42634 | PUMP QV 1L 28MM WITH SMOOTH WALL CAP | 273,224 |
42767 | PUMP QV 500ML 28MM WITH SMOOTH WALL CAP | 181,822 |
42816 | BOTTLE QV PETAL 500G | 41,349 |
42817 | PUMP QV 500G PETAL 28MM SMOOTH WALL CAP | 146,800 |
42877 | BOTTLE WHITE FLAT OVAL 28MM HDPE 500ML | 41,116 |
43297 | BOTTLE UND WHT RND HDPE AQ PTL BASE 500G | 31,513 |
43720 | BOTTLE UNDC WHT RND HDPE BLUE PTL B 250G | 62,093 |
43721 | PUMP LOTN 124MM ST SP 28MM SMT WLL W CAP | 235,830 |
I am using excel 2016. Any suggestion to solve this will be great. I am open to non-array formulas as well.
Regards,
Abrar