How to Populate a Value Based on a Number (1-16) in Excel
Question details
The user needs to map numeric inputs (1 through 16) to specific corresponding values automatically in an Excel spreadsheet.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up an automated data mapping system, such as assigning golf scores, performance ratings, or predefined selections based on a numerical input from 1 to 16.
- Observed behavior
- The user requires a systematic function to evaluate an entered number and return its paired value without having to enter the mapped results manually.
Before setting up your formulas, determine your exact mapping logic by writing down the numbers 1 through 16 alongside their intended output values so you can easily reference them.
Use a Lookup Table with VLOOKUP or XLOOKUP
Creating a dedicated reference table is the most reliable and easiest method to map specific numbers to their corresponding values.
This approach uses a two-column table to act as a dictionary for your spreadsheet, making it easy to update your mapped values later without rewriting complex formulas.
In a blank area of your worksheet (for example, columns Y and Z), list the numbers 1 through 16 in one column, and type their corresponding output values in the adjacent column.
Click on the cell where you want the populated value to appear based on the user's numeric input.
Type =VLOOKUP(A2, $Y$1:$Z$16, 2, FALSE) into the formula bar, replacing A2 with your input cell and $Y$1:$Z$16 with the absolute range of your lookup table. If using a newer version of Excel, you can use =XLOOKUP(A2, $Y$1:$Y$16, $Z$1:$Z$16) instead.
Press Enter, then click and drag the fill handle at the bottom-right of the cell to copy the formula down to other rows.

Use the CHOOSE Function for Direct Mapping
If you prefer not to create a separate lookup table on your sheet, the CHOOSE function lets you embed the 16 values directly within the formula.
Easily Map and Analyze Data with WPS Spreadsheet
WPS Office offers a powerful, lightweight Spreadsheet tool that perfectly supports advanced lookup functions like VLOOKUP, XLOOKUP, and CHOOSE to map your numerical data seamlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the 1-16 numeric data to be mapped.
- 2. Set Up Your Data Table: Create a reference table with numbers 1-16 in one column and your desired output values in the adjacent column.
- 3. Insert a Lookup Formula: Navigate to the Formulas tab on the ribbon, select Insert Function, and choose VLOOKUP or XLOOKUP to link your data.
- 4. Calculate and Save: Press Enter to execute the formula, drag the fill handle to populate the rest of the column, and save your document.

Frequently Asked Questions
What happens if a user inputs a number outside the 1 to 16 range?
If the input number is not found in your lookup table or CHOOSE sequence, functions like VLOOKUP will return an #N/A or #VALUE! error. You can prevent this by wrapping your formula in the IFERROR function, such as =IFERROR(VLOOKUP(...), "Invalid Number"), to handle out-of-bounds entries gracefully.
Can I map numbers to text strings instead of numeric values?
Yes, lookup tables and functions like CHOOSE or VLOOKUP can return any data format, including text strings, dates, or other formulas. Ensure that if you type text directly into a formula (like in the CHOOSE function), it is enclosed in double quotation marks.
Is XLOOKUP better than VLOOKUP for a 1 to 16 data mapping?
XLOOKUP is generally preferred in modern spreadsheet applications because it is more flexible, does not require you to count column index numbers, and can look up data to the left of the search column. However, for a basic 1 to 16 mapping table, both VLOOKUP and XLOOKUP will work perfectly.




