Note: The other languages of the website are Google-translated. Back to English
登陆  \/ 
x
or
x
注册  \/ 
x

or

VLOOKUP函数以及Excel中的一些基本和高级示例

在Excel中,VLOOKUP函数是大多数Excel用户的强大功能,用于在数据范围的最左侧查找值,并在您指定的列的同一行中返回匹配值,如下面的屏幕快照所示。 。 本教程通过Excel中的一些基本和高级示例,讨论如何使用VLOOKUP函数。

目录:

1. VLOOKUP函数简介–语法和参数

2.基本的VLOOKUP示例

3.高级VLOOKUP示例

4. VLOOKUP匹配值保持单元格格式

5.下载VLOOKUP示例文件


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

VLOOKUP函数的语法:

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

参数:

查找值:您要搜索的值。 它必须在table_array范围的第一列中。

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

Col_index_num:将从中返回匹配值的列数。 它从表数组的最左列开始以1开头。

Range_lookup:确定此VLOOKUP函数将返回完全匹配还是近似匹配的逻辑值。

  • 近似匹配1 /是:如果未找到精确匹配,则公式将搜索最接近的匹配-小于查找值的最大值。 在这种情况下,您应该按升序对查找列进行排序。
    = VLOOKUP(lookup_value,table_array,col_index,TRUE)
    = VLOOKUP(lookup_value,table_array,col_index,1)
  • 完全符合0 /假:用于搜索与查找值完全相等的值。 如果找不到完全匹配的内容,则将返回错误值#N / A。
    = VLOOKUP(lookup_value,table_array,col_index,FALSE)
    = VLOOKUP(lookup_value,table_array,col_index,0)

备注:

  • 1. Vlookup函数仅从左到右查找值。
  • 2.如果基于查找值存在多个匹配值,则使用Vlookup函数将仅返回第一个匹配的值。
  • 3.如果找不到查找值,它将返回#N / A错误值。

VLOOKUP基本示例

1。 进行完全匹配Vlookup和近似匹配Vlookup

在Excel中进行完全匹配的Vlookup

通常,如果您正在寻找与Vlookup函数的完全匹配,则只需在最后一个参数中使用FALSE。

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

1. 将以下公式应用于要获取结果的空白单元格:

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

2。 然后,将填充手柄向下拖动到要填充此公式的单元格,您将根据需要获得结果。 看截图:

备注:

  • 1.在上式中 F2 是您要返回其匹配值的值, A2:D7 是表数组,数字 3 是从中返回匹配值的列号,并且 FALSE 指完全匹配。
  • 2.如果在数据范围内未找到您的标准值,将显示错误值#N / A。

在Excel中进行近似匹配Vlookup

近似匹配对于在数据范围之间搜索值很有用。 如果找不到精确匹配,则近似Vlookup将返回小于查找值的最大值。

例如,如果您具有以下范围数据,则“订单”列中没有指定的订单,如何在B列中获得最接近的折扣?

1。 在要放入结果的单元格中输入以下公式:

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

2。 然后,将填充手柄向下拖动到单元格以应用此公式,您将根据给定的值获得近似匹配,请参见屏幕截图:

备注:

  • 1.在上式中 D2 是您要返回其相对信息的值, A2:B9 是数据范围,数字 2 指示您返回匹配值的列号,并且 TRUE 指近似匹配。
  • 2.近似匹配将返回小于您的特定查找值的最大值。
  • 3.要使用Vlookup函数获取近似匹配值,必须按升序对数据范围的最左列进行排序,否则将返回错误的结果。

2。 在Excel中执行区分大小写的Vlookup

默认情况下,Vlookup函数执行不区分大小写的查找,这意味着它将小写和大写字符视为相同。 有时,您可能需要在Excel中进行区分大小写的查找,Index,Match和Exact函数或Lookup和Exact函数可以帮您一个忙。

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

公式1:使用EXACT,INDEX,MATCH函数

