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