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