title: Microsoft Excel LOOKUP function just... doesn't work?
body: <p>So, I have a fairly involved workbook.</p>
  
  <p><strong>Sheet 1:</strong> A database where the user enters a list of instruments as well as some data about the instruments in a vertical column.</p>
  
  <p><strong>Sheet 2:</strong> A sheet that contains the exact same information as sheet 1 but displays it in a different format. Automatically populates based on entries from Sheet 1. (Not useful in this question)</p>
  
  <p>There exists a macro on Sheet 1 that is executed by clicking a button. This macro takes every column from Sheet 1 and creates a new Sheet for each column. Each new sheet, <strong>Sheet 3</strong>, is renamed to the first value in the column of Sheet 1 that it represents.</p>
  
  <ul>
  <li><strong>i.e.,</strong> There are 4 columns in Sheet 1 with the first value in each column being: LS-ALPHA, LS-BRAVO, LS-CHARLIE, LS-DELTA. My macro will create 4 new sheets called LS-ALPHA, LS-BRAVO, LS-CHARLIE, LS-DELTA.</li>
  </ul>
  
  <p>The first cell (technically H2) on each of the new sheets contains a formula to reference the sheet name. </p>
  
  <pre><code>=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
  </code></pre>
  
  <ul>
  <li><strong>i.e.,</strong> H2 on the LS-ALPHA sheet will actually say "LS-ALPHA", H2 on the LS-BRAVO sheet will say LS-BRAVO, etc.</li>
  </ul>
  
  <p>Every other data cell on the new sheet will automatically look up that value on the main sheet (Sheet 1) to determine what column it is from. Then, it will go below that value and get the contents from some cell x rows below.</p>
  
  <pre><code>=LOOKUP(H2,'Database (Cols)'!D2:AN2,'Database (Cols)'!D3:AN3)
  </code></pre>
  
  <p>This works absolutely perfectly. It does everything well.</p>
  
  <p>Except, not always.</p>
  
  <ul>
  <li>If I rename the columns to "LS-A, LS-B, LS-C, LS-D", it works. If I rename the columns to "LS-AA, LS-AB, LS-AC, LS-AD", it works. If I rename the columns to "LS-AAA, LS-AAB, LS-AAC, LS-AAD", it works.</li>
  </ul>
  
  <p><strong>However,</strong> if I rename the columns to something like "LS-TTF, LS-TTD,LS-TSD, LS-TSF" they are all broken somehow.... None of the links on the sheets work any more. Some of them point to the incorrect column if they even do show something. This issue I'm having is incredibly peculiar. I don't know why these names break it in particular, nor do I know what other names would also break it.</p>
  
  <p><strong>What happens when it 'breaks':</strong> All of the references seem to find the last available column in the LOOKUP. Three of the four sheets all use values from the fourth column when they aren't supposed to. Then, one sheet just gives me errors (#N/A). When I step through the calculation, it is looking for the correct value in the LOOKUP function, it's just not returning the right thing....</p>
  
  <p>I can't really give much more information without showing you what's happening so I've included a working spreadsheet and a broken spreadsheet. The sheets have been generated from the macro so you don't have to mess with it. The working and broken files are below:</p>
  
  <p>Working: <a href="https://drive.google.com/file/d/0B9zbU-BeMQNfSmRrWVhKVW9RN3M/view?usp=drivesdk" rel="nofollow">https://drive.google.com/file/d/0B9zbU-BeMQNfSmRrWVhKVW9RN3M/view?usp=drivesdk</a></p>
  
  <p>Broken: <a href="https://drive.google.com/file/d/0B9zbU-BeMQNfd1FUemwxQjQwMEE/view?usp=drivesdk" rel="nofollow">https://drive.google.com/file/d/0B9zbU-BeMQNfd1FUemwxQjQwMEE/view?usp=drivesdk</a></p>
  
  <p><em>Note, the echo column is for debugging purposes. I was trying to see if they would all show echo instead of delta. Apparently, they don't.</em></p>