将列添加到命名范围后，如何防止 VLOOKUP 中断？

Question

我的 Excel 工作表包含大量针对我定义的命名范围的 VLOOKUP。现在，当我在命名范围中间添加一列时，引用插入列之后的列的 VLOOKUP 现在已损坏。我了解问题所在，但最好的解决方法是什么？有没有办法从标题文本中找出列号？

[编辑] 使用 Kaze 的 INDEX 和 MATCH 想法似乎是最佳解决方案。以下是我最终得到的结果：

INDEX(MyTableData, MATCH("RowLabel", RowList, 0), MATCH("ColumnLabel", ColumnList,0))

其中 MyTableData 是整个表的命名范围，RowList 是行标题的命名范围，ColumnList 是列标题的命名范围。

Answer 1

就我个人而言，我更喜欢使用INDEX-MATCH组合进行查找，而不是VLOOKUP。这是我为侄女编写的 Pokedex 的一个例子。

为了获得 Squirtle 的隐藏能力，我会使用以下公式：

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1)

这会产生与以下相同的结果：

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0))

INDEX-MATCH 的一个优点是，在大多数情况下，您不需要引用整个数据范围，因此即使您定期向数据范围添加列和行，第一个公式也应该有效。它还有另一个优点：由于您只引用 2 个一维范围，因此计算速度更快。

尽管如此，您仍然可以使用MATCH公式VLOOKUP来获取列号。

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0)

其中：
$A$1:$H$1是数据/命名范围内的第一行，或者包含标题文本的行
NAMED_RANGE是数据范围的名称
"COLUMN_TEXT"是包含所需数据的列的标题文本
C1包含查找值

编辑：根据 Doug 的要求添加了 Index-Match 示例。:D

Answer 2

您可以使用该MATCH功能：

这个网站对如何使用它有很好的解释：
http://www.techonthenet.com/excel/formulas/match.php

另一种方法是使用COLUMN函数读取实际的列号（如果您的表格从 A 列以外的任何位置开始，则需要在公式中减去第一列的编号）。例如：

 =VLOOKUP(B2,$B$2:$D$300,COLUMN($D$1)-Column($B$1), False)

其中 D 列包含您的查找值

Answer 3

另一种解决方案是创建一个用户定义函数，这应该会使该过程变得非常简单。

这里有一种方法，它创建一个名为的函数XLOOKUP：

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant
    XLOOKUP = CVErr(xlErrNA)
    Dim xRow As Long, xCol As Long
    With table_array
        For xRow = 1 To .Rows.Count
            If .Cells(xRow, 1).Value = row Then
                For xCol = 1 To .columns.Count
                    If .Cells(1, xCol).Value = column Then
                        XLOOKUP = .Cells(xRow, xCol).Value
                        Exit Function
                    End If
                Next
                Exit Function
            End If
        Next
    End With

End Function

一旦将它添加到你的项目中，你就可以像这样使用它：

=XLOOKUP(RowLabel, ColumnLabel, Range)

Answer 4

如果您在查找公式中命名列范围，则添加或删除列时公式仍可正常工作。