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