Function Problems

Use Excel VLOOKUP to Convert Text Values to Numbers

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 868 views

Question details

Create a formula that returns a corresponding number for a text value using a maintainable mapping table.

Use Excel VLOOKUP to Convert Text Values to Numbers
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains text values that need to be mapped to predefined numbers.
Observed behavior
Each recognized text value should return its mapped number, while an unrecognized value should return a blank.
Before you start

Identify the text values and their corresponding numbers, and choose an empty two-column area for the mapping table. Enter the mapped values as numbers rather than text.

Solution 1Recommended

Use VLOOKUP with a Named Mapping Range

Use this approach to maintain text-to-number mappings in a table instead of a long nested IF formula.

1
Create the mapping

In Excel, enter A and F in cells D2 and D3, then enter 10 and 20 in cells E2 and E3.

2
Name the range

Select D2:E3, click the Name Box to the left of the formula bar, type List, and press Enter.

3
Enter the formula

With the text to convert in A1, select an empty cell and enter =IFERROR(VLOOKUP(A1,List,2,FALSE),"").

4
Test and maintain the mapping

Enter A in A1 to return 10, or F to return 20. To add mappings beyond the original range, update the List range through Formulas > Name Manager.

Use VLOOKUP with a Named Mapping Range
Blank results: FALSE requires an exact match. IFERROR returns a blank for an unmatched value, but it also hides other formula errors.
WPS Spreadsheet

Map Text to Numbers in WPS Spreadsheet

Use a two-column mapping table and VLOOKUP in WPS Spreadsheet to return numbers for text values without building a long nested IF formula.

  1. 1. Open your spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Enter the mapping: Enter A and F in D2:D3, and enter 10 and 20 in E2:E3.
  3. 3. Add the formula: Enter the text to look up in A1. In an empty cell, enter =IFERROR(VLOOKUP(A1,$D$2:$E$3,2,FALSE),"").
  4. 4. Save the workbook: Use Menu > Save As to save your changes in XLSX format.
Maintain text-to-number mappings in worksheet cellsReturn a blank for unmatched values with IFERROROpen and edit Microsoft Excel XLSX filesUse familiar spreadsheet formulas and menus
microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return a blank for a value I entered?

The formula returns a blank when it cannot find an exact match. Check the input cell and the first column of the List range for spelling differences or extra spaces. IFERROR can also hide other formula errors.

How do I add more text-to-number mappings?

Add the new text and number to the mapping columns, then use Formulas > Name Manager to expand the List range. If you use an Excel table with XLOOKUP, add rows to the table instead.

Is VLOOKUP case-sensitive?

No. VLOOKUP does not distinguish between uppercase and lowercase letters, even when FALSE is used for an exact match.

Can I use XLOOKUP instead of VLOOKUP?

Yes, if your Excel version supports XLOOKUP. For a table named LetterMap with Letter and Value columns, use =XLOOKUP(A1,LetterMap[Letter],LetterMap[Value],"").