1。 请输入以下数组公式或将其复制到要获取结果的空白单元格中:

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

2。 然后按 Ctrl + Shift + Enter 同时获得两个键以获取第一个结果,然后选择公式单元格,将填充手柄向下拖动到要填充此公式的单元格上,您将获得所需的正确结果。 看截图:

备注:

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

公式2:使用查阅和精确函数

1. 请将以下公式应用到要获取结果的空白单元格中:

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

2. 然后,将填充手柄向下拖动到要复制此公式的单元格,您将获得大小写敏感的匹配值,如下图所示:

备注:

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

3。 在Excel中从右到左的Vlookup值

Vlookup函数始终在数据范围的最左列中查找值,并从右列返回相应的值。 如果要进行反向Vlookup,这意味着要在右侧查找特定值,然后在左侧栏中返回其对应的值,如下所示的屏幕截图:

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


4。 Vlookup Excel中的第二个,第n个或最后一个匹配值

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

Vlookup并返回第二个或第n个匹配值

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

1。 要根据给定的标准获取第二个或第n个匹配值,请将以下数组公式应用于空白单元格:

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

2。 然后按 Ctrl + Shift + Enter 键在一起以得到第一个结果,然后选择公式单元格,将填充手柄向下拖动到要填充此公式的单元格,并且基于给定名称的所有第二个匹配值都已立即显示,请参见屏幕截图:

请注意:

  • 在这个公式中, A2:A14 是包含所有查找值的范围, B2:B14 是您要从中返回的匹配值的范围, E2 是查询值和最后一个数字 2 表示要获取的第二个匹配值,如果要返回第三个匹配值,则只需根据需要将其更改为3。

Vlookup并返回最后一个匹配值

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


5。 Vlookup在两个给定值或日期之间匹配值

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

Vlookup将两个给定值或日期之间的值与公式匹配

1。 首先,您的原始表应为已排序的数据范围。 然后,将以下公式复制或输入到空白单元格中:

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

2。 然后,拖动填充手柄以将该公式填充到所需的其他单元格中,现在,您将基于给定的值获取所有匹配的记录,请参见屏幕截图:

备注:

  • 1.在上式中 A2:A6 是较小值的范围,并且 B2:B6 是您数据范围内较大数字的范围, E2 是要获取其对应值的给定值, C2:C6 是要从中提取的列数据。
  • 2.此公式还可以用于提取两个日期之间的匹配值,如下图所示:

Vlookup在两个给定值或日期之间匹配具有有用功能的值

如果您对上述公式感到不满意,在这里,我将介绍一个简单的工具- Kutools for Excel,其 在两个值之间查找 功能,您可以根据两个值或日期之间的特定值或日期返回相应的项目,而无需记住任何公式。   点击立即下载Kutools for Excel!


6。 在Vlookup函数中使用通配符进行部分匹配

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

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

1。 正常的Vlookup函数无法正常工作,您需要使用通配符将文本或单元格引用连接起来,请复制或在空白单元格中输入以下公式:

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

2。 然后,拖动填充手柄以将此公式填充到所需的其他单元格中,并且所有匹配的分数都已返回,如下图所示:

备注:

  • 1.在上式中 E2&“ *” 是查找值,其中的值 E2* 通配符(“ *”表示一个或多个字符), A2:C11 是查询范围,数字 3 包含要返回的值的列。
  • 2. Vlookup使用通配符时,必须在Vlookup函数的最后一个参数中将精确匹配模式设置为FALSE或0。

提示:

1.查找并返回以特定值结尾的匹配值,请应用以下公式: =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

2.要基于部分文本字符串查找并返回匹配值,无论指定的文本位于文本字符串的前面,后面还是中间,您只需要在单元格引用或文本周围加入两个*字符即可。 请使用以下公式: =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


7。 来自另一个工作表的Vlookup值

通常,您可能必须使用多个工作表,Vlookup函数可用于从另一个工作表中查找数据,就像在一个工作表上一样。

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

