I am trying to assign numerical values to a list of drop down values that are able to be selected via list data validation. I want to display the text, however be able to quantify my results with the corresponding numerical value.


Excel example

Business Value








  • 2
    There are several ways to do this (IF, INDEX/MATCH, VLOOKUP...) A bit more context and knowing what you tried and how it failed ot achieve the desired result may help us provide a better answer. – cybernetic.nomad Oct 7 at 20:25
  • Better you go with dependent Drop down method . – Rajesh S Oct 8 at 7:01
  • Sure. For example when I select 'Small' for business value, I want that to be captured as a 1 (integer) so that I can quantify my results in a formula. – Darryl Oct 8 at 13:40
  • THANK YOU! I figured it out with Match. – Darryl Oct 8 at 14:09

Lookups are arguably the most practical method but it sounds like you want to use Custom Number Formats because that is precisely what they do. There's a limit to the number of values you can assign this way so they may not be suitable for you. If you only have three values then it's a no brainier. This is one of the better write ups I've been able to find on them: https://www.exceltactics.com/definitive-guide-custom-number-formats-excel/

And if you want get creative, you can explore off the wall options like using a second line in a cell or creating a formula to convert your string to the desired value.

Really, your imagination is the only limit once you get your geek on..

  • Thank you. I figured it out. – Darryl Oct 8 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.