Excel country code

Question

I have a worksheet (worksheet1) with country codes in the A column and country names in the B column. In an other worksheet (worksheet2) is a long list with the country names and other additional informations (in the same cell). How to show the country code in the B column of worksheet2 from the list in worksheet1 if the cell contains a specified country name. I have no idea which function(s) shall I use.

Answer 1

If you were prepared to switch the order of the columns in worksheet1 then VLOOKUP should serve.

Without changing the sequence the very slightly less easy INDEX/MATCH combination should serve.

There are hundreds, if not thousands, of examples of both on SO.

MATCH to look for the location in your country name column, relative to the top of your range, of the country name you choose and INDEX to take that relative location and return whatever is in there within your country code column.

If your country name is within other text in the same cell (ie a substring of a longer string) there is no option but to find some means to extract it first - there is no way to match a long string to a shorter one (though shorter to longer is possible).

Answer 2

If I've understood the question correctly, you have a list of countries and codes in your first sheet like this:-

And you want to do a lookup on a list of countries with additional information in your second sheet like this:-

You could try using a formula like the one shown. It does a 'FIND' with each of the countries in sheet1!b2:b5 in turn to see if any match with sheet2!a2. Then the MATCH statement picks out any which do from the resulting array, and the INDEX statement finds the corresponding country code in sheet1!a2:a5.

=IFERROR(INDEX(Sheet1!A$2:A$5,MATCH(TRUE,ISNUMBER(FIND(Sheet1!B$2:B$5,A2)),0)),"")

Must be entered as an array formula with Ctrl-Shift-Enter and pulled down as required.