Excel Formula to Show Value Based on Input

Overview

This guide presents an Excel formula that returns a value out of five possibilities based on the value entered in a specified cell.

Formula Example

Suppose we want to evaluate the content of cell A1 and return a specific value based on that content. The following formula can be used:

=CHOOSE(MATCH(A1, {"Option1", "Option2", "Option3", "Option4", "Option5"}, 0), "Value1", "Value2", "Value3", "Value4", "Value5")

Explanation of the Formula

1. MATCH Function:

   • MATCH(A1, {"Option1", "Option2", "Option3", "Option4", "Option5"}, 0)
   • This portion checks the value in cell A1 against an array of options.
   • It returns the position of the matched option (1 through 5) or an error if A1 does not match any of the options.

2. CHOOSE Function:

   • CHOOSE(index_num, "Value1", "Value2", "Value3", "Value4", "Value5")
   • This function takes the integer returned by the MATCH function as an index to select the corresponding value from the listed options.
   • If MATCH returns 1, "Value1" is returned; if it returns 2, "Value2" is returned, and so on.

Practical Example

• Input: If cell A1 contains the text "Option3".
• Output: The formula will output "Value3".

Use Case

This formula is useful for scenarios where you need to return predefined values based on user input, such as scoring systems, grading, or selecting categories based on keywords.

Conclusion

Using the MATCH and CHOOSE functions together allows for robust handling of user inputs in Excel, yielding dynamic outputs based on predetermined criteria. For further learning on advanced Excel functionality, consider exploring the resources available on the Enterprise DNA Platform.