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.

 

[Fishboy]

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

[TABLE="width: 417"]
<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.

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

 

[Caribeiro77]

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

Cell Formulas
Range	Formula
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.

 

[markmol]

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]

Caribeiro77,

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

 

[Caribeiro77]

IF you want you can give this a try... Put the formula in F2 and drag down..

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

Cell Formulas
Range	Formula
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.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td] Organization[/td][td] Product Name[/td][td] Total $[/td][td] Organization[/td][td] Product[/td][td][/td][/tr]


[tr][td]

2​

[/td][td] American[/td][td] American o[/td][td] $6,564,132 [/td][td] American[/td][td] American o[/td][td] American Dune[/td][/tr]


[tr][td]

3​

[/td][td] American[/td][td] American Inc[/td][td] $3,647,234 [/td][td] Franklin[/td][td] Franklin 95654[/td][td][/td][/tr]


[tr][td]

4​

[/td][td] American[/td][td] American CIB[/td][td] $443,245 [/td][td] Stoneblack[/td][td] Stoney 982[/td][td][/td][/tr]


[tr][td]

5​

[/td][td] American[/td][td] American Wash[/td][td] $523 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td] American[/td][td] American Dune[/td][td] $6,564,132 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td] American[/td][td] American Pc[/td][td] $4,324,523 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td] Franklin[/td][td] Franklin 1[/td][td] $4,523 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td] Franklin[/td][td] Franklin 244[/td][td] $4,523 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td] Franklin[/td][td] Franklin 4567[/td][td] $432 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td] Franklin[/td][td] Franklin 95654[/td][td] $453,244 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td] Franklin[/td][td] Franklin I6[/td][td] $45,555 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td] Stoneblack[/td][td] Stoney5[/td][td] $45,665 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td] Stoneblack[/td][td] Stoney 982[/td][td] $4,241,222 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

15​

[/td][td] Stoneblack[/td][td] Stoney1[/td][td] $1,114 [/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

16​

[/td][td] Stoneblack[/td][td] Stoney8[/td][td] $42,445 [/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G2 control+shift+enter, not just enter, copy across, and down:

=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]

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]

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]

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?

[TABLE="width: 472"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Organization[/TD]
[TD]Product Name[/TD]
[TD]Product Type[/TD]
[TD]Total $[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American o[/TD]
[TD]A[/TD]
[TD]$6,564,132[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Inc[/TD]
[TD]A[/TD]
[TD]$3,647,234[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American o[/TD]
[TD]B[/TD]
[TD]$544,444[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Inc[/TD]
[TD]B[/TD]
[TD]$98,414,614[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American CIB[/TD]
[TD]A[/TD]
[TD]$443,245[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Wash[/TD]
[TD]B[/TD]
[TD]$6[/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]American Pc[/TD]
[TD]C[/TD]
[TD]$4,324,523[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 1[/TD]
[TD]A[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 244[/TD]
[TD]A[/TD]
[TD]$4,523[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 4567[/TD]
[TD]A[/TD]
[TD]$432[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 95654[/TD]
[TD]A[/TD]
[TD]$453,244[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin I6[/TD]
[TD]A[/TD]
[TD]$45,555[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 1[/TD]
[TD]B[/TD]
[TD]$888,476[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 244[/TD]
[TD]B[/TD]
[TD]$6,476,126[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 4567[/TD]
[TD]B[/TD]
[TD]$4,362,167[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin 95654[/TD]
[TD]B[/TD]
[TD]$461,633[/TD]
[/TR]
[TR]
[TD]Franklin[/TD]
[TD]Franklin I6[/TD]
[TD]B[/TD]
[TD]$5,456[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney 982[/TD]
[TD]A[/TD]
[TD]$4,241,222[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney1[/TD]
[TD]A[/TD]
[TD]$1,114[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney8[/TD]
[TD]A[/TD]
[TD]$42,445[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney 982[/TD]
[TD]B[/TD]
[TD]$5,566,113[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney1[/TD]
[TD]B[/TD]
[TD]$1,919,149,961[/TD]
[/TR]
[TR]
[TD]Stoneblack[/TD]
[TD]Stoney8[/TD]
[TD]B[/TD]
[TD]$41,456,546[/TD]
[/TR]
</tbody>[/TABLE]



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