How to Convert Country Names to ISO Codes and Vice Versa in Excel？

Country names and ISO country codes are commonly used in customer databases, international sales reports, shipping records, and geographic analysis. However, one worksheet may contain full country names such as United States, while another system requires two-letter codes such as US or three-letter codes such as USA.

To convert country names to ISO codes in Excel, you can:

1. Use an Excel lookup formula with a country-code reference table.
2. Use the Convert Country Names and ISO Codes feature in Kutools for Excel to perform the conversion directly.

This guide explains how to convert country names to ISO Alpha-2 or Alpha-3 codes, as well as how to convert ISO codes back to full country names.

What Are ISO Country Codes?

ISO country codes are standardized abbreviations used to identify countries and territories.

• ISO2, or an alpha-2 code, consists of two letters, such as US, CA, or JP, and is widely used on websites, in databases and addresses, and for regional settings.
• ISO3, or an alpha-3 code, consists of three letters, such as USA, CAN, or JPN, and is generally easier to recognize in reports, international datasets, and data exchange systems.

The two most commonly used formats are:

Method 1: XLOOLKUP function (Excel 2021 and later versions / Office 365)

Using a formula is a practical choice when you want the converted results to update automatically whenever the source data changes.

However, formulas require a reference table containing the country names and their corresponding ISO codes.

XLOOKUP Syntax:

• lookup_value: The value you want to find.
• lookup_array: The cell range in which Excel searches for the lookup value.
• return_array: The range containing the result you want to return.
• [if_not_found]: Optional. Specifies the text or value to return when no match is found. If omitted, Excel returns #N/A.
• [match_mode]: Optional. Specifies the matching method:
  • 0 – Exact match; this is the default.
  • -1 – Exact match or next smaller value.
  • 1 – Exact match or next larger value.
  • 2 – Wildcard match.
• [search_mode]: Optional. Specifies the search direction:
  • 1 – Search from first to last; this is the default.
  • -1 – Search from last to first.
  • 2 – Binary search in ascending order.
  • -2 – Binary search in descending order.

Convert Country Names to ISO Codes

Supposing, I paste the reference table in cell A1:C250, and my data range in E1:F14, see screenshot:

Return ISO Alpha-2 Codes

Enter the following formula in G2:

=XLOOKUP(F2,$A$2:$A$250,$B$2:$B$250,"Not found")

Press Enter, and then drag the fill handle down to convert the remaining country names.

Return ISO Alpha-3 Codes

To return a three-letter ISO code, change the return range from column B to column C:

Enter the following formula in H2:

=XLOOKUP(F2,$A$2:$A$250,$C$2:$C$250,"Not found")

Press Enter, and then drag the fill handle down to convert the remaining country names.

Convert ISO Codes to Country Names

If your worksheet contains two-letter or three-letter ISO codes, such as US, GB, CAN, or DEU, you can convert them into full country names for easier reading and reporting.

Convert ISO Alpha-2 Codes to Country Names

When the source column contains two-letter codes, reverse the lookup and return ranges. Use this formula:

=XLOOKUP(F2,$B$2:$B$250,$A$2:$A$250,"Not found")

Press Enter, and then drag the fill handle down to fill the formula into the other cells. This time, Excel searches the Alpha-2 column and returns the corresponding full country name.

Convert ISO Alpha-3 Codes to Country Names

For three-letter country codes, use this formula:

=XLOOKUP(F2,$C$2:$C$250,$A$2:$A$250,"Not found")

Press Enter, and then drag the fill handle down to return the corresponding full country names.

Convert Either Alpha-2 or Alpha-3 Codes to Country Names

When a column may contain a mixture of two-letter and three-letter ISO codes, use two XLOOKUP functions with IFERROR:

=IFERROR(XLOOKUP(F2,$C$2:$C$250,$A$2:$A$250),
XLOOKUP(F2,$B$2:$B$250,$A$2:$A$250,"Not found"))

Press Enter, and then drag the fill handle down to convert the remaining ISO codes into country names.

