Excel国家代码 [英] Excel country code
问题描述
我不知道我应该使用哪个功能。
如果我已经正确地理解了这个问题您可以在第一张表中列出国家/地区和代码: -
=在这里输入图像描述>
您可以尝试使用所显示的公式。它与sheet1!b2:b5中的每个国家进行查找,依次查看是否与sheet2!a2匹配。然后,MATCH语句从结果数组中选出任何一个,INDEX语句在sheet1!a2中找到相应的国家代码:a5。
= IFERROR(INDEX(Sheet1!A $ 2:A $ 5,MATCH(TRUE,ISNUMBER(FIND(Sheet1!B $ 2:B $ 5,A2)),0)),)
必须使用Ctrl-Shift-Enter作为数组公式输入,并根据需要下拉。
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.
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.
这篇关于Excel国家代码的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!