Microsoft Excel LOOKUP function just... doesn't work?

Question

So, I have a fairly involved workbook.

Sheet 1: A database where the user enters a list of instruments as well as some data about the instruments in a vertical column.

Sheet 2: 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)

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, Sheet 3, is renamed to the first value in the column of Sheet 1 that it represents.

• i.e., 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.

The first cell (technically H2) on each of the new sheets contains a formula to reference the sheet name.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

• i.e., H2 on the LS-ALPHA sheet will actually say "LS-ALPHA", H2 on the LS-BRAVO sheet will say LS-BRAVO, etc.

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.

=LOOKUP(H2,'Database (Cols)'!D2:AN2,'Database (Cols)'!D3:AN3)

This works absolutely perfectly. It does everything well.

Except, not always.

• 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.

However, 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.

What happens when it 'breaks': 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....

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:

Working: https://drive.google.com/file/d/0B9zbU-BeMQNfSmRrWVhKVW9RN3M/view?usp=drivesdk

Broken: https://drive.google.com/file/d/0B9zbU-BeMQNfd1FUemwxQjQwMEE/view?usp=drivesdk

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.

Answer 1

From the help for the LOOKUP function:

IMPORTANT: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

The set of values which work correctly - "LS-A, LS-B, LS-C, LS-D" - are in alphabetical order. The set of values which don't work correctly - "LS-TTF, LS-TTD, LS-TSD, LS-TSF" - are not in alphabetical order. Also, LOOKUP doesn't necessarily find an exact match - as specified in the help:

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

To fix, either:

• reorder the non-working set of values to be in alphabetical order (although you still won't guarantee an exact match), or
• switch to using the HLOOKUP function instead. Ensure that the Range_lookup parameter is false to require an exact match. Sample usage: =HLOOKUP(H2,'Database (Cols)'!D2:AN3,2,FALSE)