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


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

Generic formula



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:

  • 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:


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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.