1. 请输入以下公式或将其复制到要获取匹配项的空白单元格中:

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

2. 然后,将填充手柄向下拖动到要应用此公式的单元格上,您将根据需要获得相应的结果,请参见屏幕截图:

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

  • A2 代表查询值;
  • 数据表 是您要从中查找数据的工作表的名称,(如果工作表名称包含空格或标点符号,则应在工作表名称周围加上单引号,否则,可以直接使用工作表名称,例如= VLOOKUP(A2,数据表!$ A $ 2:$ C $ 15,3,0));
  • A2:C15 是我们要在其中搜索数据的数据表中的数据范围;
  • 3 是包含您要从中返回匹配数据的列号。

8。 来自另一个工作簿的Vlookup值

本节将讨论查找,并使用Vlookup函数从其他工作簿中返回匹配值。

例如,第一个工作簿包含产品和成本清单,现在,您要根据产品项在第二个工作簿中提取相应的成本,如下图所示。

1。 要从另一个工作簿中检索相对成本,请首先打开要使用的两个工作簿,然后将以下公式应用到要放入结果的单元格中:

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

2。 然后,将该公式拖放到所需的其他单元格中,请参见屏幕截图:

备注:

  • 1.在上式中:
    B2 代表查询值;
    [产品列表.xlsx] Sheet1 是您要从中查找数据的工作簿和工作表的名称(对工作簿的引用用方括号括起来,整个工作簿+工作表用单引号括起来);
    A2:B6 是我们要搜索数据的另一个工作簿的工作表中的数据范围;
    2 是包含您要从中返回匹配数据的列号。
  • 2.如果关闭了查找工作簿,则查找工作簿的完整文件路径将在公式中显示,如以下屏幕截图所示:

9。 Vlookup并返回空白或特定文本,而不是0或#N / A错误值

通常,当您应用vlookup函数返回相应的值时,如果匹配的单元格为空,则将返回0,如果找不到匹配的值,则会显示错误#N / A值,如下图所示。 而不是显示0或#N / A值以及空白单元格或您喜欢的其他值,这是 Vlookup返回空白或特定值,而不是0或N / A 本教程可能会逐步帮助您。


高级VLOOKUP示例

1。 具有Vlookup功能的双向查找(行和列中的Vlookup)

有时,您可能需要进行二维查找,这意味着同时在行和列中对Vlookup进行查找。 假设有以下数据范围,现在,您可能需要获取指定季度中特定产品的价值。 本节将介绍一些在Excel中处理此工作的公式。

公式1:使用VLOOKUP和MATCH函数

在Excel中,您可以结合使用VLOOKUP和MATCH函数进行双向查找,请将以下公式应用于空白单元格,然后按 输入 获得结果的关键。

=VLOOKUP(H1, $A$2:$E$6, MATCH(H2, $A$1:$E$1, 0), FALSE)

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

  • H1:您要在其中获得相应值的列中的查找值;
  • A2:E6:包含行标题的数据范围;
  • H2:您要基于其获取相应值的行中的查找值;
  • A1:E1:列标题的单元格。

公式2:使用INDEX和MATCH函数

这是另一个公式,也可以帮助您执行二维查找,请应用以下公式,然后按 输入 获得所需结果的关键。

=INDEX($B$2:$E$6, MATCH(H1, $A$2:$A$6, 0), MATCH(H2, $B$1:$E$1, 0))

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

  • B2:E6:要从中返回匹配项的数据范围;
  • H1:您要在其中获得相应值的列中的查找值;
  • A2:A6:行标题包含您要查找的产品。
  • H2:您要基于其获取相应值的行中的查找值;
  • B1:E1:列标题包含您要查找的季度。

2。 基于两个或多个条件的Vlookup匹配值

您可以轻松地基于一个条件查找匹配值,但是如果您有两个或多个条件,该怎么办? Excel中的LOOKUP或MATCH和INDEX函数可以帮助您快速轻松地解决此任务。

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

