说到按条件查询并返回值,大家都不陌生。但是常用的 vlookup, hlookup 函数只能解决一维查询,即单一条件查询。
查询二维表,比如既要满足行条件,又要满足列条件,怎么做?
用 index 和 match 函数组合可以解决这个需求。
我用的版本是 Excel 2016,其他版本的界面可能略有不同。
示例:
比如下表是学生的考试成绩原始表,需要查找某学生的某科成绩,怎么做?
![e03eacfa30e529b1609942def104aefb.png](https://i-blog.csdnimg.cn/blog_migrate/5f2370c57a0a2b02ed0dda601f13ba44.jpeg)
我做了一个结果示例,姓名和学科列,都是可选的下拉菜单,当这两列选定以后,成绩列会显示出赵六同学的政治成绩。
![3603742ff572dfd1c8e2775ee7849d4c.png](https://i-blog.csdnimg.cn/blog_migrate/b6033e89c29d8985992de959f15e4b99.jpeg)
![dc36af63331d9046787f8d7e181c0132.png](https://i-blog.csdnimg.cn/blog_migrate/06afa5feeeb4292417ff8b69fd8339de.jpeg)
解决方案:
当当当当~ index 和 match 函数组合拳闪亮登场!
=INDEX(B2:I7,MATCH(B11,A2:A7,0),MATCH(A11,B1:I1,0))
我把机器语言翻译成人类语言帮助大家理解一下阿:
- B2:I7:查找的数据源的区域,这里是所有成绩区域
- MATCH(B11,A2:A7,0):在第几行
- MATCH(A11,B1:I1,0)):在第几列
再把 match 函数翻译一下:
- MATCH(B11,A2:A7,0):根据B11 (学科)的值,查找并返回该学科所在的行数
- MATCH(A11,B1:I1,0)):根据A11 (姓名)的值,查找并返回该同学所在的列数
* 请注意:index 函数规定,行和列值的返回顺序不可以反过来,也就是说,上面两个 match 函数的位置不能互换。
啥?下拉菜单怎么做?好吧,那也说一下,虽然在本文教这个有点拉低技术含量。
1. 选择菜单 Data --> Data Validation --> Data Validation
![91515ef071f762b5b06ac15437c50a7f.png](https://i-blog.csdnimg.cn/blog_migrate/b333c212f1806c7e408c1e4fce1903e7.jpeg)
2. 在第一个下拉菜单里选择 List
![4da8fcf5d0a3a1e554e7a821179b20c2.png](https://i-blog.csdnimg.cn/blog_migrate/86ee4174eedb38020859a91518d3414f.jpeg)
3. 点击 Source 中的输入区域 --> 拖动选择需要的下拉菜单项,这里是所有姓名 --> OK
![78f7a253def861e81c847a6193d8660d.png](https://i-blog.csdnimg.cn/blog_migrate/9ab849eae4ab33ac06f944c25e2f128d.jpeg)
4. 同理制作学科列的下拉菜单