2.2.6 高效的匹配查找函数

快速查找匹配某个单元格或者区域的数值，可以用Excel函数中的匹配查找相关函数，例如，CHOOSE、VLOOKUP、HLOOKUP、LOOKUP、MATCH、INDEX、OFFSET、INDIRECT等。工作中经常会碰到匹配数据的场景，例如，从大量用户的消费数据中匹配出某几个人的消费数据，此时可以用VLOOKUP、LOOKUP或MATCH结合INDEX函数等方法进行数据匹配。此外，基于某个起点进行位移而获取其他单元格区域的数据可以使用OFFSET函数。下面通过实例对常用的匹配查找函数进行举例说明。

1.CHOOSE函数

功能说明：根据索引号index_num返回数值参数列表中的数值。

语法：CHOOSE(index_num,value1,[value2], …)

参数：

● index_num必需。用于指定所选定的数值参数。index_num必须是介于1到254的数字，或是包含1到254的数字的公式或单元格引用。

● value1,[value2],…,value1必需，后续值可选。1到254个数值参数，CHOOSE将根据index_num从中选择一个数值或一项要执行的操作。参数可以是数字、单元格引用、定义的名称、公式、函数或文本。

示例：以某班级的学生成绩表为例，数据如表2-9所示。字段包括姓名、成绩。数据位于区域A1:B5，区域D2:D4是CHOOSE函数相关公式，区域E2:E4是计算说明，区域F2:F4是计算结果。

公式与步骤：

● 示例一：单元格F2内输入公式“=CHOOSE(2,A2,A3, A4,A5)”。

● 示例二：单元格F3内输入公式“=CHOOSE(3,B2,B3,B4,B5)”。

● 示例三：单元格F4内输入公式“=SUM(CHOOSE(2,A2:A5,B2:B5))”，结果如图2-85所示。

提示：

● index_num必须是介于1到254的数字。

● 如果index_num是一个数组，则在计算函数CHOOSE时，使用数组公式，可以返回对应的每一个值。

● CHOOSE函数的value参数除了单个数值或单个单元格引用之外，也可以为区域引用。如图2-85中单元格F4里面的公式就是对区域的引用。

2.VLOOKUP函数

功能说明：将查找值在某个区域中的第一列进行查找，根据列号返回右侧第col_index_num列与查找值处于同行的数值。

语法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

参数：

● lookup_value必需。要查找的值。

● table_array必需。要在其中查找值的区域。

● col_index_num必需。区域中包含返回值的列号。

● range_lookup可选。精确匹配或近似匹配，精确匹配用0或FALSE指代，近似匹配用1或TRUE指代。参数省略时默认为近似匹配。

示例：以某学校的学生信息成绩表为例，数据如表2-10所示。字段包括ID、班级、姓名、性别、语文成绩、数学成绩、英语成绩。要求根据姓名来查找学生的语文、数学、英语成绩。

公式与步骤：单元格E11内输入公式“=VLOOKUP($D11,$C$1:$G$8,COLUMN(C1), 0)”，然后向右向下拖拽复制公式。这里lookup_value所在的单元格D11要绝对引用列（$D11）；table_array选择的范围C1:G8也要绝对引用行和列（$C$1:$G$8）；由于查找的学生的语文、数学、英语成绩的位置和数据源一致，因此col_index_num可以选择用COLUMN函数进行相对引用；range_lookup参数设置成0，进行精确匹配，结果如图2-86所示。

提示：

● lookup_value可以为模糊值，例如，查找姓李的学生的成绩，姓名使用“李*”。

● table_array的第一列必须是lookup_value查找范围的所在列，范围选择方向从左往右。

● VLOOKUP函数的column_index_num必须是大于0的整数。

● 如需精确匹配，最后一个参数设置成0或FALSE。

● 当查找的数据在查找范围内有重复的时候，返回查找范围内第一列首次出现的查找值所对应的数值。

3.HLOOKUP函数

功能说明：将查找值在某个区域中的第一行进行查找，根据行号返回下方第row_index_num行与查找值处于同列的数值。

