Assign Values to Text in Excel with IF, IFS, or VLOOKUP
Question details
Assign a number to each of three text values in Excel.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs to return 1 for Roof, 2 for Door, or 4 for Window based on the text in cell B15.
- Observed behavior
- Cell C15 should display the number associated with the value in B15.
Check that B15 contains Roof, Door, or Window, and choose an empty cell for the result. If using a lookup table, make sure E13:F15 is available.
Use a VLOOKUP table to assign values
Use a lookup table when the mappings may change or need to be expanded.
In E13:F15, enter Roof and 1 in the first row, Door and 2 in the second row, and Window and 4 in the third row.
Select C15 and enter =VLOOKUP(B15,$E$13:$F$15,2,FALSE), then press Enter. FALSE requires an exact match.
Change B15 to Roof, Door, or Window and check that C15 returns 1, 2, or 4 respectively. Update the values in column F if the mappings change.

Use IFS for a short, fixed list
Use IFS when the three mappings are unlikely to change and you do not need a separate table.
Use nested IF formulas to assign values
Use nested IF when IFS is not available in your Excel version.
Assign Text Values with a Lookup Table in WPS Spreadsheet
Use VLOOKUP in WPS Spreadsheet to match a label in B15 to a number in an editable mapping table.
- 1. Open the worksheet: Open WPS Spreadsheet and use Open to select your workbook.
- 2. Enter the mappings: In E13:F15, enter Roof and 1, Door and 2, and Window and 4 as three rows.
- 3. Add the formula: Select C15, enter =VLOOKUP(B15,$E$13:$F$15,2,FALSE), and press Enter.
- 4. Test and save: Enter a mapped label in B15, check the result in C15, and select Save.

Frequently Asked Questions
Why does VLOOKUP return #N/A?
With FALSE as the last argument, VLOOKUP requires an exact match. Check that B15 appears in E13:E15 and that the text does not contain extra spaces.
How can I change an assigned number later?
If you use the lookup-table solution, edit the corresponding number in F13:F15. The formula in C15 will use the updated value.
Can I copy the formula to other rows?
Yes. Drag the fill handle from C15 down the column. B15 will change to the corresponding row, while $E$13:$F$15 remains fixed.
What happens if B15 contains an unlisted value?
The basic IFS and VLOOKUP formulas return #N/A. Add a TRUE fallback to IFS or wrap VLOOKUP in IFNA to show a message instead.




