Help with an formula to get the value that is highest present in a Product

[93arpan]

Hi,

I'm working on an interesting case where I'm trying to capture a value from a column that has highest composition in a product. In this instance, I have 3 Products named A, B, and C. Each Product has different ingredients in its
Product A has Rice, Milk Solids, and Xantham Gum, with 60% of Rice, 10% of Milk Solids and 40% of Xantham Gum
Similarly, Product B has only one ingredient named Milk Solids which is 100%
Product C has Sugar (30%), Palm Oil (60%), and Rice (10%).

I'm trying to categories a product based on ingredient. For example if Product A has 60% Rice which is highest compared to all the other ingredient, then give me "Rice" next to Product A everywhere. And, in the case of Product C give me Palm Oil which is the highest with 60%.

The problem that I'm facing is that Rice is getting repeated.

Can anyone please suggest an solution to it

Thanks,

 

[Sam_D_Ben]

May be something like this,

=INDEX($A$3:$A$9, MATCH(1, ($B$3:$B$9=$B3)*($C$3:$C$9=MAXIFS($C$3:$C$9, $B$3:$B$9, $B3)), 0))

 

[Peter_SSs]

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

I'm guessing that it is possible that two (or more) ingredients could be equal highest in a product (eg product D below)

25 02 20.xlsm
	B	C	D	E
3	Rice	Product A	60	Rice
4	Milk Solids	Product A	10	Rice
5	Xantham Gum	Product A	40	Rice
6	Milk Solids	Product B	100	Milk Solids
7	Sugar	Product C	30	Palm Oil
8	Palm Oil	Product C	60	Palm Oil
9	Rice	Product C	10	Palm Oil
10	Sugar	Product D	40	Sugar, Rice
11	Palm Oil	Product D	20	Sugar, Rice
12	Rice	Product D	40	Sugar, Rice
Highest present

Cell Formulas
Range	Formula
E3:E12	E3	=TEXTJOIN(", ",,FILTER(B$3:B$12,(C$3:C$12=C3)*(D$3:D$12=MAXIFS(D$3:D$12,C$3:C$12,C3))))