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.

 

[markmol]

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.

 

[Aladin Akyurek]

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:

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

=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))),"")

 

[63falcondude]

http://www.mrexcel.com/forum/excel-questions/946319-formula-help.html#post4546215

 

[markmol]

Aldain your always right on point. Thanks man!

 

[Aladin Akyurek]

This is gold. Thanks so much for being patient with me and helping out, learned a ton just from this formula.

Aldain your always right on point. Thanks man!

Glad to help.