将Excel的INDEX函数与COLUMN和MATCH一起使用 [英] Using Excel's INDEX Function with COLUMN and MATCH

问题描述

我正在尝试将INDEX函数与MATCH，COLUMN和数据验证单元格结合使用.

I am trying to use an INDEX function combined with MATCH, COLUMN, and a Data Validated Cell.

这是我编写的函数，但是它抛出一个错误，说明有问题.

This is the function I have written, but it throws there was an error that there is something wrong.

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),COLUMN(ShipsData[Max_Level]))

这行得通，但并不是像我所希望的那样成为未来的证明.

This works, but it is not as future proof as I would like.

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),13)

我应该指出这些条件都不是条件间数据验证的最大依据，而第二种则放在单元格中.

I should point out neither of these works as the maximum for a data validation between condition, the second works when placed in a cell.

其他信息:

ShipsData是表的名称，B3是具有所有可能船只的下拉列表的Data Validated单元，并且表中有两列，分别命名为Name和Max_Level.

ShipsData is the name of the table, B3 is a Data Validated cell with a drop down of all possible ships, and there are Two columns in the table named Name and Max_Level.

我要做的是验证一艘船的水位，使其介于(包括)最小水位(1)和最大水位之间(随船而异).

What I am trying to do is validate the level of a ship so that it is between (inclusive) it's minimum level (1) and it's maximum level which varies per ship.

如果您需要更多信息，我可以提供.

If you need any more information I can provide it.

我解决了部分问题，现在该公式在一个单元格中完成，表格标题Max_Level中有一个错字.

I solved part of the problem, the formula now completes in a cell, there was a typo in the table header Max_Level.

但是，当我将此公式传递到数据验证中时，它会提示我我们发现此公式存在问题"，并且不会插入，但是该函数现在可在单元格中使用.

However when I past this formula into the data validation, it gives me a message about 'We found a problem with this formula' and it will not insert, however the function works in a cell now.

推荐答案

在我的系统上，您的公式显示#REF错误.

On my system, your formula shows a #REF error.

在第一个实例中，您的问题是INDEX函数的Column参数正在表中查找列号；并且您的引用返回的是Max_Level列的绝对列号.因此，只有当表从第1列(A列)开始时，此方法才起作用.

Your problem in the first instance is that the Column argument for the INDEX function is looking for the column number in the table; and your reference is returning the absolute column number of the Max_Level column. So this will work if and only if the table starts in column 1 (column A).

尝试一下:

=INDEX(ShipsData,MATCH(B3,ShipsData[Name],0),COLUMN(ShipsData[Max_Level])-COLUMN(ShipsData)+1)

如果您的公式显示您键入的公式包含错误"，则可能是您已经移动了一些内容，以致Max_Level(或Name)不再是表的一部分.

If your formula is showing "The formula you typed contains an error", then it may be that you have moved something around such that Max_Level (or Name) is no longer a part of the Table.

您可以通过在某些空白单元格中输入以下内容来进行检查:

You can check this by, in some blank cell, typing:

=ShipsData[

，然后在下拉列表中看到可用的列标题.

and seeing the column headers available showing up in the dropdown.

例如:

这篇关于将Excel的INDEX函数与COLUMN和MATCH一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！