使用 VVLOOKUP、INDEX 或 MATCH 查找值

提示: 请尝试使用新的 XLOOKUPXMATCH 函数,这是本文中介绍的函数的改进版本。 这些新函数在任意方向工作,并默认返回精确匹配项,使其比前置任务更易于使用。

假设你有一个办公室位置号码列表,并且需要知道每个办公室中的员工。 电子表格很大,因此你可能会认为它的任务很有挑战性。 使用查找函数实际上非常简单。

VLOOKUPHLOOKUP函数以及INDEXMATCH是 Excel 中一些最有用的函数。

注意: "查找向导"功能在 Excel 中不再可用。

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

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

此示例中,B2 是第一 个参数,即函数需要工作的数据元素。 对于 VLOOKUP,第一个参数是要查找的值。 此参数可以是单元格引用,固定值如"smith"或 21,000。 第二个参数是单元格区域 C2-:E7,用于搜索要查找的值。 第三个参数是包含所查找值的单元格区域中的列。

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

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

VLOOKUP 函数的典型用法

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

如果熟悉 VLOOKUP,则 HLOOKUP 函数同样易于使用。 输入相同的参数,但它以行而不是列进行搜索。

使用 INDEX 和 MATCH 而不是 VLOOKUP

使用 VLOOKUP 存在一些限制 - VLOOKUP 函数只能从左到右查找值。 这意味着包含您查找的值的列应始终位于包含返回值的列的左侧。 如果电子表格不是这样构建的,则请勿使用 VLOOKUP。 请改为使用 INDEX 和 MATCH 函数的组合。

此示例显示一个小列表,其中我们要搜索的值 Chicago 不在最左侧的列中。 因此,我们不能使用 VLOOKUP。 我们将使用 MATCH 函数查找区域 B1:B11 中的 Chicago。 它位于第 4 行。 然后,INDEX 使用该值作为查找参数,在列 D 的第 4 列中查找 (芝加哥) 。 使用的公式显示在单元格 A14 中。

使用 INDEX 和 MATCH 查找值

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

不妨尝试一下

如果要在尝试使用自己的数据之前先体验查找函数,下面是一些示例数据。

VLOOKUP 工作示例

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

提示: 将数据粘贴到 Excel 中之前,将列 A 到 C 的列宽设置为 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 到 C 的列宽设置为 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 示例

最后一个示例同时使用 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

需要更多帮助?

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

此信息是否有帮助?

谢谢您的反馈!

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

×