语法：HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

参数：

● lookup_value必需。要查找的值。

● table_array必需。要在其中查找值的区域。

● row_index_num必需。区域中包含返回值的行号。

● range_lookup可选。精确匹配或近似匹配，精确匹配用0或FALSE指代，近似匹配用1或TRUE指代。参数省略时默认为近似匹配。

示例：以某企业的员工薪资表为例，数据如表2-11所示。字段名称位于第一列，包括工号、姓名、薪资、奖金。要求根据姓名来查找员工的薪资和奖金。

公式与步骤：单元格G7内输入公式“=HLOOKUP($F7,$A$2:$H$4,COLUMN(B1),0)”，然后向右向下拖拽复制公式，结果如图2-87所示。

提示：

● HLOOKUP与VLOOKUP功能非常相似，都是进行匹配查找的函数，且函数参数相同。唯一的区别是VLOOKUP函数是在列上面进行查找，而HLOOKUP函数是在行上面查找。

● lookup_value可以为模糊值，例如，查找姓刘且名字长度为2的员工对应的薪资和奖金，姓名可以使用“刘?”。

● table_array的第一行必须是lookup_value查找范围的所在行，范围选择方向从上往下。

● HLOOKUP函数的row_index_num必须是大于0的整数。

● 如需精确匹配，最后一个参数设置成0或FALSE。

● 当查找的数据在查找范围内有重复的时候，返回查找范围内第一行首次出现的查找值所对应的数值。

4.LOOKUP函数

功能说明：将查找值在一行或一列进行查找，返回一行或列中的相同位置的数值。LOOKUP函数可以进行精确匹配和近似匹配。

语法：

● LOOKUP(lookup_value,array)

● LOOKUP(lookup_value,lookup_vector,[result_vector])

参数：

● lookup_value必需。LOOKUP在第一个向量中搜索的值。可以是数字、文本、逻辑值、名称或对值的引用。

● lookup_vector必需。只包含一行或一列的区域。可以是文本、数字或逻辑值。

● result_vector可选。只包含一行或一列的区域。

（1）精确匹配（查找范围和返回范围一致）

以人物攻击力表为例，数据如表2-12所示。字段包括性别、姓名、攻击力。数据位于区域A1:C7，区域E2:E3是查找值，区域F2:F3是返回值。

公式与步骤：

1）选中区域A1:C7，单击“数据|排序和筛选|排序”按钮，在“排序”对话框中勾选“数据包含标题”选项，“主要关键字”栏中筛选“姓名”，“次序”栏中筛选“升序”，然后单击“确定”按钮。

2）单元格F2内输入公式“=LOOKUP(E2,$B$2: $B$7)”，然后向下拖拽复制公式，结果如图2-88所示。

提示：

● 查找范围array数组中的值必须按升序排列：…, -2, -1, 0, 1, 2, …, A～Z, FALSE, TRUE，否则LOOKUP可能无法返回正确的值；文本不区分大小写。

● 沙和尚、孙悟空在B列中存在，可以返回精确的数值。

（2）近似匹配（查找范围和返回范围一致）

以人物攻击力表为例，数据如表2-12所示。字段包括性别、姓名、攻击力。数据位于区域A1:C7，单元格E2是查找值，单元格F2是返回值。

公式与步骤：

1）选中区域A1:C7，单击“数据|排序和筛选|排序”按钮，在“排序”对话框中勾选“数据包含标题”选项，“主要关键字”栏中筛选“姓名”，“次序”栏中筛选“升序”，然后单击“确定”按钮。

2）单元格F2内输入公式“=LOOKUP(E2,B2:B7)”，结果如图2-89所示。

提示：

● 查找范围array数组中的值必须按升序排列：…, -2, -1, 0, 1, 2, …, A～Z, FALSE, TRUE，否则LOOKUP可能无法返回正确的值；文本不区分大小写。

● 女儿国国王在B列中不存在，返回小于等于查找值的最大值。这里由于女儿国国王在B列里面的按照升序排序的位置在嫦娥仙子的下面（拼音排序法），所以返回嫦娥仙子。