公式1:使用LOOKUP函数

请将以下公式应用于要获取结果的单元格,然后按Enter键,请参见屏幕截图:

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

备注:

  • 1.在上式中:
    A2:A12 = G1:表示在A1:A2范围内搜索G12的标准;
    B2:B12 = G2:用于在范围B2:B2中搜索G12的标准;
    D2:D12:要返回相应值的范围。
  • 2.如果您有两个以上的条件,则只需将其他条件连接到公式中,例如: =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))

公式2:使用INDEXT和MATCH函数

Index和Match函数的组合还可以用于基于多个条件返回匹配的值。 请复制或输入以下公式:

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

然后,同时按Ctrl + Shift + Enter键以获取所需的相对值。 看截图:

备注:

  • 1.在上式中:
    A2:A12 = G1:表示在A1:A2范围内搜索G12的标准;
    B2:B12 = G2:用于在范围B2:B2中搜索G12的标准;
    D2:D12:要返回相应值的范围。
  • 2.如果您有两个以上的条件,则只需将新条件加入公式中,例如: =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))

3。 Vlookup返回具有一个或多个条件的多个匹配值

在Excel中,Vlookup函数搜索一个值,并且如果找到多个对应的值,则仅返回第一个匹配的值。 有时,您可能想返回一行,一列或单个单元格中的所有相应值。 本节将讨论如何在工作簿中使用一个或多个条件返回多个匹配值。

Vlookup水平地基于一个或多个条件的所有匹配值

Vlookup根据一个条件水平地匹配所有匹配值:

要使用Vlookup并水平返回基于一个特定值的所有匹配值,通用公式为:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range) - m, ""), COLUMN() - n)), "")
备注: m 是返回范围减去1的第一个单元格的行号。
      n 是第一个公式单元格的列号减去1。

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($F1=$A$2:$A$20, ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2。 然后,选择第一个公式单元格,并将填充手柄拖动到右侧单元格,直到显示空白单元格,并且所有对应项均已提取,请参见屏幕截图:

提示:

如果返回的列表中存在重复的匹配值,请忽略此重复,请使用此公式,然后按 输入 得到第一个结果: =IFERROR(INDEX($C$2:$C$20,MATCH($F1,$A$2:$A$20,0)),"")

继续输入以下公式: =IFERROR(INDEX($C$2:$C$20,MATCH(1,($F1=$A$2:$A$20)*ISNA(MATCH($C$2:$C$20,$F2:F2,0)),0)),"") 放入第一个结果旁边的单元格,然后按 Ctrl + Shift + Enter 键一起获得第二个结果,然后将此公式拖动到右边的单元格以获取所有其他匹配的值,直到显示空白单元格,请参见屏幕截图:


Vlookup水平基于两个或多个条件的所有匹配值:

要使用Vlookup并水平返回基于更特定值的所有匹配值,通用公式为:

=IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2))), ROW(return_range) - m, ""), COLUMN() - n)),"")
备注: m 是返回范围减去1的第一个单元格的行号。
      n 是第一个公式单元格的列号减去1。

1。 将以下公式应用于要输出结果的空白单元格:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($F1=$A$2:$A$20)) * (--($F2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), COLUMN()-5)),"")

2。 然后,选择公式单元格并将填充手柄拖动到右侧单元格,直到显示空白单元格,并且已返回基于特定条件的所有匹配值,请参见屏幕截图:

备注:有关更多条件,您只需要将lookup_value和lookup_range连接到公式中,例如: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), COLUMN() - n)),"").


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

Vlookup垂直基于一个条件的所有匹配值:

若要进行Vlookup并垂直返回基于一个特定值的所有匹配值,通用公式为:

=IFERROR(INDEX(return_range, SMALL(IF(lookup_value = lookup_range, ROW(return_range )- m ,""), ROW() - n )),"")
备注: m 是返回范围减去1的第一个单元格的行号。
      n 是第一个公式单元格的行号减去1。

