Excel matching by country with corresponding data

I am taking a trade class in international relations. I download data to find correlations all the time with SPSS and the occasional scatter plot.

If I download a list of countries of the world with their corresponding GDP and then another list of say Import totals by country of the world.

How can I match them by country and keep the data corresponding to the appropriate country. I sorted them alphabetically, but that is not good enough because often there are 10 or so countries missing since, especially if you download one set of data from the World bank and another from the IMF.

How can I quickly have it sort the two data sets so that I can quickly delete the country that lacks a corresponding country without going down the list one country at a time, deleting the one with a missing country in the other column

Example
GDP total imports
USA 35,000 France 80,000
Chile 5,000 Hong Kong 50,000
France 30,000 Canada 100,000
xxx xxx
xxx xxxx


I attached an example that I am working on. It has one set of data with country and its corresponding GDP and an environmental Index (ESI) and then another se to of data with country and total agricultural imports. I want to match the countries and delete the countries that lack a corresponding one in the other data.

I would like to know how to do this function on my own because I run data all the time and would like to be able to sort them by country and run a scatter plot in under a few minutes if possible.

I would appreciate any help anyone can offer. It is much needed.

Hi,
Welcome to the forum

The standard approach is to use a VLOOKUP() function. So in D2 enter the following and copy down.

Please Login or Register  to view this content.

This will return all the Agricultural Export numbers from column F for the countries in column A. Where a country in col A doesn't exist in col E you get a #N/A. If you want you can refine the formula and use

Please Login or Register  to view this content.

but the first one is probably sufficient.

You can of course always do it the other way round and lookup up the countries from col E in col A and return columns B & C.

HTH

Thank you, but how do you determine what codes to enter in the line. Can you tell me the logic behind the formula so that I can create it on my own in other circumstances? Can you break down what each letter and number means and where it was derived from? I understand the A2 means A column, 2, but what is E$2: F$152,2,False) I understand what false must represent, but not the figures between A2 and False.

=VLOOKUP(A2,E$2:F$152,2,FALSE)

Hi,

The VLOOKUP() function is in my opinion, (and apart from the simple =SUM function), one of the most used and useful functions in the Excel set. Use it whenever you want to find a particular value in another table. The value you're looking up should always be in the first column of a table. The basic format is:

Please Login or Register  to view this content.

In your case the two lists of countries are in cols A and E. And the list of Agricultural Exports by country is in E2:F152. So the task is to find a country listed in column A in the first column (E) of the table range E2:F152, and return the Agricultural Exports value from that table, and set it alongside the appropriate country in column A.

The '2' means the second column in the table range you specify, i.e. column F from the range E2:F152

The last parameter FALSE is optional. If the list which you're looking up is already sorted by the first column, there's no need for it. However generally speaking it does no harm to specify it just in case the list isn't sorted. There are situations where you don't want the FALSE but rather than confuse matters I'll leave it at that for the moment.

HTH