在Excel中进行数据的批量查找,经常会用到一些函数公式,下面小五和大家分享4个数据查找示例:
1、多行多列连续性查找
如下图表格所示,根据姓名进行相应的数据查找,在J2单元格中输入公式:
=VLOOKUP($I2,$B$1:$G$11,COLUMN(B1),0),往右往下填充公式即可。
![8559f015a8e08395121cb26a99f9347f.png](https://i-blog.csdnimg.cn/blog_migrate/9f96e832946208624f2238a13fc91ab4.jpeg)
公式说明:利用VLOOKUP+COLUMN函数组合,第3个参数COLUMN(B1)返回查找区域中的指定列数,公式向右填充时,会自动变成COLUMN(C1)、COLUMN(D1)、COLUMN(E1),即变成了3、4、5。
2、多行多列不连续性查找
根据工号进行数据查找,而且查找项不连续,在J2单元格中输入公式:
=VLOOKUP($I2,$A$1:$G$11,MATCH(J$1,$A$1:$G$1,0),0),往右往下填充公式即可。
![7ea3fe3683794f4878c62d23df35838e.png](https://i-blog.csdnimg.cn/blog_migrate/60da10043b8c4e0e7bda6868ac2e1fb6.jpeg)
公式说明:利用VLOOKUP+MATCH函数组合。MATCH函数公式返回查找项在标题行中对应的列号,结果作为VLOOKUP函数的第3个参数,公式中注意单元格的引用方式。
3、双条件批量查询
如下图表格所示,员工存在重名,为了精准查找,现在需要根据员工的姓名和岗位查找员工的对应月工资标准,在I2单元格中输入公式:
=VLOOKUP($I2&$J2,IF({1,0},$B$1:$B$11&$D$1:$D$11,$G$1:$G$11),2,0),
按Ctrl+Shift+Enter组合键输入公式,并向下填充。
![2c810950b7915ce6784dbf046be6ea3c.png](https://i-blog.csdnimg.cn/blog_migrate/ad631da1cd93cfde0bff74ff18609907.jpeg)
公式说明:利用VLOOKUP+IF函数组合。第1个参数用&符号进行查找值合并,
IF({1,0},$B$1:$B$11&$D$1:$D$11,$G$1:$G$11)则是重新构建一个新的查找区域。
这里也可以利用INDEX+MATCH函数组合,在I2单元格输入公式:
=INDEX($G$1:$G$11,MATCH($I2&$J2,$B$1:$B$11&$D$1:$D$11,0)),
按Ctrl+Shift+Enter组合键输入公式,并向下填充。
![318829773357008d7e9c7a05fa1451b6.png](https://i-blog.csdnimg.cn/blog_migrate/113a2fbf4caf6f25ef2143c6cab17284.jpeg)
公式说明:MATCH($I2&$J2,$B$1:$B$11&$D$1:$D$11,0)返回指定值在查找区域中所在的行位置,作为INDEX函数公式的第2个参数。
4、一对多查询
如果我们查询某一部门对应的所有员工月工资标准呢?比如我们要查询人力资源部门对应的所有月工资标准,在I2单元格中输入公式:
=IFERROR(INDEX($G$1:$G$11,SMALL(IF($C$1:$C$11=$I$2,ROW($1:$11)),ROW(A1))),""),按Ctrl+Shift+Enter组合键输入公式,并向下填充。
![294ad73b95fdb460039e9d9486f04d00.png](https://i-blog.csdnimg.cn/blog_migrate/c1cdf73cb1925f02181488fe5694695a.jpeg)
公式说明:这里利用INDEX+SMALL+IF 这个万金油函数公式。
- IF($C$1:$C$11=$I$2,ROW($1:$11)),若C1:C11中的的值等于I2,返回C列中对应的行号,否则返回逻辑值FALSE;
- SMALL(IF($C$1:$C$11=$I$2,ROW($1:$11)),ROW(A1)),对IF函数公式的计算结果进行取数,随着公式的向下填充,提取第1、2、3……n个最小值,依次得到符合条件的行号;
- INDEX函数则以SMALL函数公式返回的行号作为索引值,返回G列中对应的月工资标准;
- 最后用IFERROR函数规避错误值,使之显示为空。