Formula Help for three criterias

M

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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
markmol said:
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.
Click to expand...
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:
Upvote 0
If for some resason you want to change the "Organisation"criteria you can assign a cell to it and then put the formula bellow..


Book1
ABCDEF
1OrganizationProduct NameTotal $CriteriaProduct Name
2AmericanAmerican o6.564,13 €StoneblackStoney 982
3AmericanAmerican Inc3.647,23 €
4AmericanAmerican CIB443,25 €
5AmericanAmerican Wash523,00 €
6AmericanAmerican Pc4.324,52 €
7FranklinFranklin 14,52 €
8FranklinFranklin 2444,52 €
9FranklinFranklin 4567432,00 €
10FranklinFranklin 95654453,24 €
11FranklinFranklin I645,56 €
12StoneblackStoney545,67 €
13StoneblackStoney 9824.241,22 €
14StoneblackStoney11,11 €
15StoneblackStoney842,45 €
Sheet1
Cell Formulas
RangeFormula
F2{=INDEX($B$2:$B$15,MATCH(LARGE(IF($A$2:$A$15=$E$2,$C$2:$C$15),1),$C$2:$C$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Fishboy said:
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))
Click to expand...


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!!
 
Upvote 0
IF you want you can give this a try... Put the formula in F2 and drag down..


Book1
ABCDEF
1OrganizationProduct NameTotal $CriteriaProduct Name
2AmericanAmerican o6.564,13 €StoneblackStoney 982
3AmericanAmerican Inc3.647,23 €Stoney5
4AmericanAmerican CIB443,25 €Stoney8
5AmericanAmerican Wash523,00 €Stoney1
6AmericanAmerican Pc4.324,52 €
7FranklinFranklin 14,52 €
8FranklinFranklin 2444,52 €
9FranklinFranklin 4567432,00 €
10FranklinFranklin 95654453,24 €
11FranklinFranklin I645,56 €
12StoneblackStoney545,67 €
13StoneblackStoney 9824.241,22 €
14StoneblackStoney11,11 €
15StoneblackStoney842,45 €
Sheet1
Cell Formulas
RangeFormula
F2{=INDEX($B$2:$B$15,MATCH(LARGE(IF($A$2:$A$15=$E$2,$C$2:$C$15),ROWS($F$2:F2)-ROWS($F$2)+1),$C$2:$C$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
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))),"")
 
Upvote 0
markmol said:
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!!
Click to expand...
Happy to help.

It looks as if both Caribeiro77 and Aladin have suggested viable options for ranked results.
 
Upvote 0
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!!!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,516
Messages
6,119,981
Members
448,934
Latest member
audette89

Share this page

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