跳到主要内容

20 多个适用于 Excel 初学者和高级用户的 VLOOKUP 示例

VLOOKUP 函数是 Excel 中最常用的函数之一。 本教程将通过数十个基础和高级示例逐步介绍如何在 Excel 中使用 VLOOKUP 函数。


下载VLOOKUP示例文件

 基本 Vlookup 示例   |    高级 Vlookup 示例   |    Vlookup 保持单元格格式


VLOOKUP函数的介绍–语法和参数

在 Excel 中,VLOOKUP 函数对于大多数 Excel 用户来说是一个强大的功能,它允许您在数据区域的最左侧查找一个值,并从您指定的列返回同一行中的匹配值,如下图所示.

VLOOKUP函数的语法:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

参数:

查找值 (必需):您要搜索的值。 它可以是值(数字、日期或文本)或单元格引用。 它必须在 table_array 范围的第一列中。 

表格数组 (必填):查找值列和结果值列所在的数据范围或表。

列索引号 (必需):包含返回值的列号。 它从表数组中最左边的列开始,从 1 开始。

范围查找 (可选):一个逻辑值,用于确定此 VLOOKUP 函数将返回完全匹配还是近似匹配。

  • 近似匹配 - 1 / TRUE / 省略 (默认):如果未找到完全匹配项,则公式会搜索最接近的匹配项 - 小于查找值的最大值。
    通知:在这种情况下,您必须将查找列(数据范围最左边的列)按升序排序,否则将返回错误或#N/A 错误结果。
  • 完全匹配 – 0 / 假:用于搜索与查找值完全相等的值。 如果找不到完全匹配的内容,则将返回错误值#N / A。

功能说明:

  • Vlookup 函数只从左到右查找一个值。
  • Vlookup 函数执行不区分大小写的查找。
  • 如果根据查找值有多个匹配值,则使用 Vlookup 函数只返回第一个匹配的值。

VLOOKUP基本示例

在本节中,我们将讨论您经常使用的一些 Vlookup 公式。

2.1 精确匹配和近似匹配VLOOKUP

 2.1.1 做精确匹配VLOOKUP

通常,如果您正在寻找与 VLOOKUP 函数的精确匹配,您只需使用 FALSE 作为最后一个参数。

例如,要根据特定的ID号获得相应的数学分数,请执行以下操作:

请将下面的公式复制并粘贴到一个空白单元格中(这里,我选择 G2),然后按 输入 获得结果的关键:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

注意:在上面的公式中,有四个参数:

  • F2 是包含要查找的值 C1005 的单元格;
  • A2:D7 是您在其中执行查找的表数组;
  • 3 是返回匹配值的列号; (一旦函数发现 ID - C1005,它就会转到表数组的第三列,并返回与 ID - C1005 相同行中的值。)
  • FALSE 指完全匹配。

VLOOKUP 公式如何工作?

首先,它在表的最左边的列中查找 ID - C1005。 它从上到下查找单元格 A6 中的值。

一旦找到该值,它就会转到第三列的右侧并提取其中的值。

因此,您将得到如下图所示的结果:

