Array formula help required

[saad_mce]

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.

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

Any suggestion to solve this will be great.

Regards,
Abrar

 

[DRSteele]

Not everyone is safe during the pandemic: over 200,000 people have died and 3 million are in peril.

What version of Excel do you have? This task would be much easier if you have Excel 365 with the new dynamic array functions.

 

[Fluff]

Duplicate Array formula Help Required

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).