Forum Discussion
How to Post a Cumulative Rank for a Stock to Each Quarters Data
As SergeiBaklan has said, the third parameter in your use of VLOOKUP is the error. It should be consistently the number 2, i.e., the second column in the table in which you're seeking the corresponding rank of the company.
=VLOOKUP(C2,$C$2:$D$4,2,0)
I have 2000 stocks with quarter profits data by row for 26 years. I have summed up each firm total profits for all years on a Pivot table and got a Rank for them, e.g. Walmart is No. 1, Exxon no. 2. How do I get the Rank posted next to each quarter of data on my detailed excel page?
As someone who also likes to analyze my investment results, likes also to use Pivot Tables for purposes like the summary you're describing, I find myself wanting to answer your question with "Why? Why would you need or want to do that?" That's why you use the Pivot Table in the first place. You can always update it with next quarter's data and see the rankings there again--which might change from last quarter's summary.
That said, if you persist in wanting to do that adding a column to your raw data file, I should think an XLOOKUP referring to the stock symbol as the thing to be searched for, the Pivot Table as the table to be searched. Come to think of it, maybe just use MATCH, which would return the row in the Pivot Table where the stock symbol is found in the ranking.
mathetes Thanks I will try it.
As you are curious, I am doing economic statistical research and I need to feed the software with time series data by rank to prove my thesis as to characteristics of firms that are more successful. It's very involved but the software requires I identify each data point with its related ranking firm.
- Thanks for the explanation. That makes sense now, of what otherwise seemed odd, or unnecessary.
mathetes Thank you again. However, I used Vlookup and it's not working. Not sure what I am doing wrong. Can you see any error in my formula, if you don't mind? Here are the odd results from a tiny sample test. In Column A it works only for Exxon, whose Rank is 2 (from Col D). But it refuses to pick up col D rank for Walmart or GE? Weird. (Note: In the second set of 3 I sorted stock names in alpha order. No luck.)
As SergeiBaklan has said, the third parameter in your use of VLOOKUP is the error. It should be consistently the number 2, i.e., the second column in the table in which you're seeking the corresponding rank of the company.
=VLOOKUP(C2,$C$2:$D$4,2,0)