Microsoft Excel LOOKUP功能只是...不起作用？

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.

表1:用户在垂直列中输入仪器列表以及有关仪器的一些数据的数据库。

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)

表2:包含与表1完全相同的信息但以不同格式显示的表单。根据工作表1中的条目自动填充。(在此问题中无用)

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.

工作表1上存在一个通过单击按钮执行的宏。此宏从Sheet 1中获取每一列,并为每列创建一个新的Sheet。每个新工作表Sheet 3都重命名为它所代表的工作表1列中的第一个值。

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.

即,Sheet 1中有4列,每列中的第一个值为:LS-ALPHA,LS-BRAVO,LS-CHARLIE,LS-DELTA。我的宏将创建4个名为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.

每个新工作表上的第一个单元格(技术上为H2)包含一个引用工作表名称的公式。

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

也就是说,LS-ALPHA表上的H2实际上会说“LS-ALPHA”,LS-BRAVO表上的H2会说LS-BRAVO等。

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.

新工作表上的每个其他数据单元格将自动在主工作表(工作表1)上查找该值,以确定它来自哪个列。然后,它将低于该值并从下面的某些单元格x行中获取内容。

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

如果我将列重命名为“LS-A,LS-B,LS-C,LS-D”,它可以工作。如果我将列重命名为“LS-AA,LS-AB,LS-AC,LS-AD”,它可以工作。如果我将列重命名为“LS-AAA,LS-AAB,LS-AAC,LS-AAD”,它可以工作。

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.

但是,如果我将列重命名为“LS-TTF,LS-TTD,LS-TSD,LS-TSF”,它们都会以某种方式被破坏....工作表上的所有链接都不再起作用。如果它们甚至显示某些东西,它们中的一些指向不正确的列。我遇到的这个问题令人难以置信的奇特。我不知道为什么这些名字特别打破它,我也不知道其他名字也会打破它。

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

当它“中断”时会发生什么:所有引用似乎都找到了LOOKUP中的最后一个可用列。四张纸中的三张都使用了第四列中的值,当它们不应该使用时。然后,一张纸只给我错误(#N / A)。当我逐步完成计算时,它正在寻找LOOKUP函数中的正确值,它只是没有返回正确的东西....

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.

注意,echo列用于调试目的。我试图看看他们是否都会显示回声而不是delta。显然,他们没有。

1 个解决方案

#1

From the help for the LOOKUP function:

从LOOKUP函数的帮助:

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.

重要信息:lookup_vector中的值必须按升序排列:..., - 2,-1,0,1,2,...,A-Z,FALSE,TRUE;否则,LOOKUP可能无法返回正确的值。大写和小写文本是等效的。

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:

正确工作的一组值 - “LS-A,LS-B,LS-C,LS-D” - 按字母顺序排列。无法正常工作的一组值 - “LS-TTF,LS-TTD,LS-TSD,LS-TSF” - 不按字母顺序排列。此外,LOOKUP不一定能找到完全匹配 - 如帮助中所指定:

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.

如果LOOKUP函数找不到lookup_value,则该函数匹配lookup_vector中小于或等于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)

切换到使用HLOOKUP功能。确保Range_lookup参数为false以要求完全匹配。示例用法:= HLOOKUP(H2,'数据库(Cols)'!D2:AN3,2,FALSE)

#1