Formula Help for three criterias

[markmol]

I was hoping someone could help me with a formula based on a certain scenario.

Organization	Product Name	Total $
American	American o	$6,564,132
American	American Inc	$3,647,234
American	American CIB	$443,245
American	American Wash	$523
American	American Pc	$4,324,523
Franklin	Franklin 1	$4,523
Franklin	Franklin 244	$4,523
Franklin	Franklin 4567	$432
Franklin	Franklin 95654	$453,244
Franklin	Franklin I6	$45,555
Stoneblack	Stoney5	$45,665
Stoneblack	Stoney 982	$4,241,222
Stoneblack	Stoney1	$1,114
Stoneblack	Stoney8	$42,445

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Based on the example above, I would like to write a formula that spits back the number 1 'Product Name' for each 'Organization' based on column 'Total $'.

So, in other words, IF column 'Organization'=American then give me back the largest value in column 'Total $' but return the 'Product Name' as the final output.

My initial swing at this formula was to combine a 'IF' function with the 'INDEX & MATCH' functions and 'large' function in order to give me back the top product name for each organization but I am not having much luck. Any help and insight is much appreciated. Thank you!

Please let me know if you need any clarification.

 

[Caribeiro77]

I'm shure there's some better way to do it, but try:

Book1
	A	B	C	D	E	F	G	H
1	Organization	Product Name		Total $		Criteria	Product Type	Product Name
2	American	American o	A	6.564,13 €		American	A	American o
3	American	American Pc	A	4.324,52 €				American Pc
4	American	American Inc	B	3.647,23 €				American CIB
5	American	American Wash	B	523,00 €
6	American	American CIB	A	443,25 €
7	Franklin	Franklin 95654	B	453,24 €
8	Franklin	Franklin 4567	C	432,00 €
9	Franklin	Franklin I6	A	45,56 €
10	Franklin	Franklin 1	A	4,52 €
11	Franklin	Franklin 244	A	4,52 €
12	Stoneblack	Stoney 982	A	4.241,22 €
13	Stoneblack	Stoney5	A	45,67 €
14	Stoneblack	Stoney8	B	42,45 €
15	Stoneblack	Stoney1	B	1,11 €
Sheet1

Cell Formulas
Range	Formula
H2	{=INDEX($B$2:$B$15,MATCH(LARGE(IF($A$2:$A$15=$F$2,IF($C$2:$C$15=$G$2,$D$2:$D$15)),ROWS($H$2:H2)-ROWS($H$2)+1),$D$2:$D$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fishboy]

Ranked results? What I suggested is a processing that is complete, based on conditional max dollar value.

My bad!

I totally misread your example table and assumed (without checking afterwards) that American Dune was just the "second highest", and not "equal first".

Apologies for any confusion my comment may have created.

 

[markmol]

AM
Caribeiro,

That formula works for me! Everyone thanks for your help on this one.

 

[Aladin Akyurek]

AM
Caribeiro,

That formula works for me! Everyone thanks for your help on this one.

Shouldn't the output follow the data. Consider...

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Organization	Product Name	Product Type	Total $		Top	3
2​	American	American o	A	$6,564,132			american
3​	American	American Inc	A	$3,647,234			A
4​	American	American o	B	$544,444			$443,245
5​	American	American Inc	B	$98,414,614		Top Adjusted<strike></strike>	5
6​	American	American CIB	A	$443,245			Product
7​	American	American Dune	A	$443,245			American o
8​	American	American Wash	B	$6			American Inc
9​	American	American Pc	C	$4,324,523			American CIB
10​	American	Lincoln Z3	A	$443,245			American Dune
11​	Franklin	Franklin 1	A	$4,523			Lincoln Z3
12​	Franklin	Franklin 244	A	$4,523
13​	Franklin	Franklin 4567	A	$432
14​	Franklin	Franklin 95654	A	$453,244
15​	Franklin	Franklin I6	A	$45,555
16​	Franklin	Franklin 1	B	$888,476
17​	Franklin	Franklin 244	B	$6,476,126
18​	Franklin	Franklin 4567	B	$4,362,167
19​	Franklin	Franklin 95654	B	$461,633
20​	Franklin	Franklin I6	B	$5,456
21​	Stoneblack	Stoney 982	A	$4,241,222
22​	Stoneblack	Stoney1	A	$1,114
23​	Stoneblack	Stoney8	A	$42,445
24​	Stoneblack	Stoney 982	B	$5,566,113
25​	Stoneblack	Stoney1	B	$1,919,149,961
26​	Stoneblack	Stoney8	B	$41,456,546

<tbody>
</tbody>

Would you care to comment?

 

[markmol]

.

 

[markmol]

I'm not sure I fully understand your question. Could you elaborate?

 

[Aladin Akyurek]

I'm not sure I fully understand your question. Could you elaborate?

Post #14 shows a Top 3 list. Pay attention to the fact the result list shows 5 products... correctly, given the input.

 

[Aladin Akyurek]

I'm not sure I fully understand your question. Could you elaborate?

Another attempt. Post #14 is an attempt to show INDEX/LARGE combo is not sufficient to obtain correct results. Something amore elaborate is required to gather the results shown in this post. If you are not interested in that, it's okay too.

 

[markmol]

The formula that caribeiro posted in post #11 did the trick for me, so i'm all set. Thanks.

 

[Aladin Akyurek]

The formula that caribeiro posted in post #11 did the trick for me, so i'm all set. Thanks.

You are not.

Change 443,25 € for American CIB to 4.324,52 € in post #11. Observe what you get. (No offense meant.)