1。 将以下公式复制或键入到要获取结果的单元格中,然后按 Ctrl + Shift + Enter 键一起获得第一个匹配的值,请参见屏幕截图:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(E$2=$A$2:$A$20, ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2。 然后,选择第一个公式单元格,然后将填充手柄向下拖动到其他单元格,直到显示空白单元格,并且所有相应项都已列在列中,请参见屏幕截图:

提示:

要忽略返回的匹配值中的重复项,请使用以下公式: =IFERROR(INDEX($C$2:$C$20,MATCH(0,COUNTIF($F$1:F1,$C$2:$C$20)+($A$2:$A$20<>$E$2),0)),"")

然后按 Ctrl + Shift + Enter 键一起获得第一个匹配的值,然后将该公式单元格向下拖动到其他单元格,直到显示空白单元格,您将根据需要获得结果:


Vlookup垂直基于两个或多个条件的所有匹配值:

要使用Vlookup并垂直返回基于更具体值的所有匹配值,通用公式为:

=IFERROR(INDEX(return_range, SMALL(IF(1=((--(lookup_value1=lookup_range1)) * ( --(lookup_value2=lookup_range2))), ROW(return_range)-m,""), ROW()-n)),"")
备注: m 是返回范围减去1的第一个单元格的行号。
      n 是第一个公式单元格的行号减去1。

1。 将以下公式复制到一个空白单元格,然后按 Ctrl + Shift + Enter 键一起获得第一个匹配项。

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20)) * (--($F$2=$B$2:$B$20))), ROW($C$2:$C$20)-1,""), ROW()-1)),"")

2。 然后将公式单元格向下拖动到其他单元格,直到显示空白单元格,请参见屏幕截图:

备注:有关更多条件,您只需要将lookup_value和lookup_range连接到公式中,例如: =IFERROR(INDEX(return_range, SMALL(IF(1 = ((--(lookup_value1=lookup_range1)) * (--(lookup_value2 = lookup_range2) * (--(lookup_value3 =lookup_range3)))), ROW(return_range) - m, ""), ROW() - n)),"").


Vlookup将基于两个或多个条件的所有匹配值放入单个单元格

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

Vlookup根据一个条件将所有匹配值放入单个单元格:

请将以下简单公式应用于空白单元格,然后按 Ctrl + Shift + Enter 键一起得到结果:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

提示:

要忽略返回的匹配值中的重复项,请使用以下公式: =TEXTJOIN(",", TRUE, IF(IFERROR(MATCH($C$2:$C$20, IF(F1=$A$2:$A$20, $C$2:$C$20, ""), 0),"")=MATCH(ROW($C$2:$C$20), ROW($C$2:$C$20)), $C$2:$C$20, ""))


Vlookup将基于两个或多个条件的所有匹配值放入单个单元格:

要在将所有匹配值返回到单个单元格中时处理多个条件,请应用以下公式,然后按 Ctrl + Shift + Enter 键一起得到结果:

=TEXTJOIN(",",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

备注:

1. TEXTJOIN函数仅在Excel 2019和Office 365中可用。

2.如果使用Excel 2016和更早版本,请使用以下文章的用户定义函数:


4。 Vlookup返回匹配单元格的整个或整个行

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

1。 请将以下公式复制或键入到要输出结果的空白单元格中,然后按 输入 获得第一个值的关键。

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

2。 然后,将公式单元格拖动到右侧,直到显示整行的数据为止,请参见屏幕截图:

备注:在以上公式中, F2 是您要基于其返回整行的查找值, A1:D12 是您要使用的数据范围, A1 表示数据范围内的第一列号。

提示:

如果根据匹配的值找到多个行,要返回所有对应的行,请应用以下公式: =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),""),然后按 Ctrl + Shift + Enter 键一起获得第一个结果,然后将填充手柄向右拖动到单元格,请参见屏幕截图:

然后在单元格中向下拖动填充手柄,以获取所有匹配的行,如下图所示:


5。 在Excel中执行多个Vlookup功能(嵌套Vlookup)

有时,您可能想在多个表中查找值,如果任何一个表包含给定的查找值,如下面的屏幕截图所示,在这种情况下,您可以将一个或多个Vlookup函数与IFERROR函数组合在一起以执行多个查找。

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

=IFERROR(VLOOKUP(lookup_value,table1,col,0),IFERROR(VLOOKUP(lookup_value,table2,col,0),VLOOKUP(lookup_value,table3,col,0)))

请注意:

  • Lookup_Array中:您正在寻找的价值;
  • Table1, Table2, Table3,...:存在查找值和返回值的表;
  • 山坳:要从中返回匹配值的表中的列号。
  • 0:用于完全匹配。

1。 请将以下公式应用于要放入结果的空白单元格:

=IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),VLOOKUP(J3,$G$3:$H$7,2,0)))

2。 然后,将填充手柄向下拖动到要应用此公式的单元格上,并且所有匹配的值都已返回,如下图所示:

备注:

  • 1.在上式中 J3 是您正在寻找的价值; A3:B7, D3:E7, G3:H7 查找值和返回值所在的表范围; 号码 2 是要从中返回匹配值的范围内的列号。
  • 2.如果找不到查找值,则会显示一个错误值,用可读文本替换错误,请使用以下公式: =IFERROR(VLOOKUP(J3,$A$3:$B$7,2,0),IFERROR(VLOOKUP(J3,$D$3:$E$7,2,0),IFERROR(VLOOKUP(J3,$G$3:$H$7,2,0),"can't find")))

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

Vlookup函数还可以帮助您检查值是否基于另一个列表,例如,如果您要在C列中查找名称,并且如果在A列中找到了该名称,则只返回Yes或No,如下图所示。如图所示。

1。 请将以下公式应用于空白单元格:

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

2。 然后,将填充手柄向下拖动到要填充此公式的单元格,您将根据需要获得结果,请参见屏幕截图:

备注:在以上公式中, C2 是您要检查的查找值; A2:A10 是从中查找值的范围列表; 号码 1 是您要从中获取范围值的列号。


7。 Vlookup并对行或列中的所有匹配值求和

如果使用数字数据,有时,从表中提取匹配的值时,可能还需要将几列或多行中的数字求和。 本节将介绍一些公式以在Excel中完成此工作。

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

假设您有一个具有几个月销售量的产品列表,如下图所示,现在,您需要根据给定产品汇总所有月份的所有订单。

Vlookup并将行中的第一个匹配值求和:

1。 请复制下面的公式或将其输入到空白单元格中,然后按 Ctrl + Shift + Enter 键在一起以获得第一个结果。

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

2。 然后,向下拖动填充手柄以将此公式复制到所需的其他单元格中,并且第一个匹配值所在行中的所有值已累加在一起,请参见屏幕截图:

备注:在上式中: H2 是包含您要查找的值的单元格; A2:F9 是包含查找值和匹配值的数据范围(无列标题); 号码 2,3,4,5,6 {} 是用于计算范围总数的列号。


Vlookup并求和多行中所有匹配的值:

上面的公式只能对第一个匹配值的行中的值求和。 如果要对多行中的所有匹配项求和,请使用以下公式,然后将填充手柄向下拖动到要应用此公式的单元格,您将获得所需的结果,请参见屏幕截图:

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

备注:在上式中: H2 是您要查找的查找值; A2:A9 是包含查找值的行标题; B2:F9 要求和的数值的数据范围。


Vlookup并对一列或多列中的所有匹配值求和

Vlookup并对列中的第一个匹配值求和:

如果要汇总特定月份的总价值,如下面的屏幕快照所示。