（3）查找范围和返回范围不一致

以人物攻击力表为例，数据如表2-12所示。字段包括性别、姓名、攻击力。数据位于区域A1:C7，区域E2:E4是查找值，区域F2:G4分别是攻击力和性别的返回值。

公式与步骤：

1）选中区域A1:C7，单击“数据|排序和筛选|排序”按钮，在“排序”对话框中勾选“数据包含标题”选项，“主要关键字”栏中筛选“姓名”，“次序”栏中筛选“升序”，然后单击“确定”按钮。

2）单元格F2内输入公式“=LOOKUP(E2,$B$2:$B$7,C$2:C$7)”。

3）单元格G2内输入公式“=LOOKUP(E2,$B$2:$B$7,A$2:A$7)”。

4）同时选中F2:G2区域，然后向下拖拽复制公式，结果如图2-90所示。

提示：

● 查找范围lookup_vector向量中的值必须按升序排列：…, -2, -1, 0, 1, 2, …, A～Z,FALSE, TRUE，否则LOOKUP可能无法返回正确的值；文本不区分大小写。

● result_vector可以省略。只包含一行或一列的区域。result_vector参数必须与lookup_vector参数大小相同。

● 如果lookup_value在lookup_vector中不存在，返回小于等于查找值的最大值。所以查找女儿国国王对应的数据返回的是嫦娥仙子的攻击力和性别。

5.MATCH函数

功能说明：在区域内搜索特定的项，然后返回该项在此区域中的相对位置。

语法：MATCH(lookup_value,lookup_array,[match_type])

参数：

● lookup_value必需。要在lookup_array中匹配的值。

● lookup_array必需。要搜索的单元格区域。

● match_type可选。数字-1、0或1。参数的默认值为1。

（1）精确匹配

以某超市的商品信息表为例，数据如表2-13所示。字段包括商品名称、价格、数量。数据位于区域A1:C6。区域F2:F4是说明，区域G2:G4是计算结果。

公式与步骤：

● 示例一：单元格G2内输入公式“=MATCH("毛巾",A1:A6,0)”。

● 示例二：单元格G3内输入公式“=MATCH(19, B1:B6,0)”。

● 示例三：单元格G4内输入公式“=MATCH(20,C1:C6,0)”，结果如图2-91所示。

提示：如需精确匹配，参数match_type必须为0。

（2）近似匹配

以某超市的商品信息表为例，数据如表2-13所示。字段包括商品名称、价格、数量。数据位于区域A1:C6。要求查找数量35在区域C1:C6匹配的位置。

公式：单元格G2内输入公式“=MATCH(35,C1:C6,1)”，结果如图2-92所示。

提示：

● 如需近似匹配，参数match_type的值为1或-1。

● match_type省略或1，查找小于或等于lookup_value的最大值。lookup_array参数中的值必须以升序排序，例如，…,-2,-1, 0, 1, 2, …, A～Z, FALSE, TRUE。

● match_type为-1，查找大于或等于lookup_value的最小值。lookup_array参数中的值必须按降序排列，例如，TRUE, FALSE, Z～A, …, 2, 1, 0,-1,-2, …, 等等。

（3）根据日期判断所属季度

以某超市的商品销售表为例，数据如表2-14所示。字段包括日期、商品名称、数量。要求根据商品销售日期来判断当前日期所属季度。

公式与步骤：单元格D2内输入公式“=MATCH(MONTH(A2),{1,4,7,10},1)”，然后向下拖拽复制公式，结果如图2-93所示。

提示：

● 用MONTH函数取出日期对应的月份，然后用MATCH函数进行近似匹配。这里把日期对应的月份在数组{1,4,7,10}里面进行查找。如果可以匹配，就返回当前月份在数组里面的位置。如果月份在数组里面匹配不到，就返回小于等于当前月份的最大值所属的位置，也就是数组{1,4,7,10}对应的位置。

