Function Problems

How to Populate a Value Based on a Number (1-16) in Excel

Rana GarciaRana Garcia Oct 29, 2026 869 views

Question details

The user needs to map numeric inputs (1 through 16) to specific corresponding values automatically in an Excel spreadsheet.

How to Populate a Value Based on a Number from 1 to 16 in Excel
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 you start

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.

Solution 1Recommended

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.

1
Create a Lookup Table

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.

2
Select the Target Cell

Click on the cell where you want the populated value to appear based on the user's numeric input.

3
Enter the Lookup Formula

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.

4
Apply Formula to Other Cells

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 a Lookup Table with VLOOKUP or XLOOKUP
Quick Total for Golf Scores: If you are mapping golf-score pairs and just need a rapid total, you can simply hold the Ctrl key while selecting the individual score cells, and then read the calculated 'Sum' value directly on Excel's status bar at the bottom right of the screen.
Efficient Spreadsheet Alternative

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the 1-16 numeric data to be mapped.
  2. 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. 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. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in advanced formulas for robust data mapping, lookups, and analysis.Lightweight installation with a fast, user-friendly interface.Free to use for everyday spreadsheet tasks and complex calculations.
microsoft office alternative - wps office

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.