Excel Match函数的使用方法的8个实例，含与Sum、OffSet、Indirect组合实现提取数据和对变化区域动态跨表求

在 Excel 中，Match函数用于返回查找值在引用单元格中的相对位置。它共有三个参数，第一个参数为查找值，第二参数为查找区域，第三个参数为匹配选项；匹配选项可以设置为精确匹配和模糊匹配，其中模糊匹配需要排序。

Match函数常与Index、Sum、OffSet、Indirect函数组合使用，其中 Index + Match 或 Indirect + Match 组合都能实现返回行列对应的多个值（即提取数据），OffSet + Match 组合能返回大于等于查找值的最小值对应的某列的值，Sum + OffSet + Indirect + Match 组合实现对变化区域动态跨表求和。以下是Match函数的使用方法的8个实例。

一、Excel Match函数语法

1、表达式：MATCH(LookUp_Value, LookUp_Array, [Match_Type])

中文表达式：MATCH(查找值, 查找区域, [匹配类型])

2、说明：

（1）Match_Type 为可选项，它有三个取值，分别为 1、0 和 -1，如果省略 Match_Type，默认取 1。

A、如果 Match_Type 省略或取 1，则查找小于等查找值的最大值，并且查找区域要按升序排序，否则可能返回不正确的值。

B、如果 Match_Type 取 0，则查找第一个完全等于查找值的值，查找区域不需要排序。

C、如果 Match_Type 取 -1，则查找大于等于查找值的最小值，查找区域要按降序排序，否则可能返回不正确的值。

（2）当 Match_Type 为 0 且查找值是文本，可在查找值中使用通配符“问号 (?) 和星号 (*)”；问号表示任意一个字符，星号表示任意一个或一串字符；如果要查找问号或星号，需要在它们前加转义字符（~）；例如：查找问题应该这样写：~?，查找星号应该这样写：~*。

（3）如果Match函数找不到值，将返回错误值 #N/A；另外，查找文本时，Match函数不区分大小写。

二、Excel Match函数的使用方法及实例

（一）Match_Type 省略或取 1 的实例

1、假如要查找员工表中“姓名”为“黄子辛”的位置。双击 A9 单元格，把公式 =MATCH("黄子辛",A2:A8) 复制到 A9，按回车，返回错误值 #N/A；框选 A2:A8，选择“数据”选项卡，单击“升序”图标，打开“排序提醒”窗口，保持默认选项“扩展选定区域”，单击“确定”，则表格按“姓名”升序排列；再次双击 A9，按回车，返回“黄子辛”在 A2:A8 的位置 1；双击 A9，给公式添加第三个参数 1，按回车，也返回 1；操作过程步骤，如图1所示：

图1

2、公式说明：

A、公式 =MATCH("黄子辛",A2:A8) 中，“黄子辛”为查找值，A2:A8 为查找区域，它省略了参数 Match_Type，当没有对 A 列按升序排序时，返回错误值 #N/A，当排序后才能返回“黄子辛”在 A2:A8 中的位置 1，当给公式加上参数 Match_Type（设置为 1）后，同样返回 1，说明参数 Match_Type 省略或取 1 时，查找区域需要按升序排序且查找小于等于查找值的最大值。

B、另外，查找值在查找区域中的位置从选定区域开始算起，这里的选定区域从第二行开始算起，因此返回 1。

（二）Match_Type 取 0 的实例

1、同样以查找员工表中“姓名”为“黄子辛”的位置为例。双击 A9 单元格，把公式 =MATCH("黄子辛",A2:A8,0) 复制到 A9，按回车，返回 3；双击 A9，把公式后面的 0 删除，按回车，也返回 3；操作过程步骤，如图2所示：

图2

2、公式说明：

A、当参数 Match_Type 取 0 时，在不按升序排序时，仍能正确返回“黄子辛”在 A2:A8 中的位置 3，说明 Match 取 0 时，查找区域不用排序。

B、公式 =MATCH("黄子辛",A2:A8,0) 与 =MATCH("黄子辛",A2:A8,) 能返回相同的值，说明省略第三个参数 Match_Type 时，如果第二个参数后有逗号（,），Match_Type 默认取 0；如果第二个参数后没有逗号，Match_Type 默认取 1，这一点要注意。

（三）Match_Type 取 -1 的实例

