Formula Help for three criterias

[markmol]

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

[TABLE="width: 417"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Organization[/TD]
[TD]Product Name[/TD]
[TD]Total $[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American o[/TD]
[TD]$6,564,132[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American Inc[/TD]
[TD]$3,647,234[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American CIB[/TD]
[TD]$443,245[/TD]
[/TR]
[TR]
[TD]American [/TD]
[TD]American Wash[/TD]
[TD]$523[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Pc[/TD]
[TD]$4,324,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 1[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 244[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 4567[/TD]
[TD]$432[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin 95654[/TD]
[TD]$453,244[/TD]
[/TR]
[TR]
[TD]Franklin [/TD]
[TD]Franklin I6[/TD]
[TD]$45,555[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney5[/TD]
[TD]$45,665[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney 982[/TD]
[TD]$4,241,222[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney1[/TD]
[TD]$1,114[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney8[/TD]
[TD]$42,445[/TD]
[/TR]
</tbody>[/TABLE]



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...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD] Organization[/TD]
[TD] Product Name[/TD]
[TD] Product Type[/TD]
[TD] Total $[/TD]
[TD][/TD]
[TD] Top[/TD]
[TD] 3[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD] American[/TD]
[TD] American o[/TD]
[TD] A[/TD]
[TD] $6,564,132 [/TD]
[TD][/TD]
[TD][/TD]
[TD] american[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD] American[/TD]
[TD] American Inc[/TD]
[TD] A[/TD]
[TD] $3,647,234 [/TD]
[TD][/TD]
[TD][/TD]
[TD] A[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD] American[/TD]
[TD] American o[/TD]
[TD] B[/TD]
[TD] $544,444 [/TD]
[TD][/TD]
[TD][/TD]
[TD] $443,245 [/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD] American[/TD]
[TD] American Inc[/TD]
[TD] B[/TD]
[TD] $98,414,614 [/TD]
[TD][/TD]
[TD]Top Adjusted<strike></strike>
[/TD]
[TD] 5[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD] American[/TD]
[TD] American CIB[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] Product[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD] American[/TD]
[TD] American Dune[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American o[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD] American[/TD]
[TD] American Wash[/TD]
[TD] B[/TD]
[TD] $6 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American Inc[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD] American[/TD]
[TD] American Pc[/TD]
[TD] C[/TD]
[TD] $4,324,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American CIB[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD] American[/TD]
[TD] Lincoln Z3[/TD]
[TD] A[/TD]
[TD] $443,245 [/TD]
[TD][/TD]
[TD][/TD]
[TD] American Dune[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 1[/TD]
[TD] A[/TD]
[TD] $4,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD] Lincoln Z3[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 244[/TD]
[TD] A[/TD]
[TD] $4,523 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 4567[/TD]
[TD] A[/TD]
[TD] $432 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 95654[/TD]
[TD] A[/TD]
[TD] $453,244 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin I6[/TD]
[TD] A[/TD]
[TD] $45,555 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 1[/TD]
[TD] B[/TD]
[TD] $888,476 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 244[/TD]
[TD] B[/TD]
[TD] $6,476,126 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 4567[/TD]
[TD] B[/TD]
[TD] $4,362,167 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin 95654[/TD]
[TD] B[/TD]
[TD] $461,633 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD] Franklin[/TD]
[TD] Franklin I6[/TD]
[TD] B[/TD]
[TD] $5,456 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney 982[/TD]
[TD] A[/TD]
[TD] $4,241,222 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney1[/TD]
[TD] A[/TD]
[TD] $1,114 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney8[/TD]
[TD] A[/TD]
[TD] $42,445 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney 982[/TD]
[TD] B[/TD]
[TD] $5,566,113 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney1[/TD]
[TD] B[/TD]
[TD] $1,919,149,961 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD] Stoneblack[/TD]
[TD] Stoney8[/TD]
[TD] B[/TD]
[TD] $41,456,546 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.)