Formula Returning Tree With Largest Profit

[sacman]

I am posting a question using one of the examples from Excel's help files with this stipulation: I do not want a formula that requires the Control+Shift+Enter as those types have not been consistently accurate. I'm not sure what that type of formula is called.

Is there a formula or a combination of formulas that will identify the apple tree with the maximum profit in the following chart? The DMAX returns a value but I cannot locate a funtion that will identify the actual item (as a text) in a database.


Tree Profit
Apple A 105
Pear A 96
Cherry A 105
Apple B 75
Pear B 9 76.8
Apple C 45

Obviously the answer I want returned by the formula in this example would be Apple-A but the application for this is much larger. I wanted to phrase my question in the simplest manner as I tend to overcomplicate things .

Sorry about the poorly written chart. I tried several ways to copy/paste but the result was always a concatinated mess of text.

Allen

 

[sacman]

ah, I missed the initial bit about the array formulas, though if the results were inaccurate, it was due to a poorly setup formula vs. any real accuracy issues with array formulas themselves.

Thanks.

So much to learn, so much to learn ... so little time.



Allen

 

[scottylad2]

this will rank the fruits according to the value, but ignores the ABorC non array though, maybe you can adapt

Excel Workbook
	F	G	H	I	J	K	L
51		A	B	C	D	E	F
52	1	Apple	A	105	1	Apple	A
53	2	Apple	B	75	2	Cherry	A
54	3	Apple	C	45	3	Pear	B
55	4	Cherry	A	105	1
56	5	Pear	A	76.8	2
57	6	Pear	B	96	1
Sheet1