My post shows which is which. There are two sheets (tabs at the bottom of your screen).

Worksheet1: Contains your data, just columns C, D and E.

Worksheet2: Contains the data you supplied in your post for cells A1 to E6. The rows below row 6 are where I put the formulae:

A7 to A20 are shown but all these formulae should be copied down as far as you'll need to handle your data. The formula

=IFERROR(AGGREGATE(15,6,Worksheet1!$D$2:$D$999,ROW()-ROWS($A$1:$A$6)),"")

takes the Type from Worksheet1and puts them in ascending sequence (i.e. 3x"1", 5x"2" and 6x"6"). It's OK if you copy this formula down too far because it will just return an empty cell.

B7 should be copied down rows and across columns as far as needed because again it will just return an empty cell if not needed.

=IFERROR(INDEX(Worksheet1!$E$2:$E$999,AGGREGATE(15,6,ROW(Worksheet1!$E$2:$E$999)-ROW($A$1)/((Worksheet1!$C$2:$C$999=B$1)*(Worksheet1!$D$2:$D$999=$A7)),COUNTIF($A$6:$A7,$A7))),"")

Uses the INDEX to retrieve column E from Worksheet1 (ComponentName) where the Worksheet1 Product SKU in column A matches the Worksheet2 row 1 entry for this column, and the Worksheet1 Type in column D matches the Worksheet2 type we constructed in column A. The COUNTIF tells the AGGREGATE which occurrence to retrieve when there are more than one entries for that Type and Product SKU (e.g. for our Worksheet2 column B product SYNARBALMr we have two entries in Worksheet1 with a type of "6". In Worksheet2 B15 the COUNTIF($A$6:$A15,$A15) returns a 1 so the AGGREGATE gets the first match which is in row 11 of Worksheet1, "Active Release Balm Retail - Label". The Worksheet2 B16 COUNTIF($A$6:$A16,$A16) will return a 2 so we get the second entry from Worksheet1 for Type 6, product SYNARBALMr which is row 12, "Active Release Balm Bulk".

I hope that explains but what I posted are not images. If you create a new Workbook and two sheets named Worksheet1 and Worksheet2 then you can click the little pages on the top left of each of my posts and it copies the contents, including formulae, onto the clipboard. Then you paste them into your Worksheet1 then Worksheet2.