请注意: 如果在最左侧的列中未找到查找值,则返回 #N/A 错误。
🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ...
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ...
列管理器: 添加特定数量的列  |  移动列   |  取消隐藏列  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器  |  资源库   |  日期选择器  |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (粗体/斜体...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   还有很多...

Kutools for Excel 拥有 300 多种功能, 确保只需点击一下即可获得您所需要的...

 
 2.1.2 做近似匹配VLOOKUP

近似匹配对于搜索数据范围之间的值很有用。 如果未找到完全匹配,则近似 VLOOKUP 将返回小于查找值的最大值。

比如你有如下范围的数据,而指定的订单不在Orders列中,那么如何在B列中获取与其最接近的Discount呢?

第 1 步:应用 VLOOKUP 公式并将其填充到其他单元格

将以下公式复制并粘贴到要放置结果的单元格中,然后向下拖动填充柄以将此公式应用于其他单元格。

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

结果:

现在,您将根据给定的值获得近似匹配,请参见屏幕截图:

笔记:

  • 在上面的公式中:
    • D2 是您要返回其相关信息的值;
    • A2:B9 是数据范围;
    • 2 表示返回你的匹配值的列号;
    • TRUE 指近似匹配。
  • 如果找不到完全匹配,近似匹配将返回小于您的特定查找值的最大值。
  • 要使用 VLOOKUP 函数得到一个近似的匹配值,必须对数据区域最左边的列进行升序排序,否则会返回错误的结果。

2.2 在Excel中做区分大小写的VLOOKUP

默认情况下,VLOOKUP 函数执行不区分大小写的查找,这意味着它将小写和大写字符视为相同。 有时,您可能需要在 Excel 中执行区分大小写的查找,普通的 VLOOKUP 函数可能无法解决。 在这种情况下,您可以使用替代函数,例如 INDEX 和 MATCH 与 EXACT 函数,或 LOOKUP 和 EXACT 函数。

例如,我具有以下数据范围,其中ID列包含大写或小写的文本字符串,现在,我想返回给定ID号的相应数学分数。

第 1 步:应用任何一个公式并将其填充到其他单元格

请将以下任一公式复制并粘贴到您想要获得结果的空白单元格中。 然后,选择公式单元格,将填充柄向下拖动到要填充此公式的单元格。

配方1: 粘贴公式后,请按 Ctrl + Shift + Enter 键。

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

配方2: 粘贴公式后,请按 输入 键。

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

结果:

然后您将获得所需的正确结果。 看截图:

笔记:

  • 在上面的公式中:
    • A2:A10 是包含您要查找的特定值的列;
    • F2 是查找值;
    • C2:C10 是要从中返回结果的列。
  • 如果找到多个匹配项,此公式将始终返回最后一个匹配项。

2.3 Excel中从右到左的VLOOKUP值

VLOOKUP 函数总是在数据区域的最左边的列中搜索一个值,并从右边的列中返回相应的值。 如果要执行反向 VLOOKUP,这意味着在右列中查找特定值并在左列中返回其对应的值,如下图所示:

单击以逐步了解有关此任务的详细信息…


2.4 VLOOKUP Excel中的第二个、第n个或最后一个匹配值

通常情况下,如果在使用Vlookup 函数时找到多个匹配值,则只返回第一个匹配的记录。 在本节中,我将讨论如何获取数据范围内的第二个、第 n 个或最后一个匹配值。

 2.4.1 VLOOKUP并返回第XNUMX个或第n个匹配值

假设您在 A 列中有一个姓名列表,他们在 B 列中购买了培训课程。现在,您要查找给定客户购买的第 2 个或第 n 个培训课程。 看截图:

在这里,VLOOKUP 函数可能无法直接解决此任务。 但是,您可以使用 INDEX 函数作为替代。

第 1 步:将公式应用并填充到其他单元格

例如,要根据给定的条件获得第二个匹配值,请将以下公式应用于空白单元格,然后按 Ctrl + Shift + Enter 组合键以获得第一个结果。 然后,选择公式单元格,将填充柄向下拖动到要填充此公式的单元格。

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

结果:

现在,所有基于给定名称的第二个匹配值都已一次显示。

请注意: 在上面的公式中:

  • A2:A14 是包含所有查找值的范围;
  • B2:B14 是您要返回的匹配值的范围;
  • E2 是查找值;
  • 2 表示要获取的第二个匹配值,要返回第三个匹配值,只需要把它改成3即可。
 2.4.2 VLOOKUP并返回最后一个匹配值

如果要进行vlookup并返回最后一个匹配值,如下面的屏幕截图所示,此 VLOOKUP 并返回最后一个匹配值 教程可以帮助您详细获取最后一个匹配值。


2.5 VLOOKUP 匹配两个给定值或日期之间的值

有时,您可能希望在两个值或日期之间查找值并返回相应的结果,如下面的屏幕截图所示。 在这种情况下,您可以对排序表使用 LOOKUP 函数而不是 VLOOKUP 函数。

 2.5.1 VLOOKUP 用公式匹配两个给定值或日期之间的值

第 1 步:排列数据并应用以下公式

您的原始表应该是一个排序的数据范围。 然后,将以下公式复制或输入到空白单元格中。然后,拖动填充柄将此公式填充到您需要的其他单元格中。

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

结果:

现在,您将根据给定值获得所有匹配的记录,请参见屏幕截图:

笔记:

  • 在上面的公式中:
    • A2:A6 是较小值的范围;
    • B2:B6 是较大数字的范围;
    • E2 是要获取其对应值的查找值;
    • C2:C6 是要从中返回相应值的列。
  • 此公式还可用于提取两个日期之间的匹配值,如下图所示:
 2.5.2 VLOOKUP 匹配两个给定值或日期之间的值,具有方便的功能

如果你觉得难以记住和理解上面的公式,那么在这里,我将介绍一个简单的工具—— Kutools for Excel,其 在两个值之间查找 功能,您可以轻松地根据两个值或日期之间的特定值或日期返回相应的项目。

  1. 点击 库工具 > 超级查找 > 在两个值之间查找 启用此功能。
  2. 然后根据您的数据从对话框中指定操作。
备注: 要应用此功能,您应该下载 Kutools for Excel 30天免费试用 首先。


2.6 在 VLOOKUP 函数中使用通配符进行部分匹配

在 Excel 中,可以在 VLOOKUP 函数中使用通配符,它​​允许您对查找值执行部分匹配。 例如,您可以使用 VLOOKUP 根据查找值的一部分从表中返回匹配值。

假设我有一系列数据,如下图所示,现在,我想根据名字(不是全名)提取分数。 如何在Excel中解决此任务?

第 1 步:将公式应用并填充到其他单元格

请将以下公式复制或输入到空白单元格中,然后拖动填充柄将此公式填充到您需要的其他单元格中:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

结果:

并且所有匹配的分数都已返回,如下图所示:

请注意: 在上面的公式中:

  • E2&“ *” 是部分数学的标准。 这意味着您要查找以单元格 E2 中的值开头的任何值。 (通配符“*”表示任意一个字符或任意字符)
  • A2:C11 是要搜索匹配值的数据范围;
  • 3 表示从数据范围的第3列返回匹配值;
  • 表示精确的数学。 (使用通配符时,您必须将函数中的最后一个参数设置为 FALSE 或 0,以在 VLOOKUP 函数中启用精确匹配模式。)
Tips:
  • 要查找并返回以特定值结尾的匹配值,您应该在值前面加上通配符“*”。 请应用这个公式:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • 要根据部分文本字符串查找并返回匹配值,无论指定文本是在文本字符串的开头、结尾还是中间,只需将单元格引用或文本用两个星号 (*) 括起来在两侧。 请用这个公式
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 来自另一个工作表的 VLOOKUP 值

通常,您可能需要处理多个工作表,VLOOKUP 函数可用于从另一个工作表中查找数据,就像在一个工作表中一样。

例如,您有两个工作表,如下图所示,要从指定的工作表中查找并返回相应的数据,请执行以下步骤:

第 1 步:将公式应用并填充到其他单元格

请将以下公式输入或复制到您要获取匹配项的空白单元格中。 然后,将填充柄向下拖动到要应用此公式的单元格。

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

结果:

您将根据需要获得相应的结果,请参见截图:

请注意: 在上面的公式中:

  • A2 代表查询值;
  • '数据表'!A2:C15 表示在名为“数据表”的工作表中搜索 A2:C15 范围内的值; (如果工作表名称中包含空格或标点符号,则应将工作表名称用单引号引起来,否则可以直接使用工作表名称,如 =VLOOKUP(A2,Datasheet!$A$2:$C$15,3,0))。
  • 3 是包含您要从中返回的匹配数据的列号;
  • 0 表示执行精确匹配。

2.8 来自另一个工作簿的 VLOOKUP 值

本节将讨论使用 VLOOKUP 函数查找并返回来自不同工作簿的匹配值。

例如,假设您有两个工作簿。 第一个工作簿包含产品列表及其各自的成本。 在第二个工作簿中,您想要提取每个产品项目的相应成本,如下图所示。

第 1 步:应用并填写公式

打开您要使用的两个工作簿,然后将以下公式应用到您要将结果放入第二个工作簿的单元格中。然后,将此公式拖动并复制到您需要的其他单元格中

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

结果:

笔记:

  • 在上面的公式中:
    • B2 代表查询值;
    • '[Product list.xlsx]Sheet1'!A2:B6 表示从工作簿Product list中名为Sheet2的工作表A6:B1范围内搜索; (对工作簿的引用用方括号括起来,整个工作簿 + 工作表用单引号括起来。)
    • 2 是包含您要从中返回的匹配数据的列号;
    • 0 表示返回完全匹配。
  • 如果查找工作簿已关闭,查找工作簿的完整文件路径将显示在公式中,如下图所示:

2.9 返回空白或特定文本而不是 0 或 #N/A 错误

通常,当你使用VLOOKUP函数返回对应的值时,如果匹配的单元格为空,则返回0。如果没有找到匹配的值,则会得到#N/A的错误值,如下所示下面的截图。 如果要显示空白单元格或特定值而不是 0 或 #N/A,这 VLOOKUP 返回空白或特定值而不是 0 或 N/A 教程可能会对你有帮助。


高级VLOOKUP示例

3.1 双向查找(VLOOKUP in row and column)

有时,您可能需要执行二维查找,这意味着同时在行和列中搜索一个值。 例如,如果您有以下数据范围,您可能需要获取特定产品在指定季度的值。 本节将介绍在 Excel 中处理此作业的公式。

在 Excel 中,您可以结合使用 VLOOKUP 和 MATCH 函数来进行双向查找。

请将以下公式应用到空白单元格中,然后按 输入 获得结果的关键。

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

请注意: 在上面的公式中:

  • G2 是要根据其获取相应值的列中的查找值;
  • A2:E7 是您要查看的数据表;
  • H1 是要根据其获取相应值的行中的查找值;
  • A2:E2 是列标题的单元格;
  • FALSE 表示获得精确匹配。

3.2 基于两个或多个条件的VLOOKUP匹配值

你很容易根据一个标准来查找匹配值,但是如果你有两个或更多的标准,你能做什么呢?

 3.2.1 VLOOKUP 根据两个或多个条件用公式匹配值

在这种情况下,Excel 中的 LOOKUP 或 MATCH 和 INDEX 函数可以帮助您快速轻松地解决这个问题。

例如,我具有下面的数据表,以根据特定的产品和尺寸返回匹配的价格,以下公式可能会为您提供帮助。

第 1 步:应用任何一个公式

配方1: 粘贴公式后,请按 输入 键。

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

配方2: 粘贴公式后,请按 Ctrl + Shift + Enter 键。

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

结果:

笔记:

  • 在以上公式中:
    • A2:A12 = G1 表示在A1:A2范围内搜索G12的条件;
    • B2:B12 = G2 表示在B2:B2范围内搜索G12的条件;
    • D2:D12 is 您要从中返回相应值的范围。
  • 如果你有两个以上的条件,你只需要将其他条件加入到公式中,例如:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 VLOOKUP 基于两个或多个条件的匹配值具有智能特征

记住上述需要重复应用的复杂公式可能具有挑战性,这会降低您的工作效率。 然而, Kutools for Excel 提供 多条件查找 只需点击几下,即可根据一个或多个条件返回相应的结果。

  1. 点击 库工具 > 超级查找 > 多条件查找 启用此功能。
  2. 然后根据您的数据从对话框中指定操作。
备注: 要应用此功能,您应该下载 Kutools for Excel 30天免费试用 首先。


3.3 VLOOKUP 以一个或多个条件返回多个值

在 Excel 中,VLOOKUP 函数搜索一个值,如果找到多个对应值,则只返回第一个匹配的值。 有时,您可能希望返回一行、一列或单个单元格中的所有对应值。 本节将讨论如何返回工作簿中具有一个或多个条件的多个匹配值。

 3.3.1 VLOOKUP基于一个或多个条件横向匹配的所有值

假设您有一个数据表,其中包含 A1:C14 范围内的国家/地区、城市和名称,现在您想要水平返回所有来自“US”的名称,如下图所示。 要解决这个任务,请 单击此处逐步获取结果.

 3.3.2 VLOOKUP 所有基于一个或多个条件的垂直匹配值

如果您需要 Vlookup 并根据特定条件垂直返回所有匹配值,如下图所示, 请点击此处获取详细解决方案.

 3.3.3 VLOOKUP 所有基于一个或多个条件的匹配值到单个单元格中

如果要 Vlookup 并将多个匹配值返回到具有指定分隔符的单个单元格中, TEXTJOIN 的新功能可以帮助您快速轻松地解决这个问题.

笔记:


3.4 VLOOKUP 返回匹配单元格的整行

在本节中,我将讨论如何使用 VLOOKUP 函数检索匹配值的整行。

第 1 步:应用并填写以下公式

请将以下公式复制或键入到要输出结果的空白单元格中,然后按 输入 获取第一个值的键。 然后,将公式单元格向右拖动,直到显示整行数据。

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

结果:

现在,您可以看到返回了整行数据。 看截图:
文档 vlookup 函数 50 1

请注意: 在上面的公式中:

  • F2 是您要根据其返回整行的查找值;
  • A1:D12 是您要从中搜索查找值的数据范围;
  • A1 表示数据范围内的第一列编号;
  • FALSE 表示精确查找。

提示:

  • 如果根据匹配值找到多行,要返回所有对应的行,请应用以下公式,然后按 Ctrl + Shift + Enter 组合键以获得第一个结果。 然后向右拖动填充手柄。 然后,继续向下拖动填充柄穿过单元格以获取所有匹配的行。 请参阅下面的演示:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    文档 vlookup 函数 51 2

3.5 Excel 中的嵌套 VLOOKUP

有时,您可能需要查找跨多个表相互关联的值。 在这种情况下,您可以将多个 VLOOKUP 函数嵌套在一起以获得最终值。

例如,我有一个包含两个单独表格的工作表。 第一个表格列出了所有产品名称及其相应的销售人员。 第二张表列出了每个推销员的总销售额。 现在,如果您想要查找每个产品的销售额,如下面的屏幕截图所示,您可以嵌套 VLOOKUP 函数来完成此任务。
文档 vlookup 函数 53 1

嵌套 VLOOKUP 函数的通用公式为:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

请注意:

  • Lookup_Array中 是您正在寻找的价值;
  • 表_array1, 表_array2 是查找值和返回值存在的表;
  • 列索引号1 表示查找中间公共数据在第一个表中的列号;
  • 列索引号2 表示要返回匹配值的第二个表中的列号;
  • 0 用于精确匹配。

第 1 步:应用并填写以下公式

请将以下公式应用于空白单元格,然后将填充柄向下拖动到要应用此公式的单元格。

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

结果:

现在,您将获得如下屏幕截图所示的结果:

请注意: 在上面的公式中:

  • G3 包含您要查找的值;
  • A3:B7, D3:E7 是查找值和返回值存在的表范围;
  • 2 是要从中返回匹配值的范围中的列号。
  • 0 表示 VLOOKUP 精确数学。

3.6 根据另一列中的列表数据检查值是否存在

VLOOKUP 函数还可以帮助您根据另一列中的数据列表检查值是否存在。 例如,如果您想在 C 列中查找姓名,如果在 A 列中找到或未找到该姓名,则返回 Yes 或 No,如下图所示。
文档 vlookup 函数 56 1

第 1 步:应用并填写以下公式

请将以下公式应用于空白单元格,然后将填充柄向下拖动到要填充此公式的单元格。

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

结果:

你会得到你需要的结果,看截图:

请注意: 在上面的公式中:

  • C2 是您要检查的查找值;
  • A2:A10 是检查是否找到查找值的范围列表;
  • FALSE 表示获得精确匹配。

3.7 VLOOKUP并对行或列中的所有匹配值求和

在处理数字数据时,您可能需要从表中提取匹配值,然后对多个列或行中的数字求和。 本节将介绍一些可以帮助您完成此任务的公式。

 3.7.1 VLOOKUP并对一行或多行中所有匹配的值求和

假设您有一个产品列表,其中包含几个月的销售额,如以下屏幕截图所示。 现在,您需要根据给定的产品对所有月份的所有订单求和。

第 1 步:应用并填写以下公式

请将以下公式复制或输入到空白单元格中,然后按 Ctrl + Shift + Enter 组合键以获得第一个结果。 然后,向下拖动填充柄以将此公式复制到您需要的其他单元格。

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

结果:

第一个匹配值的一行中的所有值都已加在一起,见截图:

请注意: 在上面的公式中:

  • H2 是包含您要查找的值的单元格;
  • A2:F9 是包含查找值和匹配值的数据范围(无列标题);
  • 2,3,4,5,6 {} 是用于计算范围总和的列号;
  • FALSE 表示完全匹配。

提示: 如果要对多行中的所有匹配项求和,请使用以下公式:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 VLOOKUP并对一列或多列的所有匹配值求和

如果您想对特定月份的总值求和,如下面的屏幕截图所示。 普通的 VLOOKUP 函数可能帮不了您,在这里,您应该同时应用 SUM、INDEX 和 MATCH 函数来创建公式。

第 1 步:应用以下公式

将下面的公式应用到空白单元格中,然后向下拖动填充柄以将此公式复制到其他单元格。

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

结果:

现在,列中基于特定月份的第一个匹配值已汇总在一起,请参见屏幕截图:

请注意: 在上面的公式中:

  • H2 是包含您要查找的值的单元格;
  • B1:F1 是包含查找值的列标题;
  • B2:F9 是包含要求和的数值的数据范围。

提示: 要 VLOOKUP 并对多列中的所有匹配值求和,您应该使用以下公式:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 VLOOKUP 对第一个匹配的或者所有匹配的值求和,一个强大的功能

可能上面的公式你很难记住,既然如此,我就推荐一个强大的功能—— 查找和总和 of Kutools for Excel,通过这个功能,您可以尽可能轻松地对行或列中的第一个匹配值或所有匹配值进行 Vlookup 和求和。

  1. 点击 库工具 > 超级查找 > 查找和总和 启用此功能。
  2. 然后根据需要从对话框中指定操作。
备注: 要应用此功能,您应该下载 Kutools for Excel 30天免费试用 首先。
 3.7.4 VLOOKUP 并对行和列中的所有匹配值求和

例如,如果您想在需要同时匹配列和行时对值求和,以获取XNUMX月月份产品Sweater的总值,如下图所示。

在这里,您可以使用 SUMPRODCT 函数来完成此任务。

请将以下公式应用到单元格中,然后按 输入 获得结果的关键,请参见屏幕截图:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

请注意: 在上面的公式中:

  • B2:F9 是包含要求和的数值的数据范围;
  • B1:F1 是包含要根据其求和的查找值的列标题;
  • I2 是您要查找的列标题中的查找值;
  • A2:A9 是包含要根据其求和的查找值的行标题;
  • H2 是您要查找的行标题中的查找值。

3.8 VLOOKUP 根据键列合并两张表

在日常工作中,在分析数据时,您可能需要根据一个或多个关键列将所有必要信息收集到一个表中。 要完成此任务,您可以使用 INDEX 和 MATCH 函数代替 VLOOKUP 函数。

 3.8.1 VLOOKUP根据一个键列合并两张表

例如,您有两个表,第一个表包含产品和名称数据,第二个表包含产品和订单数据,现在,您希望通过将公共产品列匹配到一个表中来合并这两个表。

第 1 步:应用并填写以下公式

请将以下公式应用于空白单元格。 然后,将填充柄向下拖动到要应用此公式的单元格

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

结果:

现在,您将得到一个合并表,其中顺序列根据键列数据连接到第一个表。

请注意: 在上面的公式中:

  • A2 是您要查找的查找值;
  • F2:F8 是要返回匹配值的数据范围;
  • E2:E8 是包含查找值的查找范围。
 3.8.2 VLOOKUP根据多个键列合并两张表

如果要联接的两个表有多个键列,要根据这些公共列合并表,请按照以下步骤操作。

通用公式为:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

请注意:

  • 查找表 是包含查找数据和匹配记录的数据范围;
  • 查找值1 是您要寻找的第一个标准;
  • 查找范围1 数据列表是否包含第一个条件;
  • 查找值2 是您正在寻找的第二个标准;
  • 查找范围2 数据列表是否包含第二个条件;
  • 返回列编号 表示要返回匹配值的lookup_table中的列号。

第 1 步:应用以下公式

请将下面的公式应用到要放置结果的空白单元格中,然后按 Ctrl + Shift + Enter 键一起获得第一个匹配的值,请参见屏幕截图:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

第二步:将公式填充到其他单元格

然后,选择第一个公式单元格,并拖动填充柄以根据需要将此公式复制到其他单元格:

Tips: 在 Excel 2016 或更高版本中,您还可以使用 Power Query 该功能可根据键列将两个或多个表合并为一个表。 请点击以了解详细信息.

3.9 VLOOKUP 跨多个工作表匹配值

您是否需要在 Excel 中跨多个工作表执行 VLOOKUP? 例如,如果您有三个包含数据范围的工作表,并且您想要根据这些工作表中的条件检索特定值,则可以按照分步教程进行操作 跨多个工作表的 VLOOKUP 值 完成这项任务。


VLOOKUP匹配值保持单元格格式

查找匹配值时,不会保留原始单元格格式,如字体颜色、背景颜色、数据格式等。 为了保持单元格或数据格式,本节将介绍一些解决作业的技巧。

4.1 VLOOKUP匹配值并保持单元格颜色、字体格式

众所周知,普通的 VLOOKUP 函数只能从另一个数据范围中检索匹配值。 但是,在某些情况下,您可能希望获得相应的值以及单元格格式,例如填充颜色、字体颜色和字体样式。 在本节中,我们将讨论如何在 Excel 中保留源格式的同时检索匹配值。

请执行以下步骤以查找并返回其对应的值以及单元格格式:

第一步:将代码1复制到Sheet Code Module

  1. 在工作表中包含要VLOOKUP的数据,右击工作表标签,选择 查看代码 从上下文菜单中。 看截图:
  2. 在开 Microsoft Visual Basic应用程序 窗口,请将下面的VBA代码复制到“代码”窗口中。
  3. VBA 代码 1:VLOOKUP 获取单元格格式和查找值
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

第二步:将代码2复制到Module窗口

  1. 仍然在 Microsoft Visual Basic应用程序 窗口中,单击 插页 > 模块,然后将下面的VBA代码2复制到“模块”窗口中。
  2. VBA 代码 2:VLOOKUP 获取单元格格式和查找值
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

第 3 步:选择 VBAproject 的选项

  1. 输入以上代码后,点击 工具 > 参考资料 ,在 Microsoft Visual Basic应用程序 窗口。 然后检查 Microsoft脚本运行时 复选框 参考– VBAProject 对话框。 查看屏幕截图:
  2. 然后,单击 OK 关闭对话框,然后保存并关闭代码窗口。

第 4 步:键入获取结果的公式

  1. 现在,返回工作表,应用以下公式。 然后,向下拖动填充柄以获取所有结果及其格式。 看截图:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

请注意: 在上面的公式中:

  • E2 是您要查找的值;
  • A1:C10 是表格范围;
  • 3 是要从中检索匹配值的表的列号。

4.2 保留 VLOOKUP 返回值的日期格式

使用VLOOKUP函数查找并返回日期格式的值时,返回结果可能显示为数字。 要在返回结果中保留日期格式,您应该将 VLOOKUP 函数包含在 TEXT 函数中。

第 1 步:应用并填写以下公式

请将下面的公式应用到空白单元格中。 然后,拖动填充柄将此公式复制到其他单元格。

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

结果:

所有匹配的日期都已返回,如下图所示:

请注意: 在上面的公式中:

  • E2 是查找值;
  • A2:C9 是查找范围;
  • 3 是您希望返回值的列号;
  • FALSE 表示获得精确匹配;
  • mm/dd/yyy 是您要保留的日期格式。

4.3 从 VLOOKUP 返回单元格注释

您是否需要在 Excel 中使用 VLOOKUP 检索匹配的单元格数据及其关联的注释,如以下屏幕截图所示? 如果是这样,下面提供的用户定义函数可以帮助您完成此任务。

第 1 步:将代码复制到模块中

  1. 按住 ALT + F11 键打开 Microsoft Visual Basic应用程序 窗口。
  2. 点击 插页 > 模块,然后将以下代码复制并粘贴到“模块窗口”中。
    VBA代码:Vlookup并返回带有单元格注释的匹配值:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. 然后保存并关闭代码窗口。

第 2 步:输入公式以获取结果

  1. 现在,输入以下公式,并拖动填充柄将此公式复制到其他单元格。 它会同时返回匹配的值和评论,见截图:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

请注意: 在上面的公式中:

  • D2 是要返回其对应值的查找值;
  • A2:B9 是你要使用的数据表;
  • 2 是包含要返回的匹配值的列号;
  • FALSE 表示获得精确匹配。

4.4 VLOOKUP 数字存储为文本

例如,我有一个数据范围,其中原始表中的 ID 号为数字格式,而查找单元格中的 ID 号存储为文本,使用普通 VLOOKUP 函数时可能会遇到 #N/A 错误。 在这种情况下,要检索正确的信息,您可以将 TEXT 和 VALUE 函数包装在 VLOOKUP 函数中。下面是实现此目的的公式:

第 1 步:应用并填写以下公式

请将以下公式应用到空白单元格中,然后向下拖动填充柄以复制此公式。

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

结果:

现在,您将获得正确的结果,如下图所示:

笔记:

  • 在上面的公式中:
    • D2 是要返回其对应值的查找值;
    • A2:B8 是你要使用的数据表;
    • 2 是包含要返回的匹配值的列号;
    • 0 表示获得精确匹配。
  • 如果您不确定数字和文本的位置,此公式也很有效。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations