Data from one column to another - Matching names and values (complex)

Hello there!

I made my first thread yesterday and I must say you guys are really helpful! I've unfortunately encountered one more issue in my worksheet. My problem is pretty complex, at least for me. In the attached file you will find data for a number of stocks during 4 months. To describe it briefly; column A contain the stock names, B is the return in descending order (highest to lowest) while C is the portfolio name (see the formula for more description on how I "calculated it").

Now, my problem is that I can't seem to find a way how to bring future stock returns "back" in column D. My task is to evaluate the portfolio performances, i.e. I need to take the returns made in the following months and put them in column D. Say that stocks in the first month, 2000-01, has made different returns and based on this I placed them in portfolio 1, 2 or 3 with the highest performing stocks being in portfolio 1 etc. What I want to do now is to look at the following month returns, and place it in column D. This is hard since I have a huge worksheet to deal with and it would take me years to complete this manually.

So, in short, how do I take the following months' stock returns (for simplicity say the following month, so the 2000-02 data goes to column D of 2000-01 etc) and transfer it to column D so that they match the stock names and appear on the same row as their names? Since the returns change from month to month a stock that placed at the top one month may be at the bottom the next month, and so on. As you can imagine, it would be quite painful to do this manually

Appreciate any kind of help! You guys rock!

A vlookup or offset/match combo should work, but it's not working when I try it on your sheet. It's because of the use of the tilde character (~). Does it need to be that character or could they be replaced with a hyphen (-), for example?

I'm not sure why it is not working. If the hyphen works for you, go for it. I have no preferences what so ever as long as I get some understanding of how I can solve it.

I did a Replace (Ctrl+H), finding "~~" and replacing it with "-" on the data (it needs the two tilde's in the find field as it is a special character in Excel).

I then put this in D2 and dragged it down. It can also be copied to columns H, L and P, or subsequently every fourth column:

Formula:

Please Login or Register  to view this content.

I have also revised your formulas for the Portfolio allocations to

Formula:

Please Login or Register  to view this content.

(in cell C2). With the COUNT and COUNTA you don't also need the sum.