1、假如要在水果销量表中查找销量 2050 和 2000 的位置。双击 D8 单元格，把公式 =MATCH(2050,D2:D8,-1) 复制到 D8，按回车，返回错误值 #N/A；选中 D8，按 Delete 键把公式删除；选择“数据”选项卡，单击“降序”图标把表格按“销量”降序排序；再次把公式 =MATCH(2050,D2:D8,-1) 复制到 D8，按回车，返回 2050 在 D2:D8 中的位置 3；双击 D8，把 2050 改为 2000，按回车，同样返回 3；操作过程步骤，如图3所示：

图3

2、公式说明：

A、当 Match_Type 取 -1 时，在未对查找区域“销量”列排序前，公式返回错误值 #N/A，当按“降序”排序后，能返回 2050 在 D2:D8中的正确位置 3，说明 Match_Type 取 -1，查找区域必须按降序排序；当把 2050 改为 2000 后，由于 D2:D8 中没有 2000，所以查找大于等于 2000 的最小值，即 2050，最后返回该值的位置。

（四）查找值使用通配符问号（?）或星号（*）的实例

1、假如要查找以任意一个字或三个字开头且以“瓜”结尾的水果名称在 A 列中的位置。双击 D8 单元格，把公式 =MATCH("?瓜",A2:A7,0) 复制到 D8，按回车，返回 2；双击 D8，把 "?瓜" 改为 "???瓜"，按回车，返回 4；再次双击 D8，把 "???瓜" 改为 "*桃"，按回车，返回 5；操作过程步骤，如图4所示：

图4

2、公式说明：

公式 =MATCH("?瓜",A2:A7,0) 中的查找值 "?瓜" 有一个问号，它表示任意一个字，找到的是“香瓜”，因此返回位置 2；"???瓜" 表示由任意三个字开头且以“瓜”结尾，因此找到“青皮西瓜”；"*桃" 表示以任意一个或多个字开头且以“桃”结尾，因此找到“猕猴桃”。使用通配符除以上几种查找方式外，还可以组合出多种查找方式，在前面的篇章中已经介绍过多次。

三、Excel Match函数的使用方法扩展实例

（一）Index + Match 组合使用实例

Index + Match 有两种组合，一种为 =Index(, Match(,,))，另一种为 =Index(, Match(,,),Match(,,))，具体请查看《Excel Index函数的使用方法及与Match、Small、If配合返回行列对应的多个值和一对多、多对多查找》一文。

（二）Indirect + Match 组合实现返回行列对应的多个值（提取数据）实例

1、双击 B10 单元格，把公式 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0) 复制到 B10，按回车，返回“浆果”；选中B10，把鼠标移到 B10 右下角的单元格填充柄上，鼠标变为十字架后，按住左键，往右拖，则提取出“香蕉”的所有数据；把鼠标移到 D10 右下角单元格填充柄上，按住左键往下拖，则提取“香瓜”的所有数据；双击 B13，把公式 =INDEX($A$1:$D$7,MATCH($A13,$A$1:$A$7,),MATCH(B$9,$A$1:$D$1,)) 复制到 B13，按回车，同样返回“浆果”，再用往右拖和往下拖的方法提取剩余数据；操作过程步骤，如图5所示：

图5

2、公式说明：

（1）公式 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0)

A、$A10 表示绝对引用列相对引用行，往右拖时，A10 不会变为 B10、C10、……；往下拖时，A10 会变为 A11、A12、……；$A$1:$A$7以数组形式返回 A1:A7 中的“名称”。

B、MATCH($A10,$A$1:$A$7,) 用于返回 A10（香蕉）在 A1:A7 中的位置，结果为 4；COLUMN() 用于返回公式所在列列号，当公式在 B10 时，返回 2。

C、则公式变为 =INDIRECT("r"&4&"c"&2,0)，& 是连接符，在这里用于把字符（或字符串）与数字连接起来，进一步计算变为 =INDIRECT(r4c2,0)，r4c2 中 r 表示行、c 表示列，r4c2 表示第4行第2列，即 B4；参数 0 表示单元格引用被解释为 r1b1；最后用 Indirect 返回 r4c2 的引用，即返回“浆果”。

（2）公式 =INDEX($A$1:$D$7,MATCH($A13,$A$1:$A$7,),MATCH(B$9,$A$1:$D$1,)) 与 =INDIRECT("r"&MATCH($A10,$A$1:$A$7,)&"c"&COLUMN(),0) 作用一样，所不同的是 Index + Match 在公式中要引用表格的列名（如 B$9），而 Indirect + Match 不需引用；Index + Match 的解释请参考上面的“Index + Match 组合使用实例”。

（三）OffSet + Match 组合使用实例

