Excel Formula question from excel newb

[svrem]

SO I have decided to teach myself excel and thought I was getting along great creating spreadsheets until I ran into a little snag with what I thought would be a simple formula. I have been beating my head on this for a day now and figured I would ask for assistance. Below is an example of what I am trying to do:

-- removed inline image ---


I am trying to make a formula so that the corresponding product in Column A is returned to cell B10/11 (they are merged) when a match is found for D9 (D9 is already set to max(D2:D7)). I tried using some of the lookup functions but the data in columns B and C is dynamic in that it changes automatically from a web feed every hour so I cant have it in ascending order. Cell B10 should return Oranges in this example but if Apples was more profitable in 2 hours it should change to apples automatically.

Any help would be appreciated.

 

[njimack]

Welcome to the board.
=INDEX(A2:A7,MATCH(D9,D2,D7,0))

 

[mikerickson]

Something like

=INDEX(A2:A7, MATCH(MAX(D2:D7), D2:D7, 0), 1)

should do what you want.

By the way, Merged Cells have evil consequences. For your situation, a horizontal alignment of CenterAcrossSelection would be a better approach.

 

[svrem]

Wow that was fast, Mike your formula worked perfectly. Tried yours njimack and it told me there were to many arguments, but thank you for trying to assist anyways. Thank for the quick response. I should have come here after the 1st hour of struggling :D .

 

[njimack]

Oops - it should have been =INDEX(A2:A7,MATCH(D9,D2:D7,0))

And I wholeheartedly second Mike's comment re Merged cells - the programmer who first dreamt them up is surely going to hell!

 

[mikerickson]

...
And I wholeheartedly second Mike's comment re Merged cells - the programmer who first dreamt them up is surely going to hell!

No, he's not. He just got a new job; designing the tax code.

 

[njimack]

No, he's not. He just got a new job; designing the tax code.

So he's finished designing the Office ribbon then?