将以下公式应用到空白单元格中,然后向下拖动填充手柄以将此公式复制到其他单元格中,现在,已将基于列中特定月份的第一个匹配值相加,请参见屏幕截图:

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

备注:在上式中: H2 是您要查找的查找值; B1:F1 是包含查找值的列标题; B2:F9 要求和的数值的数据范围。


Vlookup并对多个列中的所有匹配值求和:

若要Vlookup并对多列中的所有匹配值求和,应使用以下公式:

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

备注:在上式中: H2 是您要查找的查找值; B1:F1 是包含查找值的列标题; B2:F9 要求和的数值的数据范围。


Vlookup并使用功能强大的功能对第一个匹配或所有匹配的值求和

也许上面的公式很难记住,在这种情况下,我将推荐一个方便的功能- 查找和总和 of Kutools for Excel,使用此功能,您可以尽可能轻松地获得结果。    点击立即下载Kutools for Excel!


Vlookup并对行和列中的所有匹配值求和

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

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

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

备注:在上式中: B2:F9 是要求和的数值的数据范围; B1:F1 is 列标题包含您要求和的查找值; I2 是您要查找的列标题中的查找值; A2:A9 行标题包含您要求和的查找值; H2 是要查找的行标题内的查找值。


8。 Vlookup合并基于一个或多个键列的两个表

在日常工作中,分析数据时,您可能需要根据一个或多个关键列将所有必要的信息收集到一个表中。 为了解决此问题,Vlookup函数也可以帮您一个忙。

Vlookup合并基于一个键列的两个表

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

公式1:使用VLOOKUP函数

要将两个表基于一个键列合并为一个表,请将以下公式应用于要获取结果的空白单元格中,然后将填充手柄向下拖动到要应用此公式的单元格中,获得一个合并的表,其中的order列基于键列数据连接到第一个表数据。

=VLOOKUP($A2,$E$2:$F$8,2,FALSE)

备注:在以上公式中, A2 是您想要的价值, E2:F8 是要搜索的表,数字 2 是表中从中检索值的列号。

公式2:使用INDEX和MATCH函数

如果您的常用数据在第二个表的右侧,而返回的数据在左侧,则为了合并订单列,Vlookup函数将无法完成此工作。 要从右向左查找,可以使用INDEX和MATCH函数替换Vlookup函数。

请复制以下公式或将其输入到空白单元格中,然后将该公式复制到该列的下方,并且订单列已加入到第一张表中,请参见屏幕截图:

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

备注:在以上公式中, A2 是您要查找的查找值, E2:E8 是您要返回的数据范围, F2:F8 是包含查找值的查找范围。


Vlookup合并基于多个键列的两个表

如果要连接的两个表具有多个键列,要基于这些公共列合并表,则INDEX和MATCH函数可以为您提供帮助。

基于多个键列合并两个表的通用公式为:

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

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

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

备注:在以上公式中,单元格引用表示的内容如下所示:

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

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

9。 跨多个工作表的Vlookup匹配值

您是否曾经尝试过在多个工作表中使用Vlookup值? 假设我具有以下三个具有数据范围的工作表,现在,我想根据这三个工作表中的条件获取部分相应的值,以得到如下所示的结果。 在这种情况下, 跨多个工作表的Vlookup值 本教程可能会逐步帮助您。


VLOOKUP匹配值保持单元格格式

1。 Vlookup获取单元格格式(单元格颜色,字体颜色)以及查找值

众所周知,普通的Vlookup函数只能帮助我们从另一个数据范围返回匹配的值,但是有时,您可能希望返回相应的值以及单元格格式,例如填充颜色,字体颜色,字体样式如下图所示。 本节将讨论如何在Excel中使用返回值获取单元格格式。

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

1。 在工作表中包含您要Vlookup的数据,右键单击工作表选项卡,然后选择 查看代码 从上下文菜单中。 看截图:

2。 在开 Microsoft Visual Basic应用程序 窗口,请将下面的VBA代码复制到“代码”窗口中。

