Index / Match / Large based on a criteria.

I have been pulling my hair out for hours and I hope that someone here can offer me some help.

I have a large list of product models, and the component parts that make up each model.

The data is all in one list, and I want to generate reports off the data to show the largest price effect for only the red models.

The largest mix effect for only the red models, etc. I have attached an example. I am hoping to use the Index/Large/match formula.

My list is quite large, and I want to use the most efficient formula that won't bog down my workbook.

Thank you very much!


Model Part # PriceEffect MixEffect Prev Month Price Curr Month Price
Red ABC 5.00 20.00 80.00 90.00
Red DEF 6.00 21.00 81.00 91.00
Yellow GHI 7.00 22.00 82.00 92.00
Blue JKL 8.00 23.00 83.00 93.00
Red MNO 9.00 24.00 84.00 94.00
Red PQR 10.00 25.00 85.00 95.00
Red STU 11.00 26.00 86.00 96.00
Red VWX 12.00 27.00 87.00 97.00
Blue ABC 13.00 28.00 88.00 98.00