Map inputs to arbitrary values

To map inputs to arbitrary values, you can use the VLOOKUP function. In the worksheet shown, the formula in cell F7 is:

=VLOOKUP(F6,B5:C10,2,0)

With a lookup value of 4 in cell F6, the result is 23 based on the table in columns B and C.

Explanation

In this example, the goal is to map the numbers 1-6 to the arbitrary values seen in the table below. For example:

If the input is 1, the output should be 10
If the input is 2, the output should be 81
If the input is 3, the output should be 17
If the input is 4, the output should be 23
And so on...

Input	Output
1	10
2	81
3	17
4	23
5	13
6	31

Although we could solve this problem with a complicated nested IF formula, a better option is to put the table on the worksheet and perform a lookup operation. The VLOOKUP function provides an easy way to do this. In the example shown, the formula in F7 is:

=VLOOKUP(F6,B5:C10,2,0)

lookup_value - the value in cell F6 (4)
table_array - the range B5:C10
col_index_num - 2, to specify the second column
range_lookup - zero, to force an exact match

Although in this case, we are mapping numeric inputs to numeric outputs, the same basic approach will readily handle text values for both inputs and outputs. A good example is converting test scores to grades.

Alternative with CHOOSE

If you have a limited number of inputs, and if the inputs are numbers starting with 1, you can also use the CHOOSE function. For the example shown the equivalent formula based on CHOOSE is:

=CHOOSE(F6,10,81,17,23,13,31)

The choose function is unwieldy for large amounts of data but for smaller data sets that map to a 1-based index, it has the advantage of being a simple "all in one" solution.