Formula Help for three criterias

[markmol]

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

Organization	Product Name	Total $
American	American o	$6,564,132
American	American Inc	$3,647,234
American	American CIB	$443,245
American	American Wash	$523
American	American 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
Stoneblack	Stoney5	$45,665
Stoneblack	Stoney 982	$4,241,222
Stoneblack	Stoney1	$1,114
Stoneblack	Stoney8	$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.

 

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

	Tom	Bob	Joe
Item #0	50	15	90
Item #1	65	16	100
Item #2	80	17	110
Item #3	95	18	120
Item #4	110	19	130
Item #5	125	20	140
Item #6	140	21	150
Item #7	155	22	160
Item #8	170	23	170
Item #9	185	24	180
Item #10	200	25	190

<tbody>
</tbody>

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

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​		Tom	Bob	Joe		Name	bob	Joe
2​	Item #0	50	15	90			25	190
3​	Item #1	65	16	100			Item(s)	Item(s)
4​	Item #2	80	17	110			Item #7	Item #10
5​	Item #3	95	18	120			Item #10
6​	Item #4	110	19	130
7​	Item #5	125	20	140
8​	Item #6	140	21	150
9​	Item #7	155	25	160
10​	Item #8	170	23	170
11​	Item #9	185	24	180
12​	Item #10	200	25	190

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.