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 have one final piece to this project. Its a similar scenario just a different layout and I can't seem to reorganize the formula. Here is an example.

[TABLE="class: cms_table, width: 420"]
<tbody>[TR]
[TD][/TD]
[TD]Tom[/TD]
[TD]Bob[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]Item #0[/TD]
[TD]50[/TD]
[TD]15[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Item #1[/TD]
[TD]65[/TD]
[TD]16[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Item #2[/TD]
[TD]80[/TD]
[TD]17[/TD]
[TD]110[/TD]
[/TR]
[TR]
[TD]Item #3[/TD]
[TD]95[/TD]
[TD]18[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]Item #4[/TD]
[TD]110[/TD]
[TD]19[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]Item #5[/TD]
[TD]125[/TD]
[TD]20[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Item #6[/TD]
[TD]140[/TD]
[TD]21[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]Item #7[/TD]
[TD]155[/TD]
[TD]22[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]Item #8[/TD]
[TD]170[/TD]
[TD]23[/TD]
[TD]170[/TD]
[/TR]
[TR]
[TD]Item #9[/TD]
[TD]185[/TD]
[TD]24[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]Item #10[/TD]
[TD]200[/TD]
[TD]25[/TD]
[TD]190[/TD]
[/TR]
</tbody>[/TABLE]



Enter Criteria (input): Joe


Output: Largest number within Joe numbers. aka (190) but again show the Item name instead of value.

In this scenario there will never be any ties in values so I believe Caribeiro77 original formula from post #3 would work:
{=INDEX($B$2:$B$15,MATCH(LARGE(IF($A$2:$A$15=$E$2,$C$2:$C$15),1),$C$2:$C$15,0))}

I just need help reorganizing it since the criteria match has to be from a row and not a column.

In other words, what I want to accomplish is to be able to type in any names and the formula looks up the largest value only within that persons column and returns the item name.
 
Upvote 0
I have one final piece to this project. Its a similar scenario just a different layout and I can't seem to reorganize the formula. Here is an example.
[...]

[Table="width:, class:grid"][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][td]
H​
[/td][/tr]
[tr][td]
1​
[/td][td] [/td][td] Tom[/td][td] Bob[/td][td] Joe[/td][td][/td][td] Name[/td][td] bob[/td][td] Joe[/td][/tr]


[tr][td]
2​
[/td][td] Item #0[/td][td] 50[/td][td] 15[/td][td] 90[/td][td][/td][td][/td][td] 25[/td][td] 190[/td][/tr]


[tr][td]
3​
[/td][td] Item #1[/td][td] 65[/td][td] 16[/td][td] 100[/td][td][/td][td][/td][td] Item(s)[/td][td] Item(s)[/td][/tr]


[tr][td]
4​
[/td][td] Item #2[/td][td] 80[/td][td] 17[/td][td] 110[/td][td][/td][td] [/td][td] Item #7[/td][td] Item #10[/td][/tr]


[tr][td]
5​
[/td][td] Item #3[/td][td] 95[/td][td] 18[/td][td] 120[/td][td][/td][td][/td][td] Item #10[/td][td] [/td][/tr]


[tr][td]
6​
[/td][td] Item #4[/td][td] 110[/td][td] 19[/td][td] 130[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
7​
[/td][td] Item #5[/td][td] 125[/td][td] 20[/td][td] 140[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
8​
[/td][td] Item #6[/td][td] 140[/td][td] 21[/td][td] 150[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
9​
[/td][td] Item #7[/td][td] 155[/td][td] 25[/td][td] 160[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
10​
[/td][td] Item #8[/td][td] 170[/td][td] 23[/td][td] 170[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
11​
[/td][td] Item #9[/td][td] 185[/td][td] 24[/td][td] 180[/td][td][/td][td][/td][td] [/td][td] [/td][/tr]


[tr][td]
12​
[/td][td] Item #10[/td][td] 200[/td][td] 25[/td][td] 190[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


In G2 just enter and copy across:
Rich (BB code):
=MAX(INDEX($B$2:$D$12,0,MATCH(G$1,$B$1:$D$1,0)))

In G4 control+shift+enter, not just enter, copy across, and down:
Rich (BB code):
=IFERROR(INDEX($A$2:$A$12,SMALL(IF(INDEX($B$2:$D$12,0,MATCH(G$1,$B$1:$D$1,0))=G$2,
    ROW($A$2:$A$12)-ROW($A$2)+1),ROWS($G$4:G4))),"")
 
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