使用 VVLOOKUP、INDEX 或 MATCH 查找值

提示: 尝试使用新的XLOOKUPXMATCH函数,改进了本文中介绍的函数版本。 这些新函数在任何方向上都是正常的,并且在默认情况下返回精确匹配,这使它们比它们的前置任务更方便、更方便使用。

假设您有一个 office 位置编号列表,并且您需要了解每个办公室中有哪些员工。 电子表格非常大,因此你可能认为它是一种富有挑战性的任务。 使用 lookup 函数实际上非常简单。

VLOOKUPHLOOKUP函数与INDEXMATCH一起是 Excel 中一些最有用的函数。

注意: Excel 中已不再提供 "查阅向导" 功能。

下面是如何使用 VLOOKUP 的示例。

=VLOOKUP(B2,C2:E7,3,TRUE)

在此示例中,B2 是第一个参数-函数需要使用的数据元素。 对于 VLOOKUP,此第一个参数是要查找的值。 此参数可以是单元格引用,也可以是固定值,如 "smith" 或21000。 第二个参数是单元格区域(C2-: E7,可在其中搜索要查找的值。 第三个参数是包含您要查找的值的单元格区域中的列。

第四个参数可选。 请输入 TRUE 或 FALSE。 如果输入 TRUE 或留空此参数,则函数将返回第一个参数中指定的值的近似匹配。 如果输入 FALSE,函数将匹配第一个参数提供的值。 换句话说,将第四个参数保留为空(或输入 TRUE)可提供更大的灵活性。

此示例演示函数的工作方式。 当您在单元格 B2 (第一个参数)中输入值时,VLOOKUP 将搜索单元格区域 C2: E7 (第二个参数)中的单元格,并返回从区域的第三列中最接近的近似匹配值,列 E (第三个参数)。

VLOOKUP 函数的典型用法

第四个参数为空,因此该函数返回近似匹配。 如果未返回结果,必须输入 C 或 D 列中的某个值才能得到结果。

当您熟悉 VLOOKUP 时,HLOOKUP 函数同样易于使用。 输入相同的参数,但它在行中搜索,而不是列。

使用 INDEX 和 MATCH 而不是 VLOOKUP

使用 VLOOKUP 有某些限制-VLOOKUP 函数只能从左到右查找值。 这意味着,包含你所查找的值的列应始终位于包含返回值的列的左侧。 现在,如果您的电子表格不是以这种方式生成的,则不要使用 VLOOKUP。 改为使用索引和匹配函数的组合。

此示例显示了一个较小的列表,其中我们要搜索的值不在最左侧的列中。 因此,我们无法使用 VLOOKUP。 相反,我们将使用 MATCH 函数查找范围 B1: B11 中的芝加哥。 它位于第4行。 然后,INDEX 使用该值作为 lookup 参数,并在第4列(列 D)中查找芝加哥的人口。 单元格 A14 中显示了所使用的公式。

使用 INDEX 和 MATCH 查找值

有关使用 INDEX 和 MATCH (而不是 VLOOKUP)的更多示例,请参阅 Microsoft MVP https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/的 "帐单 Jelen" 一文。

不妨尝试一下

如果想要在用自己的数据尝试查找函数之前试用查找函数,请参阅下面的示例数据。

在工作中使用 VLOOKUP 示例

将以下数据复制到空白电子表格中。

提示: 将数据粘贴到 Excel 之前,请将列 A 到250像素的列宽设置为像素,然后单击 "自动换行" ("开始" 选项卡,"对齐方式" 组)。

密度

粘度

温度

0.457

3.55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

公式

说明

结果

=VLOOKUP(1,A2:C10,2)

使用近似匹配搜索 A 列中的值 1,在 A 列中找到小于等于 1 的最大值 0.946,然后返回同一行中 B 列的值。

2.17

=VLOOKUP(1,A2:C10,3,TRUE)

使用近似匹配搜索 A 列中的值 1,在 A 列中找到小于等于 1 的最大值 0.946,然后返回同一行中 C 列的值。

100

=VLOOKUP(0.7,A2:C10,3,FALSE)

使用精确匹配在 A 列中搜索值 0.7。因为 A 列中没有精确匹配的值,所以返回一个错误值。

#N/A

=VLOOKUP(0.1,A2:C10,2,TRUE)

使用近似匹配在 A 列中搜索值 0.1。因为 0.1 小于 A 列中最小的值,所以返回一个错误值。

#N/A

=VLOOKUP(2,A2:C10,2,TRUE)

使用近似匹配搜索 A 列中的值 2,在 A 列中找到小于等于 2 的最大值 1.29,然后返回同一行中 B 列的值。

1.71

HLOOKUP 示例

复制此表中的所有单元格,然后将其粘贴到空白 Excel 工作表的单元格 A1 中。

提示: 将数据粘贴到 Excel 之前,请将列 A 到250像素的列宽设置为像素,然后单击 "自动换行" ("开始" 选项卡,"对齐方式" 组)。

车轴

轴承

螺钉

4

4

9

5

7

10

6

8

11

公式

说明

结果

=HLOOKUP("车轴", A1:C4, 2, TRUE)

在首行查找车轴,并返回同列(列 A)中第 2 行的值。

4

=HLOOKUP("轴承", A1:C4, 3, FALSE)

在首行查找轴承,并返回同列(列 B)中第 3 行的值。

7

=HLOOKUP("B", A1:C4, 3, TRUE)

在首行查找 B,并返回同列中第 3 行的值。 因为找不到 B 的完全匹配项,将使用第 1 行列 A 中小于 B 的最大值 "车轴"。

5

=HLOOKUP("螺栓", A1:C4, 4)

在首行查找螺栓,并返回同列(列 C)中第 4 行的值。

11

=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

在三行数组常量中查找数字 3,并返回同列(本例中为第三列)中第 2 行的值。 数组常量中有三行数值,并且每行都用分号 (;) 分隔。 因为在第 2 行和第 3 列(同一列)中找到 c,因此将返回 c。

c

索引和匹配示例

最后一个示例将 INDEX 和 MATCH 函数一起使用,以返回每五个城市的最早发票编号及其对应日期。 由于日期返回为数字,我们使用 TEXT 函数将其格式设置为日期。 INDEX 函数实际使用 MATCH 函数的结果作为其参数。 INDEX 和 MATCH 函数的组合在每个公式中使用两次,首先返回发票编号,然后返回日期。

复制此表中的所有单元格,然后将其粘贴到空白 Excel 工作表的单元格 A1 中。

提示: 在将数据粘贴到 Excel 之前,请将列 A 到 D 的列宽设置为250像素,然后单击 "自动换行" ("开始" 选项卡,"对齐方式" 组)。

发票

城市

发票日期

按城市显示的最早发票,包含日期

3115

亚特兰大

12/4/7

="广州 = "&INDEX($A$2:$C$33,MATCH("广州",$B$2:$B$33,0),1)& ", 发票日期:" & TEXT(INDEX($A$2:$C$33,MATCH("广州",$B$2:$B$33,0),3),"yy/m/d")

3137

亚特兰大

12/4/9

="武汉 = "&INDEX($A$2:$C$33,MATCH("武汉",$B$2:$B$33,0),1)& ", 发票日期:" & TEXT(INDEX($A$2:$C$33,MATCH("武汉",$B$2:$B$33,0),3),"yy/m/d")

3154

亚特兰大

12/4/11

="张家口 = "&INDEX($A$2:$C$33,MATCH("张家口",$B$2:$B$33,0),1)& ", 发票日期:" & TEXT(INDEX($A$2:$C$33,MATCH("张家口",$B$2:$B$33,0),3),"yy/m/d")

3191

亚特兰大

12/4/21

="三亚 = "&INDEX($A$2:$C$33,MATCH("三亚",$B$2:$B$33,0),1)& ", 发票日期:" & TEXT(INDEX($A$2:$C$33,MATCH("三亚",$B$2:$B$33,0),3),"yy/m/d")

3293

广州

12/4/25

="长春 = "&INDEX($A$2:$C$33,MATCH("长春",$B$2:$B$33,0),1)& ", 发票日期:" & TEXT(INDEX($A$2:$C$33,MATCH("长春",$B$2:$B$33,0),3),"yy/m/d")

3331

亚特兰大

12/4/27

3350

亚特兰大

12/4/28

3390

亚特兰大

12/5/1

3441

亚特兰大

12/5/2

3517

广州

12/5/8

3124

武汉

12/4/9

3155

武汉

12/4/11

3177

武汉

12/4/19

3357

武汉

12/4/28

3492

武汉

12/5/6

3316

张家口

12/4/25

3346

张家口

12/4/28

3372

张家口

12/5/1

3414

张家口

12/5/1

3451

张家口

12/5/2

3467

张家口

12/5/2

3474

张家口

12/5/4

3490

张家口

12/5/5

3503

张家口

12/5/8

3151

三亚

12/4/9

3438

三亚

12/5/2

3471

三亚

12/5/4

3160

长春

12/4/18

3328

长春

12/4/26

3368

长春

12/4/29

3420

长春

12/5/1

3501

长春

12/5/6

另请参阅

课程摘要卡: vlookup 复习

查找和引用函数(参考)

在 VLOOKUP 函数中使用 table_array 参数

扩展你的 Office 技能
了解培训
抢先获得新功能
加入 Office 预览体验计划

此信息是否有帮助?

谢谢您的反馈!

谢谢你的反馈! 可能需要转接到 Office 支持专员。

×