Finding multiple data values in excel

Microsoft Office excel 2007 home & stude...
April 30, 2011 at 21:35:21
Specs: Windows 7, 2.4Ghz/4Gb
I am really badly stuck at this and am a rookie on excel. I am building portfolios based on Market values(MV) and then multiplying those MV's with returns calculated from Price Index (PI). Iv got approx 300 companies, out of which I have to make 6 portfolios for each year comprising of 20-30 companies out of those 300. The company names are in in the top row and the MV values follow in columns. same happens with the returns which is, as i said, function of the PI values, on a different sheet. So now i need to search for those portfolio companies one by one and then copy paste the MV values first and then repeat the whole process to get the return values from PI sheet, then multiply them together and find the total portfolio return. I have uploaded the file on zippyshare

In this file you will see 2004 portfolio where i have actually made the first "BH" portfolio by doing what iv mentioned, when ull scroll down ull see another portfolio "BM" where the companies are listed.
Is there an easier way to search through the 300 companies to retain the required companies and their corresponding yearly values and filter out the rest? and same could be done for returns?

THANKS a million in advance

See More: Finding multiple data values in excel

Report •

May 1, 2011 at 07:19:55
I'm not if this is what you are looking, so let me know...

In 2004 Portfolio!B2 you have a value of 6013.42 which I assume came from MV 2004-2005!KF2.

This formula will return that value from MV 2004-2005 using the INDEX and MATCH functions.

The first thing I did was turn MV 2004-2005 into a Named Range (MyData). I then used the following formula to return 6013.42 into 2004 Portfolio!B2

The MATCH functions will return the row_num of the value in 2004 Portfolio!A2 and column_num of the value in 2004 Portfolio!B1 based on where they are found in MyData (MV 2004-2005).

It will then use those row_num and column_num values in the INDEX function to return the value found at that intersection.

=INDEX(MyData,MATCH(A2,'MV 2004-2005'!A:A,0),MATCH(B1,'MV 2004-2005'!1:1,0))

Hope that helps!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question