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.

OrganizationProduct NameTotal $
AmericanAmerican o$6,564,132
American American Inc$3,647,234
American American CIB$443,245
American American Wash$523
AmericanAmerican 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
StoneblackStoney5$45,665
StoneblackStoney 982$4,241,222
StoneblackStoney1$1,114
StoneblackStoney8$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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
I was hoping someone could help me with a formula based on a certain scenario.

OrganizationProduct NameTotal $
AmericanAmerican o$6,564,132
AmericanAmerican Inc$3,647,234
AmericanAmerican CIB$443,245
AmericanAmerican Wash$523
AmericanAmerican Pc$4,324,523
FranklinFranklin 1$4,523
FranklinFranklin 244$4,523
FranklinFranklin 4567$432
FranklinFranklin 95654$453,244
FranklinFranklin I6$45,555
StoneblackStoney5$45,665
StoneblackStoney 982$4,241,222
StoneblackStoney1$1,114
StoneblackStoney8$42,445

<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.
Hi markmol, welcome to the boards.

Assuming your data starts in A2 (headers starting in A1), then try the following array formulas (entered with CTRL+SHIFT+ENTER, not just ENTER):

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="American",$C$2:$C$15)),$C$2:$C$15,0))

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="Franklin",$C$2:$C$15)),$C$2:$C$15,0))

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="Stoneblack",$C$2:$C$15)),$C$2:$C$15,0))
 
Last edited:

Caribeiro77

Well-known Member
Joined
Sep 24, 2010
Messages
1,261
If for some resason you want to change the "Organisation"criteria you can assign a cell to it and then put the formula bellow..

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Organization</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Product Name</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Total $</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Criteria</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Product Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American o</td><td style="text-align: right;;">6.564,13 €</td><td style="text-align: right;;"></td><td style="color: #333333;;">Stoneblack</td><td style=";">Stoney 982</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Inc</td><td style="text-align: right;;">3.647,23 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American CIB</td><td style="text-align: right;;">443,25 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Wash</td><td style="text-align: right;;">523,00 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Pc</td><td style="text-align: right;;">4.324,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 1</td><td style="text-align: right;;">4,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 244</td><td style="text-align: right;;">4,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 4567</td><td style="text-align: right;;">432,00 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 95654</td><td style="text-align: right;;">453,24 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin I6</td><td style="text-align: right;;">45,56 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney5</td><td style="text-align: right;;">45,67 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney 982</td><td style="text-align: right;;">4.241,22 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney1</td><td style="text-align: right;;">1,11 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney8</td><td style="text-align: right;;">42,45 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$2:$B$15,MATCH(<font color="Red">LARGE(<font color="Green">IF(<font color="Purple">$A$2:$A$15=$E$2,$C$2:$C$15</font>),1</font>),$C$2:$C$15,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

markmol

New Member
Joined
Jun 3, 2016
Messages
30
Hi markmol, welcome to the boards.

Assuming your data starts in A2 (headers starting in A1), then try the following array formulas (entered with CTRL+SHIFT+ENTER, not just ENTER):

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="American",$C$2:$C$15)),$C$2:$C$15,0))

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="Franklin",$C$2:$C$15)),$C$2:$C$15,0))

=INDEX($B$2:$B$15,MATCH(MAX(IF($A$2:$A$15="Stoneblack",$C$2:$C$15)),$C$2:$C$15,0))

Fishboy,

You are the man! Thanks for saving a bunch of my time. It is exactly what I've been trying to figure out.

One more thing, if I wanted to see the 2nd and 3rd top product, could I just substitute the MAX function with the LARGE function?

Thanks alot for your help!!
 

markmol

New Member
Joined
Jun 3, 2016
Messages
30
Caribeiro77,

Thanks for the replay! Awesome, I'm all set thanks for your help.
 

Caribeiro77

