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

But it will fail if two Red has same price.

try just Pivot table.. and filter desired model

Hi! Suggest a "Tie Breaker" to make the prices more unique (as I am not aware of the "large list of product models"). The formula in L12 is an array formula, so exit with Ctrl+Shift+Enter. Hmmm, see now that "npamcpp" had the same idea...

npamcpp

I copied your formulas for M12 and N12 and dropped them into my sheet and received an error.

Could you see if you can drop the formulas in my sheet and they work? I really like your formula structure and that's what I was looking for.

Thank you!

Hmmm, when you change cell C13 to "12", then you have "Part Number" STU twice in the result list... that's why I have used the tie breaker... to make all the values unique... (see my attachment above; if you are confused iro the decimal places in cell L12:L14, just format the cells showing NIL decimals places).