如何使用多标准VLOOKUP

观看视频-如何使用多重条件的VLOOKUP函数

Excel VLOOKUP函数在其基本形式中,可以查找一个查找值并从指定的行返回相应的值。

但是通常需要使用带有多个条件的Excel VLOOKUP。

如何使用多标准VLOOKUP

假设您有一个具有学生名称,考试类型和数学分数的数据(如下所示):

VLOOKUP与多标准-数据

使用VLOOKUP函数获取每个学生对应考试水平的数学分数可能是一个挑战。

人们可以争辩说更好的选择是重组数据集或使用a数据透视表。如果这对你有用,就别这样了。但在许多情况下,您会被现有的数据所困扰,而数据透视表可能不是一个选择。

在这种情况下,本教程就是为您准备的。

现在有两种方法可以使用具有多个标准的VLookup获取查找值。

  • 使用Helper列。
  • 使用CHOOSE函数。

VLOOKUP具有多个标准 - 使用辅助专栏

我是Excel中辅助专栏的粉丝。

我发现使用helper列比使用数组公式有两个显著的优点:

  • 它使得易于理解工作表中发生的事情。
  • 与数组函数相比,它的速度更快(在大型数据集中尤为明显)。

别误会我的意思。我并不反对数组公式。我喜欢用数组公式做一些神奇的事情。我只是把它们留到特别的场合当其他所有选择都无济于事的时候。

回到问题中,需要辅助列来创建唯一的限定符。然后可以使用此唯一限定符来查找正确的值。例如,数据中有三个亚光,但亚光和单元测试或亚光和中期只有一个组合。

以下是步骤:

  • 在B栏和C列之间插入帮助柱。VLOOKUP与多标准-帮助列
  • 在Helper列中使用以下公式:= A2&“|”&B2
    • 这将为每个实例创建惟一的限定符,如下所示。Vlookup具有多个标准 - 独特的限定员
  • 请使用G3中的如下公式美元= VLOOKUP (f3”|“2美元、美元加元2:$ D $ 19日2 0)
  • 复制所有单元格。

VLOOKUP与多标准-结果助手

这是如何工作的呢?

我们为名称和考试的每个实例创建惟一的限定符。在这里使用的VLOOKUP函数中,查找值被修改为$F3& " | " &G$2,这样这两个查找条件就被组合起来,用作一个单独的查找值。例如,G2中VLOOKUP函数的查找值为马特|单元测试。现在,此查找值用于从C2:D19获取分数。

澄清:

有几个问题可能会迎来你的脑海,所以我以为我会在这里尝试回答:

  • 为什么我在加入两个标准时使用了|符号?在一些非常罕见(但可能)的情况下,你可能有两个不同的标准,但最终得到相同的结果。这里有一个非常简单的例子(请原谅我缺乏创造力):

VLOOKUP与多个标准-为什么分隔符

请注意,虽然A2和A3是不同的,B2和B3是不同的,但最终的组合是相同的。但如果你使用分隔符,那么即使组合也会不同(D2和D3)。

  • 为什么我把辅助列插入B和C列之间而不是最左边?- - - - - -将辅助列插入到左侧没有危害。事实上,如果您不想用原始数据锻炼,那应该是去的方式。我做到了它,因为它让我在vlookup函数中使用少量的小区。而不是在表阵列中具有4列,我可以只使用2列来管理。但那只是我。

现在没有一种适合所有人的方法。有些人在使用带有多个条件的VLOOKUP时,可能不喜欢使用任何帮助列。

所以这是你的非辅助列方法。

下载示例文件
下载文件

Vlookup具有多个标准 - 使用选择功能

使用数组公式而不是helper列可以节省工作表的空间,并且如果在工作簿中使用较少的次数,性能也同样好。

考虑到上面使用的相同数据集,这是将为您提供结果的公式:

= vlookup($ e3&“|”&f $ 2,选择({1,2},$ 2美元,$ 2 $ 19&“|”&$ b $ 2:$ b $ 19,$ c $ 2:$ c $ 2:$ 2:$ c $ 19),2,0)

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

VLOOKUP与多标准-数组选择

这是如何工作的呢?

该公式还使用辅助列的概念。不同之处在于,不是将帮助程序列放在工作表中,认为它是作为公式的一部分的虚拟辅助数据。

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

VLOOKUP具有多个标准 - 虚拟辅助数据

在上图中,当我选择公式的选择部分并按F9时,它显示了选择公式将提供的结果。

结果是{" Matt|Unit Test ",91; " Bob|Unit Test ", 52;......}

它是一个数组,其中逗号表示同一行中的下一个单元格,分号表示下一列中有以下数据。因此,这个公式创建了2列数据—一列具有惟一标识符,另一列具有分数。

现在,当您使用VLookup函数时,它只需查找第一列(此虚拟2列数据)中的值并返回相应的分数。

下载示例文件
下载文件

您还可以使用其他公式对多个条件(例如指数/比赛或者SUMPRODUCT.)。

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

您也可能喜欢以下查找教程:

Excel电子书订阅

免费Excel书籍

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

关于“如何使用多标准VLOOKUP”的13个思考

  1. 假设在同一单元格中,假设A1使用Alt + Enter输入多个值。我可以在其他单元格中获得相应的Vlookup值,假设B1在A1单元中的每个条目吗?

  2. 嗨sumit.

    有人发现,您的网站正在提供关于MS Excel的非常好的信息。
    我正在寻找关于选择多个值到一个基于从另一个下拉列表的选择下拉列表的信息。

    1 1.65
    1 2.77
    1 2.9
    1 3.38
    1 4.55
    1 6.35
    1 9.09
    2 1.65
    2 2.11
    2 2.77
    2 3.18
    2 3.58
    2 3.91
    2 4.37
    2 4.78
    2 5.54
    2 6.35
    2 7.14
    2 8.74
    2 11.07

    如果从下拉列表中选择1,则以1将以1填充到另一个下拉列表中的值。第一列可以包含文本格式,小数等的分数。

    你能帮我一下吗?

    感谢您

    克利须那神

  3. 您好,非常感谢您非常有用的提示,但我没有找到与以下问题相关的任何答案。列中的公式我应该返回与列C日期相关的列中的货币的汇率(另一张表)。如果那天没有汇率,则应退回以前的前日期的汇率。我的问题是如何编写公式,以将汇率退回货币,以至于之前的日期?非常感谢你的帮助。

  4. 嗨sumit!谢谢你的提示。如果您需要的信息是您所需的信息,是否有一种方法可以使用它?我已经运行了一个宏来插入列并将信息连接到列A.您的提示看起来更容易。它也会离开吗?

  5. 在特定线路后插入行是否有任何快捷方式,
    我想在每十条线后插入新行

  6. 嗨Sumit,非常感谢您的提示,我在Excel中开始Excel,我有一个小问题,同时从一个其他Excel列复制一组数字。我已经粘贴了它并试图分类顺序。但他们有一个“前面的空间”而不是响应我的命令。如何克服这个问题。

    关于

    JACIN

  7. 对于您的数据表设置和生成的布局,Pivot表应为理想的解决方案。

    • 谢谢你的评论。我在文章里也提到了。数据透视表工作得非常好,而且很容易使用,但是需要将数据结构成所需的格式。如果您希望只获取两个值(假设在仪表板中),那么VLOOKUP(或Index/Match、Sumproduct)可能是正确的方法。

评论被关闭。