转自EXCEL不加班
1.根据编号和级别双条件查找金额。
这种如果用常规的方法查找,难度非常大,不过小小的变动就可以将难度降到最低。
插入一列,将编号和级别合并起来。
在B12输入公式,右拉和下拉。
=VLOOKUP($A12&B$11,$C$1:$D$9,2,0)
以上所有公式,如果需要进行容错处理,用IFERROR函数。
=IFERROR(VLOOKUP($A12&B$11,$C$1:$D$9,2,0),"")
2.突破VLOOKUP函数限制,查找多个对应值
VLOOKUP函数很神奇,不过并非万能查找函数,比如根据著作查找所有人物。
直接用VLOOKUP函数进行查找,只能查找到第一个对应的人物,没法查找到全部人物。
=IFERROR(VLOOKUP($E2,$B:$C,2,0),"")
究竟该如何突破VLOOKUP函数的限制,让VLOOKUP函数可以查找到全部对应值呢?
思路:在查找的时候,唯一值才可以查找,著作都不是唯一值,没办法直接查找。如果添加一个辅助列,获取著作 次数,就变成了唯一值,这样就可以突破VLOOKUP函数的局限。
添加一个辅助列次数,在A2输入公式下拉填充。
=B2&COUNTIF($B$2:B2,B2)
区域采用$B$2:B2这种写法,估计很多初学者不理解,卢子这里详细说明一下。
$B$2加美元$锁定行号和列号,这样下拉的时候,就不会进行任何改变,依然是$B$2,也就是绝对引用。
B2因为没有加美元$锁定,所以下拉的时候就变成了B3、B4、B5……,这种就叫相对引用。
一个锁定,一个不锁定,这样下拉的时候,就可以让区域逐渐变大。用COUNTIF函数,就可以依次获取著作的出现次数。
左边的查找区域原理知道了,现在来看右边如何用VLOOKUP函数查找。
在F2输入公式下拉和右拉。
=IFERROR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0),"")
现在以红楼梦为例进行说明,红楼梦一共出现3次,也就是红楼梦1、红楼梦2、红楼梦3。
而E2单元格只是红楼梦缺少次数,数字1、2、3可以通过COLUMN函数获取。A就对应1,B就对应2,依次类推。
这样用下面的公式就完成了查找。
=VLOOKUP($E2&COLUMN(A1),$A:$C,3,0)
不过这样直接查找,没有对应值会显示错误值,不太美观,因此嵌套一个IFERROR函数,让错误值显示空白。
到此,就解释完毕了。其实,要学好函数,思路真的很重要。
3.Excel双条件查找新套路,简单好用
查找对应值几乎所有人都会用到,而VLOOKUP函数是所有查找函数的代表。只要遇到查找对应值,第一反应就是用VLOOKUP函数。但是VLOOKUP函数真的是最好的选择吗?
根据姓名查找对应值,单元格H1可以选择标题返回相应的列,效果如动图。
常规方法:
=VLOOKUP(G2,A:E,MATCH($H$1,$A$1:$E$1,0),0)
卢子就带你突破常规,见识不一样的查找。
Step 02 在单元格H2输入公式,双击填充。
=INDIRECT(G2) INDIRECT($H$1)
不要怀疑自己的眼睛,就是两个INDIRECT函数,中间用空格隔开,这就是正确的公式。
原理:
用空格隔开,代表两个区域的交叉部分,比如A2:A13和13:13的交叉部分就是A13,也就是返回卢子。
根据所选内容创建就是对区域进行定义名称,打开名称管理器,就可以看到所有创建的名称。
卢子的区域是B13:E13,公司名称的区域是C2:C13,这两个区域的交叉部分就是C13,也就是返回Excel不加班。
交叉区域这种用法很多人都不知道,你可以将此方法分享给朋友。
,