title: Excel: alternatives for very long IF($Cell=key, value) list?
body: <p>I have formula which looks something like the following:</p>
  
  <p><code>=(IF(ISBLANK(B1),1, (IF($B1="Banana", 2.23, IF($B1="Cherry",3.5))) )*C1*A1)</code></p>
  
  <p>Here the value of the cell B1 is being pulled from a data validation drop-down list, which unlike the example above is getting very, very long, with many IF and OR operators. Its quickly going to become impossible for me to declare maintain the relationship of all the bannans and cherries in the formula it self. What's the point of updating both the list AND the formula with largely the same information - just to give the user a dropdown :/ </p>
  
  <p>One option would be for me put the multiplier value in with the drop down list and then use <code>VALUE(RIGHT(B1,LEN(B1)-FIND("x",B1))</code> <a href="https://stackoverflow.com/questions/10337723/explode-a-cells-value-and-multiply-together-values-found-on-either-side-of-a-se#comment13316668_10337723">to pull it out</a>. BUT in this case the multiplier is a very long decimal and would be confusing to the end user, like: "Banana 0.000000d325001" </p>
  
  <p>I see two possibilities: </p>
  
  <p>1.Find a way to hide the multiplier from being displayed to the user in the drop down menu with some sort of preprocessing OR</p>
  
  <ol start="2">
  <li>Find a way to associate the bananas and cherries in some sort of array Key : Value relationship that wont display in the dropdown menu. </li>
  </ol>
  
  <p>Does anyone know how this might be achieved - is it feasible or just a world of hurt?</p>
  
  <p>Thanks everyone for your help.</p>