Method 2: VLOOLKUP or INDEX and MATCH function (Older Excel versions)

XLOOKUP is not available in Excel 2016 or Excel 2019. In these versions, you can use VLOOKUP or INDEX and MATCH instead.

Convert Country Names to Codes with VLOOKUP

VLOOKUP can be used to convert country names to ISO codes because the country-name column is located on the left side of the reference table, allowing the function to search for each country name and return the corresponding ISO code from a column to its right.

VLOOKUP Syntax:

• lookup_value: The value you want to find.
• table_array: The table containing the lookup value and the result. The lookup value must be in the first column of this range.
• col_index_num: The column number in the table from which the result should be returned. The first column is 1, the second column is 2, and so on.
• [range_lookup]: Optional. Specifies the matching method:
  • FALSE or 0 – Returns an exact match.
  • TRUE or 1 – Returns an approximate match. The first column should be sorted in ascending order.

Return an Alpha-2 code

=VLOOKUP(F2,$A$2:$C$250,2,FALSE)

Press Enter, and then drag the fill handle down to return the corresponding Alpha-2 codes.

Return an Alpha-3 code

=VLOOKUP(F2,$A$2:$C$250,3,FALSE)

Press Enter, and then drag the fill handle down to return the corresponding Alpha-3 codes.

Convert ISO Codes to Country Names with INDEX and MATCH

Because VLOOKUP can only search the first column of the lookup range, converting codes back to country names requires rearranging the reference table or using INDEX and MATCH.

General INDEX and MATCH Formula:

• return_range: The range containing the result you want to return.
• lookup_value: The value you want to find.
• lookup_range: The range in which Excel searches for the lookup value.
• 0: Requires MATCH to find an exact match.

Convert Alpha-2 codes to country names

=INDEX($A$2:$A$250,MATCH(F2,$B$2:$B$250,0))

This formula searches for the ISO Alpha-2 code in F2 within column B, then returns the corresponding country name from column A.

Convert Alpha-3 codes to country names

=INDEX($A$2:$A$250,MATCH(F2,$C$2:$C$250,0))

This formula searches for the ISO Alpha-3 code in F2 within column C, then returns the corresponding country name from column A.

Convert either Alpha-2 or Alpha-3 codes to country names

If your data includes both Alpha-2 and Alpha-3 country codes, you can combine the INDEX and MATCH functions with IFERROR to return the corresponding country names. It can handle mixed codes such as US, CAN, DE, and JPN in the same column.

=IFERROR(INDEX($A$2:$A$250,MATCH(F2,$B$2:$B$250,0)),
INDEX($A$2:$A$250,MATCH(F2,$C$2:$C$250,0)))

This formula converts either an ISO Alpha-2 or ISO Alpha-3 code in cell F2 into its corresponding country name.

Advantages and Limitations of the Formula Method

Method 3: Convert Country Names and ISO Codes with Kutools for Excel

If you do not want to create a reference table or write lookup formulas, Kutools for Excel provides a dedicated Convert Country Names and ISO Codes feature.

It supports the following operations:

• Convert country names to ISO2 codes.
• Convert country names to ISO3 codes.
• Convert ISO2 or ISO3 codes to country names.

This method is especially useful for processing a long list of countries directly within the selected Excel range.

1. Select the cells containing the country names or ISO codes you want to convert.
2. Click Kutools > Content > Convert Country/Region Names and ISO Codes, see screenshot:

3. In the dialog box, select the appropriate conversion option based on the format of your source data and the result you want to obtain. (here, I will choose Convert ISO2/ISO3 Codes to Country Names)

4. Click OK to apply the conversion.

Excel Formulas vs. Kutools for Excel

The best method depends on your Excel version, formula experience, and whether you want dynamic results or a quick direct conversion.

• Use an Excel formula when the source data changes frequently and the results need to update automatically.
• Use Kutools for Excel when you need a quick conversion without creating a country-code table, maintaining lookup ranges, or troubleshooting formulas.

Frequently Asked Questions (FAQs)

Conclusion