Formula Help for three criterias

markmol

New Member
Joined
Jun 3, 2016
Messages
30
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.
 
I'm shure there's some better way to do it, but try:


Book1
ABCDEFGH
1OrganizationProduct NameTotal $CriteriaProduct TypeProduct Name
2AmericanAmerican oA6.564,13 €AmericanAAmerican o
3AmericanAmerican PcA4.324,52 €American Pc
4AmericanAmerican IncB3.647,23 €American CIB
5AmericanAmerican WashB523,00 €
6AmericanAmerican CIBA443,25 €
7FranklinFranklin 95654B453,24 €
8FranklinFranklin 4567C432,00 €
9FranklinFranklin I6A45,56 €
10FranklinFranklin 1A4,52 €
11FranklinFranklin 244A4,52 €
12StoneblackStoney 982A4.241,22 €
13StoneblackStoney5A45,67 €
14StoneblackStoney8B42,45 €
15StoneblackStoney1B1,11 €
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top