Well-known Member
Joined
Sep 24, 2010
Messages
1,261
IF you want you can give this a try... Put the formula in F2 and drag down..

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Organization</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Product Name</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Total $</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Criteria</td><td style="font-weight: bold;text-decoration: underline;color: #333333;;">Product Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American o</td><td style="text-align: right;;">6.564,13 €</td><td style="text-align: right;;"></td><td style="color: #333333;;">Stoneblack</td><td style=";">Stoney 982</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Inc</td><td style="text-align: right;;">3.647,23 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Stoney5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American CIB</td><td style="text-align: right;;">443,25 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Stoney8</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Wash</td><td style="text-align: right;;">523,00 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Stoney1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;;">American</td><td style="color: #333333;;">American Pc</td><td style="text-align: right;;">4.324,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 1</td><td style="text-align: right;;">4,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 244</td><td style="text-align: right;;">4,52 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 4567</td><td style="text-align: right;;">432,00 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin 95654</td><td style="text-align: right;;">453,24 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="color: #333333;;">Franklin</td><td style="color: #333333;;">Franklin I6</td><td style="text-align: right;;">45,56 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney5</td><td style="text-align: right;;">45,67 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney 982</td><td style="text-align: right;;">4.241,22 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney1</td><td style="text-align: right;;">1,11 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="color: #333333;;">Stoneblack</td><td style="color: #333333;;">Stoney8</td><td style="text-align: right;;">42,45 €</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$2:$B$15,MATCH(<font color="Red">LARGE(<font color="Green">IF(<font color="Purple">$A$2:$A$15=$E$2,$C$2:$C$15</font>),ROWS(<font color="Purple">$F$2:F2</font>)-ROWS(<font color="Purple">$F$2</font>)+1</font>),$C$2:$C$15,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,176
Row\Col
A​
B​
C​
F​
G​
H​
1​
Organization Product Name Total $ Organization Product
2​
American American o $6,564,132 American American o American Dune
3​
American American Inc $3,647,234 Franklin Franklin 95654
4​
American American CIB $443,245 Stoneblack Stoney 982
5​
American American Wash $523
6​
American American Dune $6,564,132
7​
American American Pc $4,324,523
8​
Franklin Franklin 1 $4,523
9​
Franklin Franklin 244 $4,523
10​
Franklin Franklin 4567 $432
11​
Franklin Franklin 95654 $453,244
12​
Franklin Franklin I6 $45,555
13​
Stoneblack Stoney5 $45,665
14​
Stoneblack Stoney 982 $4,241,222
15​
Stoneblack Stoney1 $1,114
16​
Stoneblack Stoney8 $42,445

In G2 control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$F2,
    IF($C$2:$C$16=MAX(IF($A$2:$A$16=$F2,$C$2:$C$16)),ROW($B$2:$B$16)-ROW($B$2)+1)),
    COLUMNS($G2:G2))),"")
 

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
Fishboy,

You are the man! Thanks for saving a bunch of my time. It is exactly what I've been trying to figure out.

One more thing, if I wanted to see the 2nd and 3rd top product, could I just substitute the MAX function with the LARGE function?

Thanks alot for your help!!
Happy to help.

It looks as if both Caribeiro77 and Aladin have suggested viable options for ranked results.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,176
Happy to help.

It looks as if both Caribeiro77 and Aladin have suggested viable options for ranked results.
Ranked results? What I suggested is a processing that is complete, based on conditional max dollar value.
 

markmol

New Member
Joined
Jun 3, 2016
Messages
30
Wow guys, this is even better!! So i'll try to take this one step further and see if it is possible. Building off what we did here, can I add another criteria?

OrganizationProduct NameProduct TypeTotal $
AmericanAmerican oA$6,564,132
AmericanAmerican IncA$3,647,234
AmericanAmerican oB$544,444
AmericanAmerican IncB$98,414,614
AmericanAmerican CIBA$443,245
AmericanAmerican WashB$6
AmericanAmerican PcC$4,324,523
FranklinFranklin 1A$4,523
FranklinFranklin 244A$4,523
FranklinFranklin 4567A$432
FranklinFranklin 95654A$453,244
FranklinFranklin I6A$45,555
FranklinFranklin 1B$888,476
FranklinFranklin 244B$6,476,126
FranklinFranklin 4567B$4,362,167
FranklinFranklin 95654B$461,633
FranklinFranklin I6B$5,456
StoneblackStoney 982A$4,241,222
StoneblackStoney1A$1,114
StoneblackStoney8A$42,445
StoneblackStoney 982B$5,566,113
StoneblackStoney1B$1,919,149,961
StoneblackStoney8B$41,456,546

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>



So here I would like to get the top 3 products for each organization per each product type. Again in other words, IF 'Organization'= American and 'Product type'= A then return largest 'product name' based on 'Total $' column.


Thanks so much in advance!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,857
Messages
5,465,099
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top