● 有些月份在数组{1,4,7,10}中并没有出现，需要近似匹配。数组{1,4,7,10}是以升序进行排序的，参数match_type设置为1。

6.INDEX函数

功能说明：返回表格或区域中的值或值的引用。

语法：INDEX(array,row_num,[column_num])

参数：

● array必需。单元格区域或数组常量。

● row_num必需。选择数组中的某行，函数从该行返回数值。

● column_num可选。选择数组中的某列，函数从该列返回数值。

示例：以某企业的销售业绩表为例，数据如表2-15所示。字段包括月份、交单数、业绩。数据位于区域A1:C7。要求匹配查找区域A1:A7中第三个位置对应的数值，区域A1:C1中第三个位置对应的数值，区域A1:C7中第三行和第三列交叉处的数值。

公式与步骤：

● 示例一：单元格G2内输入公式“=INDEX(A1:A7,3)”。

● 示例二：单元格G3内输入公式“=INDEX(A1:C1,3)”。

● 示例三：单元格G4内输入公式“=INDEX(A1:C7, 3,3)”，结果如图2-94所示。

提示：

● 如果array参数仅包含一行或一列，参数row_num或column_num为可选参数。

● 如果array参数中包含多行多列，而且仅使用了row_num或column_num一个参数，函数结果则返回数组中的整行或整列。

● 如果array参数中包含多行多列，而且同时使用了row_num和column_num参数，函数结果则返回某一行和某一列的交叉单元格中的值。

7.OFFSET函数

功能说明：返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。

语法：OFFSET(reference,rows,cols,[height],[width])

参数：

● reference必需。作为偏移基准的参照。

● rows必需。需要左上角单元格引用的向上或向下行数。

● cols可选。需要结果的左上角单元格引用的从左到右的列数。

● height可选。需要返回的引用的行高。

● width可选。需要返回的引用的列宽。

（1）查找并返回某一个单元格数值

以某企业的产品销售业绩表为例，数据如表2-16所示。字段包括省份、电器、服装、日用品。数据位于区域A1:D5。要求以单元格A1作为引用的起始位置，查找并返回浙江省的服装数值。

公式与步骤：单元格G2内输入公式“=OFFSET (A1,2,2,1,1)”，结果如图2-95所示。

提示：如果返回的是某一个单元格的数值，参数height和width可以省略。

（2）查找并返回单元格区域数值

以某企业的产品销售业绩表为例，数据如表2-16所示。字段包括省份、电器、服装、日用品。数据位于区域A1:D5。要求以单元格A1作为查找引用的起始位置，查找并返回“上海”的电器、服装、日用品数值。

公式与步骤：

1）选中区域G2:I2，在单元格G2内输入公式“=OFFSET(A1,3,1,1,3)”。

2）按〈Ctrl+Shift+Enter〉组合键创建数组公式，结果如图2-96所示。

8.INDIRECT函数

功能说明：返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。

语法：INDIRECT(ref_text,[a1])

参数：

● ref_text必需。对单元格的引用，此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。

● [a1]可选。逻辑值，用于指定在ref_text中的引用的类型。参数值为TRUE指定的是A1样式，参数值为FALSE指定R1C1引用样式。

（1）查找返回指定单元格数值

以某门店到访用户人数表为例，数据如表2-17所示。字段包括门店名称、到访用户数。数据位于区域A1:B5。要求查找返回单元格B2、B3以及B4中的引用值。区域D2:D4是INDIRECT函数公式，区域E2:E4是说明，区域F2:F4是计算结果。

公式与步骤：

● 示例一：单元格F2内输入公式“=INDIRECT("B2", TRUE)”。

● 示例二：单元格F3内输入公式“=INDIRECT("R3C2",0)”。

● 示例三：单元格F4内输入公式“=INDIRECT("B"&ROW(B4),1)”，结果如图2-97所示。

提示：

● 参数ref_text一定是引用样式对应的文本类型。

● 如果参数[a1]为TRUE，ref_text采用A1样式；如果参数[a1]为FALSE，ref_text采用R1C1样式。

（2）多个工作表引用合并数据

