VLOOKUP to automate the creation of a table in Excel

How do you go from this data set containing about 2,000 observations over a period of 10 years (aka panel data)

To this neat looking table?

The answer is: VLOOKUP. And it looks like this:

Let's look at cell C22 for Argentina. What this VLOOKUP command is telling Excel to do is to find the value in B22 (Argentina) in the 'INDEX OF ECON FREEDOM DATA 2000' tab (which is the tab where the data is stored) and then look at the range B1860-O2014 and give you the value on the 14th column. The FALSE statement at the end indicates that you want the EXACT value. If you look at the 1st snapshot on this blog entry you will notice that the O column or the 14th column contains the value "Rank". Then the neat looking table  or the 2nd snapshot in this entry presents 11 yearly rankings for 11 countries. Once you have done cell C22 for Argentina just copy and paste the VLOOKUP command, making sure that you press the "F4" key to anchor your data range.

This is it. To do this for subsequent years just go back to the tab where you have the data, apply a filter for the year you want to do and repeat the process. This data is publicly available at:

http://www.heritage.org/index/Explore.aspx?view=by-region-country-year