01 VLOOKUP回顾
在之前的一期文章中我们曾经提到VLOOKUP函数的用法。为了防止遗忘,我们再放上来。
VLOOKUP是一个非常有用的纵向查找函数,在工作中有广泛的应用。我们可以用它来核对数据,或者多个表格之间快速导入数据。
它的公式是=VLOOKUP(查询值,查询范围,查询值在查询范围中的列数,匹配模式)看起来好像有点难理解,让我们来翻译一下:
=VLOOKUP(找什么,在哪找,找到后返回其右侧对应的第几列数据,精确还是模糊查找)这样一看,是不是简单多了?
比如,在下面这个案例中,我们想要在左侧的数据库中找到某几个id的同学他们的分数和名字,就可以用vlookup来快速查询。
![30bf54c1ffa0d78cfca060891643c86c.gif](https://i-blog.csdnimg.cn/blog_migrate/d7d7dd5e64ad46fd048c483b9d71ec06.gif)
我们使用的公式是:
=VLOOKUP($J2,$A$1:$E$13,COLUMN(B1),0)
其中$J2是我们要找的东西,也就是ID和名字;$A$1:$E$13是我们查找ID/名字的范围,也就是我们的数据库;
COLUMN(B1)是我们找到ID后返回其右侧对应的第几列数据,在这里也就是第二列;0代表精确查找,1代表模糊查找。
02 INDEX&MATCH查找函数
但是,有时候由于表格格式的原因,我们不得不反向查询。而VLOOKUP函数只能作正向查找,而不能反向查找,即根据前列查找后列的内容,而不能根据后列查找前列内容。
如下图,有时候我们并不是通过ID查找NAME和SALE,而是反过来通过Sale查找Name和ID。
![7882e54c931d4f7af5105d950f2a8b58.png](https://i-blog.csdnimg.cn/blog_migrate/72e9683f7840fad9e7e9db30e851ab78.jpeg)
反向查找的情况下,INDEX&MATCH函数就可以大展身手了。
下面我们就来讲讲INDEX函数和MATCH函数的用法。
基本逻辑:MATCH函数可以返回指定内容所在的位置,而INDEX又可以根据指定位置查询到位置所对应的数据。两个函数组合,我们就可以返回指定位置相关联的数据。
MATCH函数(返回指定内容所在的位置)
MATCH(lookupvalue,lookup-array,match-type)
Lookupvalue:表示要在区域或数组中查找的值,可以是直接输入的数组或单元格引用。
Lookup-array:表示可能包含所要查找的数值的连续单元格区域。
Match-type:表示查找方式,其中0为精确查找。
INDEX函数(返回指定位置的内容)
INDEX(array,row-num,column-num)
Array:要返回值的单元格区域或数组
Row-num:返回值所在的行号
Column-num:返回值所在的列号
03 INDEX&MATCH应用
下面我们通过一个简单的例子来应用INDEX&MATCH查找函数。如下图,我们想通过指定的月份(Month)和地区(Region)找到销售额。
![af73612b33d24215aaf5711c153c2cec.png](https://i-blog.csdnimg.cn/blog_migrate/b964422b0b53351ec91142af1ea081f8.jpeg)
第一步,我们先确定月份(Month)的位置,也就是应用Match函数。公式为=Match(E7,G7:G18,0),其中E7是月份,G7:G18是月份所在的区域,0是精准查找。
![0750d715df19086bc1af29c1892c922a.png](https://i-blog.csdnimg.cn/blog_migrate/c55d9ec9ac16de081de978d5d2daa568.jpeg)
第二步,我们确定区域(Region)的位置,同样使用Match函数。公式为=Match(E8,H6:L6,0)
![98fac6c4438e2955853fec79758e3a47.png](https://i-blog.csdnimg.cn/blog_migrate/bb17cf0ef180ed24ce19e443da647f69.jpeg)
第三步,我们通过Index函数来找到指定位置的销售额。=INDEX(H7:L18,E10,E11)
![6501839a9083d144d78341977d79c511.png](https://i-blog.csdnimg.cn/blog_migrate/6b936fa8719f981c27ce73a1d7be93e9.jpeg)
其中,H7:L18是销售额范围,E10是月份的位置也就是列的位置3,E11是区域也就是行的位置3。
第四步,我们把上述第二步和第三步的公式组合起来,一步找到销售额,公式如下。
=INDEX(H7:L18,MATCH(E7,G7:G18,0),MATCH(E8,H6:L6,0))
如上图,通过INDEX&MATCH搭档,我们可以轻松地查找出三月份(March),中西部(Midwest)的销售额啦。
![4e7ccb1f8308a0c829a6d3f457d1f1d4.png](https://i-blog.csdnimg.cn/blog_migrate/6eb354dc8732a38cab49f734e9ead062.jpeg)
如果我们不仅仅想要三月,中西部的数据,而是想要查找任意月份和任意地区的销售额,我们可以在March和Midwest两个单元格建立下拉框,方便我们选择任意月份和地区。
我们选中月份(March)的单元格,在数据(DATA)中选择数据检验(Data Validation),然后在标准(Criteria)中选择列表(List),在来源中选择所有月份的区域。对于区域(Midwest),我们也做同样的操作。
![0d5001dd21c87602c73f8d19793dcc8d.png](https://i-blog.csdnimg.cn/blog_migrate/8782dec26290d5638816cc9b92ff4955.png)
上述动图展示了我们的效果,我们可以方便地查找任意月份和所有地区的销售额。
我们又掌握了一个Excel重要且实用的函数。完结,撒花!
以上就是今天的INDEX&MATCH查找函数展示,希望大家在闲暇时间可以多多练习。欢迎关注,收藏和在看。欢迎关注公众号“三千越甲吞吴”免费获取更多求职,留学,英语,酒店干货。