1、假如要返回大于等于查找值的最小值对应的某列的值，例如要查找价格为 3.5 或 3.8 对应的“名称”。先把 C 列按“降序”排序（方法在上面“Match_Type 取 -1 的实例”已经介绍），双击 B10 单元格，把公式 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 复制到 B10，按回车，返回“猕猴桃”；双击 C10，把公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 复制到 C10，按回车，也返回“猕猴桃”；双击 A10，把 3.5 改为 3.8，单击一下 B10（或按 Ctrl + S 保存），B10 的内容变为“香蕉”，C10 的内容变为错误值 #N/A；操作过程步骤，如图6 所示：

图6

2、公式说明：

A、=OFFSET(A1,MATCH(A10,C2:C7,-1),) 中 MATCH(A10,C2:C7,-1) 用于返回 A10（3.5）在 C2:C7 中的位置 3；则公式变为 =OFFSET(A1,3,)，然后用 OffSet 返回距 A1 三行 0 列的单元格中的值，正好是 A5 中的“猕猴桃”。

B、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 与 =OFFSET(A1,MATCH(A10,C2:C7,-1),) 能返回相同的值仅限于查找范围中有查找值，例如 A10 为 3.5 时，C 列中有 3.5；当把 3.5 改为 3.8，OffSet + Match 能返回正确的值，而 VLookUp + Choose 返回 #N/A，说明 OffSet + Match 能查找大于等于查找值的最小值，而 VLookUp + Choose 却不能；另外，OffSet + Match 比 VLookUp + Choose 效率高。

C、公式 =VLOOKUP(A10,CHOOSE({1,2},C2:C7,A2:A7),2,) 在《Excel VLookUp函数的使用方法，含逆向查找、一对多查找和近似匹配实例》一文中已经有类似介绍，如果不理解可以参考。

（四）Sum + OffSet + Indirect + Match 组合实现对变化区域动态跨表求和实例

1、假如要汇总每个月的女装和男装的销量，女装销量与男装销量分别在一个表，要把它们汇总到“汇总”表。当前工作表为“服装销量女装”，单击“服装销量男装”切换到该表，单击“汇总”表切换到该表；双击 B2 单元格，把公式 =SUM(OFFSET(INDIRECT("服装销量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服装销量"&$A2&"!$C$2:$H$2"),))) 复制到 B2，按回车，返回“4月”的“女装”销量汇总结果2407；选中 B2，用往右拖的方法返回“女装”剩余月份的销量汇总结果，再用往下拖的方法返回“男装”各月的销量汇总结果；操作过程步骤，如图7所示：

图7

2、公式 =SUM(OFFSET(INDIRECT("服装销量"&$A2&"!$B$3:$B$7"),,MATCH(B$1,INDIRECT("服装销量"&$A2&"!$C$2:$H$2"),))) 说明：

A、"服装销量"&$A2&" 用于组合工作表名称，每个工作表都以“服装销量”开头，A2 中为“女装”，因此，公式执行时，"服装销量"&$A2&" 变为“服装销量女装”。当往下拖时， A2 变为 A3，"服装销量"&$A2&" 变为 "服装销量"&$A3&"，即“服装销量男装”。

B、$B$3:$B$7 以数组形式返回 B3 至 B7 中的值，INDIRECT("服装销量"&$A2&"!$B$3:$B$7") 把字符转为对单元格的引用，它返回“服装销量女装!$B$3:$B$7”。

C、$C$2:$H$2 以数组形式返回 C2 至 H2 中的值，即返回 C2 至 H2 的列名；INDIRECT("服装销量"&$A2&"!$C$2:$H$2") 返回“服装销量女装!$C$2:$H$2”。

D、MATCH(B$1,INDIRECT("服装销量"&$A2&"!$C$2:$H$2"),) 变为 MATCH(B$1,服装销量女装!$C$2:$H$2,)，接着用 Match 返回 B1（4月）在 C2 至 H2 中位置 2。

E、则公式变为 =SUM(OFFSET(服装销量女装!$B$3:$B$7,,2,)))，进一步计算用 OffSet 返回“服装销量女装!$D3$3:$D$7”，是怎么返回的呢？执行到 OffSet 时，首取出 B3，然后返回与 B3 相隔两列的单元格，即返回 D3；第二次取出 B4，返回与 B4 相隔两列的单元格 D4；其它的以此类推，因此最后返回“服装销量女装!$D3$3:$D$7”；则公式变为 =SUM(服装销量女装!$D$3:$D$7)，最后用 Sum 对 D3:D7 求和，所以返回 2407。