If函数中的判断条件可以用ISNA()函数取代。
- 原来的语法格式,If(判断条件,如果是就显示, 如果否就显示)
- 代入ISNA后的语法格式,If(ISNA(值),如果是就显示, 如果否就显示)
ISNA函数中的值可以用Vlookup函数取代。
- 原来的语法格式,ISNA(值)
- 代入Vlookup后的语法格式,ISNA(Vlookup(...))
将If, ISNA, Vlookup组合在一起,
=if(ISNA(Vlookup(...)) ,"没找到", Vlookup(...) )
回到刚才的案例,
在K2输入:
=IF(ISNA(VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$F$1,0),0)),"没找到",VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$F$1,0),0))
这是不是有点神奇呢? 没找到,不会再显示#N/A, 而是显示的"没找到".
如果有的朋友想显示"查无此人", 公式就改为
=IF(ISNA(VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$F$1,0),0)),"查无此人",VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$E$1,0),0))
如果有的朋友想显示空白, 公式就改为
=IF(ISNA(VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$F$1,0),0))," ",VLOOKUP($J2,$A:$F,MATCH(K$1,$A$1:$E$1,0),0))
第二种方法。Xlookup.
你没看错是Xlookup, 不是Vlookup,也不是Hlookup, 更不是Lookup。
Xlookup是新版 Excel才有的函数,其语法结构比Vlookup简单。
Xlookup的语法格式为:
- 英文语法格式=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
- 中文语法格式=XLOOKUP(查找值,查找列,结果列,如果没找到,查找精准度,搜索模式)
前面三个参数是必须参数,后面三个参数是可选参数。
其中的第四个参数"如果没找到", 就可以解决#N/A的问题。
我们将第一种方法的公式改为:
查找工号,在K2输入=XLOOKUP(J2,A:A,C:C,"没找到"), 并向下复制填充。
- 查找值J2
- 查找列A:A. 和Vlookup不一样,Xlookup只需要查找列A:A即可。Vlookup是查找范围,必须包含查找列和结果列。
- 结果列C:C. 工号所在列。
- 如果没找到,"没找到"。 如果没找到就显示"没找到".
Xlookup的公式,是不是比Vlookup简单多了?
Xlookup的弊端是不能和Match配合使用,Vlookup Match可以设置一个公式,向下向右复制填充。但Xlookup没这个功能,所以,我们必须每列都设置一个格式。
同理,
- 查找部门,在L2输入=XLOOKUP(J2,A:A,E:E,"没找到"),并向下复制填充。
- 查找年薪,在M2输入=XLOOKUP(J2,A:A,B:B,"没找到"),并向下复制填充。
- 查找性别,在N2输入=XLOOKUP(J2,A:A,F:F,"没找到"),并向下复制填充。
- 查找年终奖,在O2输入=XLOOKUP(J2,A:A,D:D,"没找到"),并向下复制填充。
注意,Xlookup只适用于新版Excel, 旧版Excel无法使用。
第三种方法。条件格式。如果是#N/A,就将字体颜色改为白色。
步骤1. 选择范围K:O列。
步骤2. 选择条件格式-->新建规则
步骤3. 选择新建格式规则中的最后一个“使用公式确定要设置格式的单元格”
步骤4. 输入公式=ISNA(K1).