title: Substitute array using cell references in excel
body: <p>I have a set of cells containing a list of items that I want to run a substitute function on, the data kind of look like this:</p>
  
  <pre><code>a,c,d,f,b
  
  a,f,c
  
  b,a,d
  </code></pre>
  
  <p>and I want it so that I replace the a with 1, b with 2, etc...</p>
  
  <p>currently my formula looks like this:</p>
  
  <pre><code>=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"a",1),"b",2),"c",3),"d",4)
  </code></pre>
  
  <p>The formula works great, but there are 2 issues:</p>
  
  <ol>
  <li><p>I have 25 items that require specific substitutions so the nested
  substitution statement gets too long.</p></li>
  <li><p>I'd like to be able to easily manipulate what values get substituted
  in - like changing a value in a vlookup table array - so that I can
  edit it without having to wade through a giant nested substitute
  function. So ideally I'd like something that works like
  <code>=Substitute(A1,{B1,B2,B3,B4},{C1,C2,C3,C4})</code> so I can change the values in column B, and C, and have it affect the formula.</p></li>
  </ol>
  
  <p>Any way to do this with a single formula? My alternative solution is to insert another 25 columns, use a find function on each of the 25 options in its own column, have an if statement that returns a cell reference if my find function exceeds 0, and then concatenate everything together again in a 26th column. I'd rather not use a VBA option since I'm already running a bunch of macros and don't want the added confusion.</p>
  
  <p>P.S. Please note that the <code>a,b,c,d, 1,2,3,4</code> data are just representational, the real data are much longer strings.</p>