Function Problems

Assign Values to Text in Excel with IF, IFS, or VLOOKUP

John WilsonJohn Wilson Nov 18, 2026 868 views

Question details

Assign a number to each of three text values in Excel.

Assign Values to Text in Excel with IF, IFS, or VLOOKUP
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.
Before you start

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.

Solution 1Recommended

Use a VLOOKUP table to assign values

Use a lookup table when the mappings may change or need to be expanded.

1
Create the mapping table

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.

2
Enter the lookup formula

Select C15 and enter =VLOOKUP(B15,$E$13:$F$15,2,FALSE), then press Enter. FALSE requires an exact match.

3
Test the result

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 a VLOOKUP table to assign values
Handle unmatched text: VLOOKUP returns #N/A if B15 is not found. To display a message instead, enter =IFNA(VLOOKUP(B15,$E$13:$F$15,2,FALSE),"Not found") in C15.
WPS Spreadsheet

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. 1. Open the worksheet: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Enter the mappings: In E13:F15, enter Roof and 1, Door and 2, and Window and 4 as three rows.
  3. 3. Add the formula: Select C15, enter =VLOOKUP(B15,$E$13:$F$15,2,FALSE), and press Enter.
  4. 4. Test and save: Enter a mapped label in B15, check the result in C15, and select Save.
Update assigned numbers in the lookup table without rewriting the formulaOpen and save Microsoft Excel XLSX and XLS filesUse familiar spreadsheet cells and formulasWork with a free, lightweight office suite
QA img-9

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.