How to Post a Cumulative Rank for a Stock to Each Quarters Data

[Tsmarsco]

Hi, 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?

[#2]

@Tsmarsco 

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.

[#3]

@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.

[#4]

Thanks for the explanation. That makes sense now, of what otherwise seemed odd, or unnecessary.

[#5]

@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.)

[#6]

@Tsmarsco 

In VLOOKUP third parameter is column number from which you try to return the value. In row #4 the range is C3:D4, i.e. only two columns in it. Third parameter D4, which is 3, thus an error.

Alternatively

=INDEX($D$3:$D$4, MATCH( C4, $C$3:$C$4, 0) )

[#7 · Accepted solution]

@Tsmarsco 

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)

[#8]

@SergeiBaklan Sergei, Thanks, did you see my reply on Vlookup bug? 
In any case, I was very excited Index works on my tiny Sample. However, it does not on the real data.

I have 1-2956 stocks with Ranks (left), but for many quarterly

Appperiods so 101312 rows of data.

The Index is only picking up the same ROW from the Rank page. It's clear below.

[#9]

@SergeiBaklan I was very happy at first as the Index works fine if the Rows equal the Rank. But if I just needed row numbers I could populate it easily by posting the row # 1-2956 next to my stock. I need the Rank posted next to each quarter a stock shows profits. Here it works ok on my tiny sample only.

[#10]

@mathetes Eureka! As Archimedes said. The Vlookup works now. I just had to adjust my range. Both your explanations are better than the Vlookup code description:

If you are MS Staff, MS feedback: To be clearer, I would have phrased this:

(lookup_value,table_range,lookup_col_no,{0=exact, 1=approx))

Thanks!

[#11]

This one works!
=VLOOKUP(C2,$C$2:$D$4,2,0)

[#12]

@Tsmarsco 

I'm glad you were able to make the VLOOKUP work with my correction to it; for the record INDEX and MATCH as suggested by @SergeiBaklan would have worked too, given the right use of the several variables (to include the address of the range or array being searched). Those are all very powerful functions; there are other ways to retrieve data from tables, with those being perhaps the most common. You'd do well to familiarize yourself with them, and I think you'll find this on-line reference a good resource for your further learning.