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.
Explanation
This formula uses the value in cell F7 for a lookup value, the range B6:C10 for the lookup table, the number 2 to indicate “2nd column”, and zero as the last argument to force an exact match.
Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text.
