Try this...

Assuming your named range, Investment_Table, includes the first row of
column headers.

=INDEX(INDEX(Investment_Table,1,0),MATCH(MAX(ROI), ROI,0))

--
Biff
Microsoft Excel MVP


"Thomas M." wrote in message
...
Excel 2007

I have stock investment data in a simple spreadsheet. The spreadsheet is
laid out so that company ticker symbols run across the top row, and the
details associated with each investment (share purchased, buy and sell
prices, breakeven price, ROI, etc.) are listed down the column.

Just for fun I would like to create a formula that shows me the investment
with the greatest ROI. Toward that end, I added a row called ROI, added
formulas to calculate ROI on each investment, and then named the range. I
can use MAX(ROI) to return the highest ROI. I would like to enhance that
formula so that it pulls in the company name. I tried the following:

=HLOOKUP(MAX(ROI),Investment_Table,1)

That returns #N/A. I thought that since Investment_Table is an array that
maybe the formula needs to be entered as such, but that didn't make any
difference. So I did some reading on Excel's various lookup functions but
I just haven't had any luck. I know this is doable, in fact I think I did
something like this several years ago but I haven't been able to find that
file and I can't seem to recall how I solved the problem previously.

How can I create a formula that will pick up the maximum ROI and return
the ticker symbol in row 1 of the same column?

--Tom