VBA代码1:Vlookup,以获取单元格格式以及查找值

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

3。 仍在 Microsoft Visual Basic应用程序 窗口中,单击 插页 > 模块,然后将下面的VBA代码2复制到“模块”窗口中。

VBA代码2:Vlookup,以获取单元格格式以及查找值

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

4。 插入以上代码后,然后单击 工具 > 參考資料Microsoft Visual Basic应用程序 窗口。 然后检查 Microsoft脚本运行时 复选框 参考– VBAProject 对话框。 查看屏幕截图:

5。 然后,点击 OK 关闭对话框,然后保存并关闭代码窗口,现在,返回工作表,然后应用以下公式: =LookupKeepFormat(E2,$A$1:$C$10,3) 放入要输出结果的空白单元格,然后按Enter键。 看截图:

备注:在以上公式中, E2 是您将要寻找的价值, A1:C10 是表格范围和数字 3 是您要返回匹配值的表的列号。

6。 然后,选择第一个结果单元格,然后向下拖动填充手柄以获取所有结果及其格式。 查看截图。


2。 保留Vlookup返回值的日期格式

通常,使用Vloook函数查找并返回匹配的日期格式值时,将显示一些数字格式,如下图所示。 为了避免返回结果中的日期格式,应将TEXT函数包含在Vlookup函数中。

请将以下公式应用到空白单元格中,然后拖动填充手柄以将此公式复制到其他单元格中,并且所有匹配的日期都已返回,如下图所示:

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

备注:在以上公式中, E2 是外观价值, A2:C9 是查询范围,数字 3 是您要返回值的列号, mm/dd/yyy 是您要保留的日期格式。


3。 Vlookup并返回带有单元格注释的匹配值

您是否曾尝试过Vlookup不仅返回匹配的单元格数据,还返回Excel中的单元格注释,如下面的屏幕截图所示? 为了解决此任务,下面的用户定义函数可以帮您一个忙。

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。 然后保存并关闭代码窗口,输入以下公式: =vlookupcomment(D2,$A$2:$B$9,2,FALSE) 放入空白单元格中以找到结果,然后拖动填充手柄以将此公式复制到其他单元格中,现在,匹配的值以及注释将立即返回,请参见屏幕截图:

备注:在以上公式中, D2 是您要返回其对应值的查找值, A2:B9 是您要使用的数据表,数字 2 是包含您要返回的匹配值的列号。


4。 在Vlookup中处理文本和实数

例如,我有一个数据范围,原始表中的ID号是数字格式,在存储为文本的查找单元格中,应用常规的Vlookup函数时,显示以下错误#N / A错误结果如图所示。 在这种情况下,如果表中的查找号和原始号具有不同的数据格式,如何获得正确的信息?

要在Vlookup函数中处理文本和实数,请将以下公式应用于空白单元格,然后向下拖动填充手柄以复制此公式,您将获得正确的结果,如下图所示:

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

备注:

  • 1.在上式中 D2 是您要返回其对应值的查找值, A2:B8 是您要使用的数据表,数字 2 是包含您要返回的匹配值的列号。
  • 2.如果不确定在哪里有数字和在哪里有文本,此公式也可以很好地工作。

下载VLOOKUP示例文件

Vlookup_basic_examples.xlsx

Advanced_Vlookup_examples.xlsx

Vlookup_keep_cell_formatting.zip



  • 超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 和保存数据; 拆分单元格内容; 合并重复的行和总和/平均值...防止细胞重复; 比较范围...
  • 选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
  • 确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
  • 收藏并快速插入公式,范围,图表和图片; 加密单元 带密码 创建邮件列表 并发送电子邮件...
  • 提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
  • 结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
  • 数据透视表分组依据 周号,周几等 显示未锁定的单元格 用不同的颜色 突出显示具有公式/名称的单元格...
kte选项卡201905
  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.