Function Problems

How to Show Country Descriptions While Inserting Codes in Excel

Maira Mehtab Sep 22, 2026 870 views

Question details

The user wants to display full country names (e.g., United Kingdom) to help with data entry, but only wants to store the two-letter country codes (e.g., GB) in the input cell.

Product: Excel
Device & OS: not provided
Scenario: Setting up a data entry form or spreadsheet where users select a country code from a drop-down, requiring the full country name to be visible for clarity.
Observed behavior: Excel Data Validation lists normally display and insert only one value into a cell. They do not natively support displaying a descriptive label while inserting a different underlying code in the exact same cell without complex workarounds.

Before you start

Create a complete two-column lookup table in a separate sheet or off to the side, with your two-letter country codes in the first column and the full country descriptions in the second column.

Solution 1Recommended

Use Data Validation and XLOOKUP in an Adjacent Cell

The standard approach is to use Data Validation for the code input and a lookup formula in the adjacent cell to instantly display the description.

Since standard Data Validation cannot show a description and insert a separate code into a single cell simultaneously, the most reliable method is to separate the input and the description. You can create a drop-down list for the codes and use the XLOOKUP function to pull the description into the next column.

Set Up the Lookup Table

In an empty area of your workbook, create a table with two columns. Name the first column 'Code' (e.g., GB, US) and the second column 'Description' (e.g., United Kingdom, United States).

Apply Data Validation

Select the cell where you want users to enter the country code (e.g., cell A2). Go to the Data tab, click Data Validation, select 'List' from the Allow menu, and choose the 'Code' column from your lookup table as the Source.

Insert the XLOOKUP Formula

In the cell directly adjacent to the input cell (e.g., cell B2), enter the formula: =XLOOKUP(A2, LookupTable[Code], LookupTable[Description], ""). Adjust the table references to match your actual data ranges.

Test the Drop-down

Click on the input cell, select a two-letter country code from the drop-down menu, and verify that the full country description automatically appears in the adjacent cell.

Alternative Function: If you are using an older version of Excel that does not support XLOOKUP, you can use VLOOKUP instead: =IFERROR(VLOOKUP(A2, LookupTable, 2, FALSE), "").

Advanced Spreadsheet Functions

Easily Handle Lookups and Data Validation with WPS Spreadsheet

WPS Spreadsheet provides powerful data validation and lookup functions, including XLOOKUP, VLOOKUP, and INDEX/MATCH, making it simple to build intuitive data entry forms.

1. Create Your Lookup Range: Open a new or existing file in WPS Spreadsheet and type out your country codes and descriptions into two adjacent columns.
2. Configure Data Validation: Select your target input cell, navigate to the 'Data' tab, click 'Validation', and set the criteria to 'List' using your country codes.
3. Apply the XLOOKUP Formula: In the cell next to your drop-down, type the XLOOKUP formula referencing your input cell and your lookup columns to automatically pull the full country name.

Fully compatible with Microsoft Excel (.xlsx) formulas and formatting.Supports modern lookup functions like XLOOKUP for efficient data handling.Lightweight, fast, and completely free for everyday spreadsheet tasks.User-friendly interface for quickly managing Data Validation drop-down lists.

microsoft office alternative - wps office

Frequently Asked Questions

Can I show the description in the drop-down but only insert the code in the same cell?

Native Data Validation in Excel and WPS Spreadsheet does not easily support this behavior in a single cell. Doing so requires complex VBA macros. Using an adjacent cell with a lookup formula is the most stable and recommended workaround.

How do I hide the #N/A error when the drop-down cell is empty?

If you are using XLOOKUP, you can use the built-in 'if_not_found' argument by adding empty quotes at the end of the formula: =XLOOKUP(A2, CodeRange, DescRange, ""). If using VLOOKUP, wrap the formula in an IFERROR function: =IFERROR(VLOOKUP(A2, TableRange, 2, FALSE), "").

Can I format the adjacent description cell so users don't type over it?

Yes. You can protect the worksheet to prevent users from editing the formula cell. Select the input cells, right-click, choose 'Format Cells', go to the 'Protection' tab, and uncheck 'Locked'. Then go to the 'Review' tab and click 'Protect Sheet'. This leaves the input cell editable while locking the lookup formula.

Excel & Data Problems