查询引用,不是Lookup或vlookup的专职工作吗?是的,确实如此,除了Lookup、Vlookup外,还有Hlookup以及Xlookup等;但除此之外,还有一组黄金搭档,那就是Index Match;要掌握这对黄金搭档,首先要了解Index函数和Match函数本身的作用。
一、Index。
功能:在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。
从功能中就可以看出(返回特定行列交叉处单元格的值或引用),此函数具有两种引用形式:
(一)数组形式:=Index(单元格区域或数组常量,行,[列])。
参数解读:
1、单元格区域或数组常量:必需。
如果数组仅包含一行或一列,则对应的参数“行”或“列”是可选的。
2、行:必须。
选择数组中的某行,函数从该行返回值。
3、列:可选。
选择数组中的某列,函数从该列返回值。
备注:
参数“行”和“列”必须指向数组中的单元格,否则Index将返回#REF!错误。
案例:
目的:返回数据表中“司马懿”的“月薪”。
方法:
在目标单元格中输入公式:=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列的值。
方法:
在目标单元格中输入公式:=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、常规查询。
目的:根据“员工姓名”查询对应的“月薪”。
方法:
在目标单元格中输入公式:=INDEX(H3:H12,MATCH(L3,C3:C12,0))。
解读:
典型的数组形式应用案例,首先用Match函数定位出当前值(L3)在C3:C12中的相对位置,然后返回给Index当做“行”参数使用,最后返回H3:H12中指定行的值。
2、逆向查询。
目的:根据“员工姓名”查询对应的“员工编号”。
方法:
在目标单元格中输入公式:=INDEX(B3:B12,MATCH(L3,C3:C12,0))。
解读:
用Match函数定位当前值(L3)在相对范围(C3:C12)中的相对位置,并返回给Index函数的“行”参数;用Index提取B3:B12范围中指定行的值。
3、多条件查询。
目的:查询符合指定“性别”、“婚姻”、和“部门”的“人员姓名”。
方法:
在目标单元格中输入公式:=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函数的应用技巧,欢迎在留言区留言讨论哦!