Wednesday, August 28, 2013

yogi_Given An Attribute Value Find The Matching Name Of Item From Another Sheet

                                          Google Spreadsheet   Post  #1348
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Aug 28, 2013
user Peter Nolan (!mydiscussions/docs/-y1Rvopvfbg)
extracting information from tables and creating sorted lists without using the sort feature
I've created a spreadsheet that has a stock portfolio listed along with the various attributes of the stocks in the portfolio i.e.: name, price, cost, fair market value, gain/loss, % of potfolio etc. On a separate worksheet in the same spreadsheet I'm trying to created a list to identify the stocks that represent the highest percentage of my portfolio, the one with the highest gains, the ones with the highest losses etc.. I can get that information but I can`t get the adjoining cells to link so that I know the stock's name. I've found the google sort feature has caused me problem where I've had to re-enter all my information again and doesn't work very well as it messes up my information. 

For instance, to list the stocks that represent the highest percentage of my portfolio(found in column D of worksheet A, I've done the following:

On worksheetB I would put the following formula referencing worksheetA

In WorksheetA the % of the portfolio is listed in column D(range D1:D120); The stock names are in cells C1:C120. The following formula in worksheetB gets me the highest and second highest % but how do I get the stock names to follow in the adjoining cells?

In cell B1 i put the following:  =large(worksheetA!d1:d120,1) This gives me the largest % in range d1:d120.
In cell B2 i put the following:  =large(worksheetA!d1:d120,2) This gives me the second largest % in range d1:d120.

How do I get the corresponding stock names into cells A1 and A2?