match和index的组合使用,index和match同时配合使用方法

首页 > 生活 > 作者:YD1662024-06-12 11:41:03

match和index的组合使用,index和match同时配合使用方法(1)

查询引用,不是Lookup或vlookup的专职工作吗?是的,确实如此,除了Lookup、Vlookup外,还有Hlookup以及Xlookup等;但除此之外,还有一组黄金搭档,那就是Index MATCH;要掌握这对黄金搭档,首先要了解Index函数和Match函数本身的作用。


一、Index。

功能:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。

从功能中就可以看出(返回特定行列交叉处单元格的值或引用),此函数具有两种引用形式:

(一)数组形式:=Index(单元格区域或数组常量,行,[列])。

参数解读:

1、单元格区域或数组常量:必需。

如果数组仅包含一行或一列,则对应的参数“行”或“列”是可选的。

2、行:必须。

选择数组中的某行,函数从该行返回值。

3、列:可选。

选择数组中的某列,函数从该列返回值。

备注:

参数“行”和“列”必须指向数组中的单元格,否则Index将返回#REF!错误。

案例:

目的:返回数据表中“司马懿”的“月薪”。

match和index的组合使用,index和match同时配合使用方法(2)

方法:

在目标单元格中输入公式:=INDEX(C3:H12,5,6)或=INDEX(H3:H12,5)。

解读:

1、公式:=INDEX(C3:H12,5,6)中,数组不是单行或单列,所以需要同时指定参数“行”和参数“列”。

2、公式:=INDEX(H3:H12,5)中,只有一列,所以只需指定“行”即可。


(二)引用形式:=Index(单元格区域,行,[列],[区域顺序])。

参数解读:

1、单元格区域:必需。

(1)此处的单元格区域可以是1个,也可以是多个。

(2)如果引用的区域是非邻的,必需用括号()括住。

(3)如果引用的每个区域仅包含1行或1列,则对应的参数“行”、“列”是可选的。

2、行:必需。

引用中某行的行号,函数从该行返回引用。

3、列:可选。

引用中某列的列标,函数从该列返回一个引用。

4、区域顺序:可选。

(1)指定“单元格区域”中被引用的区域,从该范围中按参数“行”、“列”的值返回指定的引用。

(2)选定或输入的第一个区域编号为1,第二个为2,以此类推。

(3)缺省该参数的情况下,Index默认该值为1。

案例:

目的:返回数据表中“财务部”第2行第6列的值。

match和index的组合使用,index和match同时配合使用方法(3)

方法:

在目标单元格中输入公式:=INDEX((C3:H5,C6:H12),2,6,2)。

解读:

公式中的最后一个参数2指定的是第2个数据区域,即C6:H12;从C6:H12中提取第2行第6列较差处的值。


二、Match。

功能:返回符合特定值顺序的项在数值中的相对位置。

语法结构:=Match(定位值,数据范围,[匹配模式]);“匹配模式”分为-1、0、1三种,分别为:“小于”、“精准匹配”、“大于”。

1或省略:Match查找小于或等于“定位置”的最大值。“数据范围”中的值必须以升序排序

0:Match查找完全等于“定位值”的第一个值。

-1:Match查找大于或等于“定位置”的最小值。“数据范围”中的值必须以降序排序

备注:

1、Match函数返回的是“定位值”在“数据范围”中的相对位置,而非其值本身。

2、匹配文本时,Match函数不区分大小写字母。

3、如果查询不到“定位值”,Match函数将返回:#N/A!。

4、如果“匹配模式”为0且“定位值”为文本字符串,则可以在“定位值”中使用通配符?(问号,匹配任意单个字符)和*(星号:匹配任意一串字符);如果要查找实际的?或*,则在字符前输入~(波形符)。

案例:

目的:返回最低“月薪”在月薪列的相对位置。

方法:

在目标单元格中输入公式:=MATCH(SMALL(H3:H12,1),H3:H12,0)。

解读:

利用Small函数获取最低“月薪”,然后利用Match函数定位其位置。


三、Index Match应用案例。

1、常规查询。

目的:根据“员工姓名”查询对应的“月薪”。

match和index的组合使用,index和match同时配合使用方法(4)

方法:

在目标单元格中输入公式:=INDEX(H3:H12,MATCH(L3,C3:C12,0))。

解读:

典型的数组形式应用案例,首先用Match函数定位出当前值(L3)在C3:C12中的相对位置,然后返回给Index当做“行”参数使用,最后返回H3:H12中指定行的值。


2、逆向查询。

目的:根据“员工姓名”查询对应的“员工编号”。

match和index的组合使用,index和match同时配合使用方法(5)

方法:

在目标单元格中输入公式:=INDEX(B3:B12,MATCH(L3,C3:C12,0))。

解读:

用Match函数定位当前值(L3)在相对范围(C3:C12)中的相对位置,并返回给Index函数的“行”参数;用Index提取B3:B12范围中指定行的值。


3、多条件查询。

目的:查询符合指定“性别”、“婚姻”、和“部门”的“人员姓名”。

match和index的组合使用,index和match同时配合使用方法(6)

方法:

在目标单元格中输入公式:=INDEX(C3:C12,MATCH(L3&M3&N3,E3:E12&F3:F12&I3:I12,0))。

解读:

1、“定位值”和“数据范围”之间用连接符“&”对应连接即可;由于有多个数据范围,所以在填充时用Ctrl Shift Enter填充。

2、如果有多个符合条件的值,返回第1条记录。


结束语:

查询引用中,无外呼常规查询(正向查询)、逆向查询以及多条件查询外;用Index Match函数都可以轻松应对,相对于Lookup和Vlookup来说,更容易理解和上手应用。

如果亲有更多的关于Index和Match函数的应用技巧,欢迎在留言区留言讨论哦!


栏目热文

文档排行

本站推荐

Copyright © 2018 - 2021 www.yd166.com., All Rights Reserved.