Use Excel VLOOKUP to Convert Text Values to Numbers
Question details
Create a formula that returns a corresponding number for a text value using a maintainable mapping table.

- 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.
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.
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.
In Excel, enter A and F in cells D2 and D3, then enter 10 and 20 in cells E2 and E3.
Select D2:E3, click the Name Box to the left of the formula bar, type List, and press Enter.
With the text to convert in A1, select an empty cell and enter =IFERROR(VLOOKUP(A1,List,2,FALSE),"").
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 XLOOKUP with an Excel Table
Use XLOOKUP in a supported Excel version to look up values in a table that can expand as mappings are added.
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. Open your spreadsheet: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Enter the mapping: Enter A and F in D2:D3, and enter 10 and 20 in E2:E3.
- 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. Save the workbook: Use Menu > Save As to save your changes in XLSX format.

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],"").