工作簿中的5个工作表，工作表的名称分别为“union”“st_001”“st002”“st_003”“st_004”，每个工作表里区域A1:A5分别是姓名、性别、年龄、班级、成绩，区域B1:B5分别是各自对应的数值，数据如图2-98所示。要求把4个工作表的学生信息合并到“union”工作表中。

公式与步骤：单元格B2内输入公式“=INDIRECT($A2&"!B"&COLUMN(A1),1)”，然后向下向右拖拽复制公式，结果如图2-99所示。

提示：

●“union”工作表内区域A2:A5的数值分别对应4个工作表的名称，因此可以用单元格引用、固定字符的连接拼凑到对应数值所在工作表内的位置，拼接的位置为“$A2&"!B"&COLUMN(A1)”，此时单元格A2是列绝对引用。

● 公式里面的参数ref_text采用的是A1样式，因此参数[a1]数值为1。

以上是对匹配查找函数的介绍，并采用示例对函数进行了功能讲解，下面的匹配查找函数案例一～案例二是匹配查找函数的应用扩展，对于同一个实例采用了多种方法来解决。

9.匹配查找函数案例一

案例说明：以不同区域的城市对照数据为例，数据如表2-18所示。数据位于单元格区域A1:B13，单元格区域E3:E6为部分城市，要求根据城市信息匹配城市所属区域。

公式与步骤：

● 方法一：

1）复制A列数据到C列作为辅助列。

2）单元格F3内输入公式“=VLOOKUP(E3,B:C,2,0)”，然后向下拖拽复制公式。

● 方法二：单元格G3内输入公式“=VLOOKUP(E3,IF({1,0},$B$2:$B$13,$A$2:$A$13), 2,0)”，然后向下拖拽复制公式。

● 方法三：单元格H3内输入公式“=INDEX($A$1:$B$13,MATCH(E3,$B$1:$B$13, 0),1)”，然后向下拖拽复制公式。

● 方法四：

1）选中单元格区域A1:C13，单击“数据|排序和筛选|排序”命令，在“排序”对话框中“主要关键字”栏筛选“城市”，“次序”栏筛选“升序”，然后单击“确定”按钮。

2）单元格I3内输入公式“=LOOKUP(E3,$B$2:$B$13,$A$2:$A$13)”，然后向下拖拽复制公式。

● 方法五：

1）选中单元格区域A1:C13，单击“数据|排序和筛选|排序”命令，在“排序”对话框中“主要关键字”栏筛选“城市”，“次序”栏筛选“升序”，然后单击“确定”按钮。

2）单元格J3内输入公式“=LOOKUP(1,1/(E3=$B$2:$B$13),$A$2:$A$13)”，然后向下拖拽复制公式，结果如图2-100所示。

10.匹配查找函数案例二

案例说明：以某超市的商品价格变动表和商品出库价目表为例，当商品价格发生变化时会标注对应日期下的价格，其余日期下商品价格不变。要求根据左侧的商品价格变动表填充右侧商品出库价目表，数据如图2-101所示。

公式与步骤：

● 方法一：

1）选中单元格区域A2:F8，按组合键〈F5〉或〈Ctrl+G〉组合键，在“定位”对话框中单击“定位条件”按钮，在“定位条件”对话框中选择“空值”，然后单击“确定”按钮，定位所选区域的空值单元格。

2）按顺序单击键盘上的“=”“←”键，然后按下〈Ctrl+Enter〉组合键，填充空值单元格区域。

3）单元格J4内输入公式

“=INDEX($A$2:$F$8,MATCH(I4,$A$2:$A$8,0),MATCH(H4,$A$2:$F$2,1))”，然后向下拖拽复制公式。

● 方法二：

1）填充空值单元格方法同上述方法一的步骤1和步骤2。

2）单元格K4内输入公式“=VLOOKUP(I4,$A$2:$F$8,MATCH(H4,$A$2:$F$2,1),0)”，然后向下拖拽复制公式。商品最新单价的匹配查找结果如图2-102所示。