如何使VLOOKUP区分大小写

默认情况下,VLOOKUP函数中的查找值不区分大小写。例如,如果查找值是马特,马特,或马特,对于VLOOKUP函数也是一样的。不管情况如何,它都会返回第一个匹配的值。

使VLOOKUP区分大小写

假设你有如下数据:

Excel Vlookup示例-区分大小写

如您所见,有三个单元格具有相同的名称(A2、A4和A5),但使用不同的字母大小写。在右边(E2:F4),我们有三个名字(Matt, Matt, Matt)和他们的数学成绩。

Excel VLOOKUP函数不能处理区分大小写的查找值。在上面的示例中,无论查找值是什么情况(Matt、Matt或Matt),它总是返回38(这是第一个匹配的值)。

在本教程中,您将学习如何使VLOOKUP区分大小写:

  • 使用Helper列。
  • 没有使用助手列和使用公式。
使VLOOKUP区分大小写-使用帮助列

可以使用helper列获取查找数组中每个项目的唯一查找值。这有助于区分不同字母大小写的名字。

以下是实现这一点的步骤:

  • 在要获取数据的列的左边插入一个helper列。在下面的示例中,您需要将helper列插入到列A和列C之间。
    Excel VLOOKUP区分大小写-助手列
  • 在helper列中,输入公式=ROW()。它将在每个单元格中插入行号。Excel VLOOKUP区分大小写-帮助列行
  • 在单元格F2中使用下面的公式来获得区分大小写的查找结果。
    = VLOOKUP (马克斯(具体(E2、美元美元2:一个9美元)* (美元($ $ 2:9美元))),B 2:美元加元9美元,2,0)
  • 复制粘贴剩下的单元格(F3和F4)。
    Excel VLOOKUP区分大小写-结果与助手

注意:因为这是一个数组公式,使用Control + Shift + Enter代替回车。

这是如何工作的呢?

让我们分解这个公式来理解它是如何工作的:

  • 准确(E2、$ $ 2:$ 9美元)- E2中的查找值与A2:A9中的所有值进行比较。它返回一个TRUE / false数组,当有精确匹配时返回TRUE。在这种情况下,E2中的值是Matt,它将返回以下数组:
    {真的,假的,假的,假的,假的,假的,假的,假的}。
  • 准确(E2、$ $ 2:$ 9美元)*(行($ $ 2:$ 9美元)—将true / false数组与A2:A9行号相乘。只要有一个TRUE,它就给出行号,否则就给出0。在本例中,它将返回{2;0;0;0;0;0;0;0}。
  • MAX(确切(E2,一美元2:$ 9美元)*(行($ $ 2:$ 9美元)))-返回数字数组中的最大值。在本例中,它将返回2(这是有精确匹配的行号)。
  • 现在我们只需使用这个数字作为查找值,并使用查找数组B2:C9。

注意:您可以将helper列插入数据集中的任何位置。只要确保它在您想要获取数据的列的左边。您需要相应地调整VLOOKUP函数中的列号。

现在,如果您不喜欢helper列,您也可以在不使用helper列的情况下进行区分大小写的查找。

使VLOOKUP区分大小写-没有帮助列

即使您不想使用helper列,您仍然需要有一个虚拟helper列。这个虚拟列不是工作表的一部分,而是在公式中构造的(如下所示)。

下面是不使用helper列的结果:

= VLOOKUP (MAX(确切(D2,一美元2:$ 9美元)*(行($ $ 2:$ 9美元))),选择({1,2}、行($ $ 2:$ 9美元),B美元2:B 9美元),2 0)

这是如何工作的呢?

该公式还使用了helper列的概念。不同之处在于,不是将助手列放在工作表中,而是将其视为虚拟助手数据,它是公式的一部分。

下面是作为助手数据工作的部分(用橙色突出显示):

= VLOOKUP (MAX(确切(D2,一美元2:$ 9美元)*(行($ $ 2:$ 9美元))),选择({1,2},美元($ $ 2:9美元),B 2美元:$ B 9美元)2 0)

让我来告诉你我所说的虚拟助手数据是什么意思。

VLOOKUP区分大小写-公式中的助手

在上面的插图中,当我选择公式的CHOOSE部分并按下F9时,它显示了CHOOSE公式将给出的结果。

结果是{2,38岁;3,88;4,57;5,82;6,55岁,7日,44;8,75;9日38}

它是一个数组,逗号表示同一行中的下一个单元格,分号表示下一行中有以下数据。因此,这个公式创建了两列数据——一列有行号,一列有数学分数。

现在,当您使用VLOOKUP函数时,它只是在第一列(这个虚拟2列数据)中查找查找值,并返回相应的分数。这里的查找值是我们从MAX和EXACT函数的组合中得到的一个数字。

下载示例文件
下载文件

你还知道其他的方法吗?如果有,请在评论区和我分享。

你也可以喜欢以下VLOOKUP教程:

Excel电子书订阅

免费的EXCEL的书

获得51个Excel提示电子书来提高你的生产力,更快地完成工作

关于“如何使VLOOKUP区分大小写”的7个思考

  1. 这个公式是工作在第一个名字,当我们输入第二个名字公式不工作
    = VLOOKUP (MAX(确切(D2,一美元2:$ 9美元)*(行($ $ 2:$ 9美元))),选择({1,2}、行($ $ 2:$ 9美元),B美元2:B 9美元),2 0)

  2. 美好的一天,
    为什么我总是得到一个错误?但当我按F9时,它会给我正确的值。

  3. 我找到了一个更简单的解决方案:
    =指数(B 2美元:B 9美元,美元匹配(真的,准确(D2,一美元2:$ 9美元),0))

评论都关门了。